SQL Day 4: Mastering GROUP BY, HAVING, Subqueries, and Advanced SQL Concepts

In today's SQL session, we will explore GROUP BY, HAVING, subqueries, JOINs, and other advanced SQL topics. Let's dive into each topic with explanations, examples, and best practices.

1️⃣ GROUP BY and HAVING

🔹 GROUP BY

• Used to group rows with the same values in specified columns.
• Often used with aggregate functions (COUNT(), SUM(), AVG(), etc.).

🔹 HAVING vs. WHERE

✅ Example: Using GROUP BY and HAVING

SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

✔️ Explanation: Groups employees by department and filters those with more than 10 employees.

2️⃣ Subqueries (Nested Queries)

A subquery is a SQL query inside another query.

🔹 Types of Subqueries

• Scalar Subquery – Returns single value
• Multi-row Subquery – Returns multiple rows
• Correlated Subquery – Uses outer query values inside subquery

✅ Example: Finding employees who earn more than the average salary

SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

✔️ Explanation: The subquery finds the average salary, and the outer query selects employees earning above that.

3️⃣ LIMIT and OFFSET

🔹 LIMIT

• Restricts the number of rows returned.
• Useful for pagination and top results queries.

🔹 OFFSET

• Skips a number of rows before starting to return results.
• Used with LIMIT for pagination.

✅ Example: Fetch top 5 highest salaries

SELECT name, salary FROM employees 
ORDER BY salary DESC 
LIMIT 5;

✅ Example: Fetch next 5 highest salaries (Pagination)

SELECT name, salary FROM employees 
ORDER BY salary DESC 
LIMIT 5 OFFSET 5;

✔️ When to use?

• Use LIMIT when fetching a specific number of rows.
• Use OFFSET when implementing pagination (e.g., showing page 2 results).

✔️ When NOT to use?

• If performance is critical, avoid high OFFSET values, as it scans many rows before returning results.

4️⃣ IN and NOT IN Operators

Used for filtering results based on a list of values.

✅ Example: Employees in specific departments

SELECT name FROM employees 
WHERE department IN ('HR', 'Finance', 'IT');

✔️ IN selects employees from HR, Finance, and IT departments.

✅ Example: Employees NOT in specific departments

SELECT name FROM employees 
WHERE department NOT IN ('HR', 'Finance', 'IT');

✔️ NOT IN excludes employees from HR, Finance, and IT departments.

✔️ When to use?

• Use IN for short lists of known values.
• Avoid IN for large lists; use JOIN or EXISTS for better performance.

5️⃣ Naming Rules in PostgreSQL

🔹 Table Naming Rules

• Must start with a letter or underscore (_).
• Can contain letters, numbers, and underscores.
• Cannot use PostgreSQL reserved keywords (e.g., SELECT, TABLE).

✅ Valid Table Names: employees, _user_data, customer_orders

❌ Invalid Table Names: 123table, select, table-name

6️⃣ When Can We Join Tables?

✅ You can join tables when:

• They have a common column (Primary Key & Foreign Key).
• Data is related (e.g., customers and orders).

🔹 Example: INNER JOIN

SELECT customers.name, orders.order_id 
FROM customers 
INNER JOIN orders ON customers.customer_id = orders.customer_id;

7️⃣ What if Two Tables Have No Common Column?

If two tables don’t have a common column, we cannot use JOINs directly. Instead, we can:

✅ Use CROSS JOIN (Cartesian Product)

SELECT employees.name, departments.department_name 
FROM employees 
CROSS JOIN departments;

✔️ Generates all possible combinations of employees and departments.

✅ Use UNION (Combine Results from Two Tables)

If tables have similar structures, use UNION:

SELECT name FROM employees
UNION
SELECT name FROM managers;

✔️ Combines employee and manager names without duplicates.

🔥 Summary of Today’s SQL Topics

✅ GROUP BY & HAVING – Used for grouping and filtering aggregated data.

✅ Subqueries – Nested queries used for advanced filtering.

✅ LIMIT & OFFSET – Control result pagination.

✅ IN & NOT IN – Filter results based on lists.

✅ Naming Rules – Follow PostgreSQL naming conventions for tables, columns, and databases.

✅ JOINing Tables – Only possible if there’s a common column. If not, use CROSS JOIN or UNION.