Definition
The SELECT statement retrieves data from one or more tables. JOINs combine rows from multiple tables on related columns. Subqueries nest one SELECT inside another. CTEs (Common Table Expressions) name subqueries for readability and reusability. Mastering SELECT with all JOIN types, WHERE conditions, ORDER BY, LIMIT, and subqueries is essential for every database professional.
SELECT statement execution order
Clause execution order and full SELECT anatomy
-- Written order: Execution order:
-- SELECT 1. FROM + JOINs
-- FROM 2. WHERE
-- WHERE 3. GROUP BY
-- GROUP BY 4. HAVING
-- HAVING 5. SELECT
-- ORDER BY 6. DISTINCT
-- LIMIT 7. ORDER BY
-- 8. LIMIT/OFFSET
-- Full example
SELECT
d.DeptName,
COUNT(s.StudentID) AS StudentCount,
AVG(s.GPA) AS AvgGPA,
MAX(s.GPA) AS TopGPA
FROM Student s
INNER JOIN Department d ON s.DeptID = d.DeptID
WHERE s.Status = 'Active'
AND s.EnrollDate > '2022-01-01'
GROUP BY d.DeptName
HAVING AVG(s.GPA) > 3.0
ORDER BY AvgGPA DESC
LIMIT 5;JOIN types — all six with examples
| JOIN type | Returns | NULL behaviour | Use case |
|---|---|---|---|
| INNER JOIN | Rows with matching values in BOTH tables | No NULLs for join columns | Students WITH departments |
| LEFT OUTER JOIN | All LEFT rows + matching RIGHT rows | Right-side NULL if no match | All students — NULL if no dept |
| RIGHT OUTER JOIN | All RIGHT rows + matching LEFT rows | Left-side NULL if no match | All departments — NULL if no students |
| FULL OUTER JOIN | All rows from BOTH tables | NULLs on whichever side has no match | Complete view — unmatched on both sides |
| CROSS JOIN | Cartesian product of both tables | No join condition | Every student-course combination |
| SELF JOIN | Table joined with itself using aliases | As per join type | Employees and their managers (same table) |
All JOIN types on the same dataset
-- Student(StudentID, Name, DeptID) Department(DeptID, DeptName)
-- Some students have DeptID=NULL, some departments have no students
-- INNER: only students WITH a department
SELECT s.Name, d.DeptName
FROM Student s INNER JOIN Department d ON s.DeptID = d.DeptID;
-- LEFT: all students, NULL DeptName if no department
SELECT s.Name, d.DeptName
FROM Student s LEFT JOIN Department d ON s.DeptID = d.DeptID;
-- CROSS: every student-department combination
-- 5 students × 3 departments = 15 rows
SELECT s.Name, d.DeptName FROM Student s CROSS JOIN Department d;
-- SELF: find students in the same department
SELECT s1.Name AS S1, s2.Name AS S2, d.DeptName
FROM Student s1
JOIN Student s2 ON s1.DeptID = s2.DeptID AND s1.StudentID < s2.StudentID
JOIN Department d ON s1.DeptID = d.DeptID;Subqueries and CTEs
Correlated subquery, EXISTS, and CTEs
-- 1. Uncorrelated subquery (runs once)
SELECT Name, GPA FROM Student
WHERE GPA > (SELECT AVG(GPA) FROM Student);
-- 2. Correlated subquery (runs once per row of outer query)
-- Students with GPA above their department average
SELECT s.Name, s.GPA FROM Student s
WHERE s.GPA > (
SELECT AVG(GPA) FROM Student WHERE DeptID = s.DeptID
);
-- 3. EXISTS (preferred over IN for large tables — short-circuits)
SELECT Name FROM Student s
WHERE EXISTS (
SELECT 1 FROM Department d
WHERE d.DeptID = s.DeptID AND d.Building = 'Tech Block'
);
-- 4. NOT EXISTS: students with no enrollment
SELECT Name FROM Student s
WHERE NOT EXISTS (SELECT 1 FROM Enrollment e WHERE e.StudentID = s.StudentID);
-- 5. CTE (Common Table Expression) — readable named subquery
WITH DeptAvg AS (
SELECT DeptID, AVG(GPA) AS AvgGPA
FROM Student WHERE Status = 'Active'
GROUP BY DeptID
)
SELECT s.Name, s.GPA, d.DeptName
FROM Student s
JOIN Department d ON s.DeptID = d.DeptID
JOIN DeptAvg da ON s.DeptID = da.DeptID
WHERE s.GPA > da.AvgGPA
ORDER BY s.GPA DESC;
-- 6. Recursive CTE: org chart traversal
WITH RECURSIVE OrgChart AS (
SELECT EmpID, Name, ManagerID, 1 AS Level
FROM Employee WHERE ManagerID IS NULL -- root (top manager)
UNION ALL
SELECT e.EmpID, e.Name, e.ManagerID, oc.Level + 1
FROM Employee e JOIN OrgChart oc ON e.ManagerID = oc.EmpID
)
SELECT * FROM OrgChart ORDER BY Level, Name;Practice questions
- Difference between WHERE and HAVING? (Answer: WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER GROUP BY. Aggregate functions (SUM, COUNT, AVG) cannot go in WHERE.)
- LEFT JOIN returns 100 rows, INNER JOIN returns 60 rows. What does this tell you? (Answer: 40 rows in the left table have no matching rows in the right table — those appear with NULLs in the LEFT JOIN only.)
- Is a correlated subquery more or less efficient than uncorrelated? (Answer: Less efficient — runs once per row of outer query. For 10,000 outer rows, runs 10,000 times. Use EXISTS or JOIN as alternatives.)
- CROSS JOIN between tables with 4 and 6 rows produces how many rows? (Answer: 4 × 6 = 24 rows — Cartesian product.)
- Find the second highest salary in SQL. (Answer: SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee). Or: SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1.)
On LumiChats
LumiChats generates any SQL query from plain English: 'Find all students who scored above the department average' → generates the correlated subquery automatically with step-by-step explanation.
Try it free