Joins, Aggregation and Subqueries
Sample tables
schema.sql
-- employee(emp_id, name, dept_id, salary)
-- department(dept_id, dept_name)
Joins
| Join | Returns |
|---|---|
INNER JOIN | Rows with a match in both tables |
LEFT JOIN | All left rows, matching right rows or NULL |
RIGHT JOIN | All right rows, matching left rows or NULL |
FULL OUTER JOIN | All rows from both, NULL where unmatched |
CROSS JOIN | Every combination (Cartesian product) |
| Self join | A table joined with itself (e.g. employee and manager) |
joins.sql
SELECT e.name, d.dept_name
FROM employee e
LEFT JOIN department d ON d.dept_id = e.dept_id;
-- employees without a department
SELECT e.name
FROM employee e
LEFT JOIN department d ON d.dept_id = e.dept_id
WHERE d.dept_id IS NULL;
Aggregation
Functions: COUNT, SUM, AVG, MIN, MAX.
group_by.sql
SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employee
GROUP BY dept_id
HAVING COUNT(*) >= 3 -- filter groups, not rows
ORDER BY avg_salary DESC;
WHERE filters rows before grouping; HAVING filters groups after aggregation.
Subqueries
subquery.sql
-- employees earning more than the average
SELECT name
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);
-- departments that have at least one employee
SELECT dept_name
FROM department d
WHERE EXISTS (SELECT 1 FROM employee e WHERE e.dept_id = d.dept_id);
Classic: second highest salary
second_highest.sql
SELECT MAX(salary)
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);
Window functions
window.sql
SELECT name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM employee;
Loading Comments...