SQL Concepts Explained with Examples
Short lessons on the ideas SQL interviews test most. Each one explains the concept in plain English, shows worked examples you can edit and run in your browser, lists the mistakes candidates make, and links to practice questions. Every example works in both MySQL 8 and PostgreSQL.
WHERE, ORDER BY & LIMIT
Learn how SQL WHERE filters rows, ORDER BY sorts them and LIMIT keeps the top N, with runnable examples, NULL handling and common interview mistakes.
NULL & COALESCE
Learn how NULL behaves in SQL comparisons, counts and joins, how COALESCE and NULLIF replace or avoid NULLs, and the NULL traps interviewers love to test.
GROUP BY
Understand SQL GROUP BY: how rows collapse into groups, COUNT, SUM and AVG per group, NULL groups and the classic GROUP BY error, with runnable examples.
CASE WHEN
Learn SQL CASE WHEN to label rows, count matching rows, compute percentages and pivot rows into columns, with runnable MySQL and PostgreSQL examples.
HAVING vs WHERE
WHERE filters rows before grouping, HAVING filters groups after aggregation. Learn when to use each with runnable SQL examples and common interview mistakes.
INNER JOIN vs LEFT JOIN
See exactly which rows INNER JOIN and LEFT JOIN keep, why unmatched rows become NULL, and how a WHERE clause can silently turn a LEFT JOIN into an INNER JOIN.
Self Join
Learn how a SQL self join works using an employee-manager example, why each copy needs its own alias, and when to use LEFT JOIN for rows without a match.
Subqueries
Learn SQL subqueries with runnable examples: compare against an overall average, write correlated subqueries per group and find missing matches with NOT EXISTS.
CTEs (WITH clause)
Learn SQL common table expressions: name steps with WITH, chain several CTEs and combine them with joins and window functions, with runnable examples.
UNION vs UNION ALL
Learn the difference between SQL UNION and UNION ALL, the rules for combining queries, and how to build a ledger from several tables, with runnable examples.
ROW_NUMBER vs RANK vs DENSE_RANK
See the difference between ROW_NUMBER, RANK and DENSE_RANK on tied values, how PARTITION BY ranks within groups, and how to get the top row per group.
LAG and LEAD
Learn SQL LAG and LEAD to compare each row with the previous or next one: change from the last order, next order per customer and month-over-month growth.
Running Totals & Moving Averages
Compute running totals and moving averages in SQL with SUM and AVG OVER, PARTITION BY and window frames like ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
Gaps and Islands
Solve the gaps and islands problem in SQL: find consecutive login streaks with ROW_NUMBER, group each streak, and return the longest streak per user.
Date Functions
Work with dates in SQL: EXTRACT years and months, filter date ranges safely, group revenue by month, and see how MySQL and PostgreSQL date functions differ.
Ready to practise? Pick a study plan or browse all interview questions.