SQL and Advanced SQL

Learning Objectives

• Write SELECT queries that filter with WHERE, sort with ORDER BY, and aggregate with GROUP BY/HAVING, and explain why HAVING exists separately from WHERE.
• Distinguish INNER, LEFT, RIGHT, FULL OUTER, self, and CROSS joins, and choose the correct one for a given two-table (or one-table) question.
• Write correlated and non-correlated subqueries in the WHERE, SELECT, and FROM clauses, and know when a join would be a better choice.
• Combine result sets with UNION, UNION ALL, INTERSECT, and EXCEPT, and explain the column-compatibility rules they require.
• Use window functions (ROW_NUMBER, RANK, DENSE_RANK, PARTITION BY) to solve per-group ranking problems that GROUP BY alone cannot solve.
• Write a Common Table Expression (CTE), including a recursive CTE, and explain why it is more readable than a nested subquery.
• Explain what views, stored procedures, and triggers are for, and identify a scenario where each is the right tool.

Quick Answer

SQL (Structured Query Language) is how you ask a relational database for data and how you shape that data once it's there. Beyond basic SELECT/WHERE/ORDER BY, advanced SQL gives you tools for questions that a single flat query can't answer: joins combine data spread across tables, subqueries and CTEs let you build a query out of smaller named steps, set operations combine or contrast whole result sets, and window functions let you rank or compare rows within a group without collapsing them the way GROUP BY does. Views, stored procedures, and triggers move logic into the database itself so it runs consistently no matter which application touches the data. Mastering these isn't about memorizing syntax — it's about recognizing which tool fits which shape of question, because most real reporting and analytics queries are really just joins, subqueries, or window functions in different combinations.

SQL Fundamentals Recap

Before going further, it's worth being precise about what SELECT actually does, because every advanced feature in this page is built on top of it.

SELECT department, name, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC;

Conceptually, a database engine processes this query in roughly this order — not the order you write the clauses, which trips up a lot of beginners:

1. FROM — pick the source table(s).
2. WHERE — filter individual rows.
3. GROUP BY — bucket the remaining rows.
4. HAVING — filter the buckets.
5. SELECT — choose which columns/expressions to return.
6. ORDER BY — sort the final result.

This ordering explains a rule that confuses almost every SQL student: you can't reference a column alias defined in SELECT inside the WHERE clause, because WHERE runs before SELECT even exists yet. It also explains why HAVING — not WHERE — is the clause that filters on aggregates: aggregates like COUNT(*) don't exist until after GROUP BY has run, and WHERE has already finished by then.

GROUP BY and HAVING

-- Departments with more than 5 employees
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

WHERE would filter individual employee rows before grouping (e.g., "only employees hired after 2020"). HAVING filters the grouped results after aggregation (e.g., "only departments with more than 5 people"). Using them interchangeably is one of the most common SQL mistakes — see Common Mistakes below.

Joins: Combining Data Across Tables

Real schemas are normalized, which means related information lives in separate tables. Joins are how you put it back together for a query. Assume this schema for every example below:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    student_id  INT,
    item        VARCHAR(100),
    amount      DECIMAL(8,2),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

• INNER JOIN — only rows with a match in both tables. A student who never placed an order disappears from the result entirely.

  SELECT s.name, o.item, o.amount
  FROM students s
  INNER JOIN orders o ON s.student_id = o.student_id;

• LEFT JOIN — every row from the left table (students), plus matching rows from the right table where they exist, NULL where they don't. This is the join you reach for when the question is "for every X, show me its Y if it has one."

  SELECT s.name, o.item
  FROM students s
  LEFT JOIN orders o ON s.student_id = o.student_id;

• RIGHT JOIN — the mirror image: every row from the right table, matches from the left where they exist. In practice, most people rewrite a RIGHT JOIN as a LEFT JOIN with the tables swapped, because it reads more naturally.

  SELECT s.name, o.item
  FROM students s
  RIGHT JOIN orders o ON s.student_id = o.student_id;

• FULL OUTER JOIN — every row from both tables, with NULLs wherever there's no match on either side. Useful for finding all mismatches at once — students with no orders and orders with a dangling/invalid student reference (which shouldn't happen if the foreign key is enforced, but is common in messy real-world data or after a failed migration).

  SELECT s.name, o.item
  FROM students s
  FULL OUTER JOIN orders o ON s.student_id = o.student_id;

• SELF JOIN — a table joined to itself, used when rows relate to other rows in the same table. Classic case: employees and their managers, both stored in one employees table.

  SELECT e.name AS employee, m.name AS manager
  FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.employee_id;

• CROSS JOIN — every row from one table paired with every row from the other (a Cartesian product), with no ON condition. Rarely what you want by accident — usually deliberate, e.g., generating all possible size/color combinations for a product catalog.

  SELECT sizes.size, colors.color
  FROM sizes
  CROSS JOIN colors;

A join question to ask yourself every time: do I want rows that might be missing a match, or only rows guaranteed to match? That single question decides between INNER and one of the OUTER joins.

Subqueries

A subquery is a query nested inside another query, used to compute an intermediate value or result set that the outer query then uses.

Non-correlated subquery (in WHERE)

The inner query runs once, independently of the outer query.

-- Students who spent more than the average order amount
SELECT name FROM students
WHERE student_id IN (
    SELECT student_id FROM orders
    WHERE amount > (SELECT AVG(amount) FROM orders)
);

Correlated subquery

The inner query references a column from the outer query, so it conceptually re-runs once per row of the outer query.

-- Students whose orders exceed the average order amount for their own department
SELECT s.name
FROM students s
WHERE s.student_id IN (
    SELECT o.student_id
    FROM orders o
    JOIN students s2 ON o.student_id = s2.student_id
    WHERE s2.department = s.department
    GROUP BY o.student_id
    HAVING AVG(o.amount) > 100
);

Correlated subqueries are powerful but can be slow on large tables because, conceptually, the engine re-evaluates the inner query for every outer row (real optimizers often rewrite these into joins internally, but you shouldn't rely on that). If a subquery can be rewritten as a join, it's usually worth doing so for both readability and performance.

Subquery in SELECT and FROM

-- Subquery in SELECT: add a computed column per row
SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.student_id = s.student_id) AS order_count
FROM students s;

-- Subquery in FROM (a "derived table"): treat a query result as a table
SELECT dept_totals.department, dept_totals.total
FROM (
    SELECT s.department, SUM(o.amount) AS total
    FROM students s
    JOIN orders o ON s.student_id = o.student_id
    GROUP BY s.department
) AS dept_totals
WHERE dept_totals.total > 500;

Set Operations

Set operations combine the results of two SELECT statements that return the same number of columns with compatible types — they operate on whole rows, not on individual columns like a join does.

-- Students who are either currently enrolled OR alumni (combine, remove duplicates)
SELECT name FROM current_students
UNION
SELECT name FROM alumni;

-- Same, but keep duplicates (faster, since no de-duplication pass is needed)
SELECT name FROM current_students
UNION ALL
SELECT name FROM alumni;

-- Students who are in BOTH lists
SELECT name FROM current_students
INTERSECT
SELECT name FROM alumni;

-- Students who are current students but NOT alumni
SELECT name FROM current_students
EXCEPT
SELECT name FROM alumni;

UNION removes duplicate rows by default (an implicit sort/de-duplication step); UNION ALL does not, and is meaningfully faster on large result sets when you know duplicates either don't exist or don't matter. INTERSECT and EXCEPT are less commonly taught but are exactly the set operations they sound like, and they're often clearer than the equivalent JOIN/NOT IN gymnastics.

Aggregate Functions

COUNT, SUM, AVG, MIN, and MAX collapse many rows into one value, either over the whole table or per group.

SELECT
    COUNT(*)          AS total_orders,
    SUM(amount)        AS revenue,
    AVG(amount)        AS avg_order,
    MIN(amount)        AS smallest_order,
    MAX(amount)        AS largest_order
FROM orders;

COUNT(*) counts rows including NULLs; COUNT(column_name) counts only non-NULL values in that column — a distinction that matters whenever a column is optional, and a favorite exam trick question.

Views

A view is a saved query that behaves like a virtual table — it doesn't store data itself (unless it's a materialized view), it re-runs its underlying query every time you select from it.

CREATE VIEW high_value_orders AS
SELECT s.name, o.item, o.amount
FROM students s
JOIN orders o ON s.student_id = o.student_id
WHERE o.amount > 200;

SELECT * FROM high_value_orders WHERE name = 'Asha Rao';

Views are useful for two reasons that come up constantly in real systems: they hide complexity (analysts query high_value_orders without needing to know the underlying join), and they can restrict access (grant a user SELECT on a view that only exposes certain columns, instead of the full underlying table).

Stored Procedures and Functions

A stored procedure is a named, precompiled block of SQL (often with parameters and procedural logic) stored inside the database and invoked by name, instead of sent as raw SQL text every time.

CREATE PROCEDURE GetStudentOrders(IN p_student_id INT)
BEGIN
    SELECT o.item, o.amount
    FROM orders o
    WHERE o.student_id = p_student_id;
END;

CALL GetStudentOrders(101);

A function is similar but returns a single value and can be used inline inside a query, the way a built-in function like UPPER() can:

CREATE FUNCTION TotalSpent(p_student_id INT) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(amount) INTO total FROM orders WHERE student_id = p_student_id;
    RETURN total;
END;

SELECT name, TotalSpent(student_id) AS spent FROM students;

Stored procedures push logic into the database layer, which guarantees every application that touches the data goes through the same validated logic — no risk of one app's code implementing a slightly different business rule than another's.

Triggers

A trigger is a block of SQL that the database runs automatically in response to an INSERT, UPDATE, or DELETE on a table — you never call it directly.

CREATE TRIGGER trg_log_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, action, action_time)
    VALUES (NEW.order_id, 'INSERT', NOW());
END;

Triggers are the right tool when a rule must hold no matter which application or user makes the change — audit logging, enforcing a business invariant, or keeping a denormalized summary column in sync. They're the wrong tool when overused: because they run invisibly, a schema with many chained triggers becomes very hard to reason about, and debugging "why did this row change?" turns into archaeology.

Window Functions

GROUP BY collapses rows into one row per group. Window functions do the opposite: they compute an aggregate or ranking per row, while still showing every original row — this is the key difference that unlocks a whole class of "top N per group" questions that GROUP BY genuinely cannot answer on its own.

SELECT
    name,
    department,
    salary,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

• PARTITION BY resets the calculation within each group (here, each department) — think of it as a GROUP BY that doesn't collapse rows.
• ROW_NUMBER() gives every row a unique, sequential number, even if salaries tie.
• RANK() gives tied rows the same rank, then skips the next rank number (1, 1, 3, ...).
• DENSE_RANK() gives tied rows the same rank, but does not skip the next number (1, 1, 2, ...).

A very common real query built on this — "top 3 earners per department":

SELECT * FROM (
    SELECT name, department, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn <= 3;

Note the window function had to be wrapped in a subquery before filtering — you cannot use ROW_NUMBER() directly inside a WHERE clause, because (per the execution order discussed earlier) WHERE runs before window functions are evaluated.

Common Table Expressions (CTEs)

A CTE is a named, temporary result set defined with WITH, scoped to a single statement. It exists purely for readability and reuse — anything a CTE can do, a subquery can also do, but the CTE reads top-to-bottom like a sequence of named steps instead of nested parentheses.

WITH department_totals AS (
    SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department
),
above_avg_departments AS (
    SELECT department, total_salary
    FROM department_totals
    WHERE total_salary > (SELECT AVG(total_salary) FROM department_totals)
)
SELECT * FROM above_avg_departments;

Recursive CTEs

A recursive CTE refers to itself, which makes it the standard tool for traversing hierarchical data — org charts, category trees, bill-of-materials structures — that a fixed number of joins can't handle because the depth isn't known in advance.

WITH RECURSIVE org_chain AS (
    -- Anchor member: the top-level person (no manager)
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: join back to org_chain to go one level deeper
    SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chain oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chain ORDER BY level;

The anchor member runs once to seed the recursion; the recursive member then repeatedly joins against the previous iteration's output until it produces zero new rows, at which point the recursion stops. Forgetting a terminating condition (or writing a recursive member that always matches more rows) causes an infinite loop — most database engines cap recursion depth to protect against exactly this.

Indexes (Brief Note)

An index is a separate, ordered structure (commonly a B-tree) that lets the engine jump to matching rows instead of scanning the whole table — the same role a book's index plays for its pages.

CREATE INDEX idx_orders_student_id ON orders (student_id);

Indexes dramatically speed up WHERE, JOIN, and ORDER BY on the indexed column, but they aren't free: every INSERT/UPDATE/DELETE must also update the index, and each index consumes extra storage. Indexing every column "just in case" is a common beginner mistake — it slows down writes for a benefit that only shows up on the columns actually filtered or joined on frequently.

Key Terms

Term	Definition
Join	An operation that combines rows from two or more tables based on a related column.
Correlated subquery	A subquery that references a column from the outer query, conceptually re-evaluated per outer row.
Non-correlated subquery	A subquery that can be evaluated independently of the outer query, typically once.
CTE (Common Table Expression)	A named, temporary result set defined with WITH, scoped to one statement, used for readability and recursion.
Recursive CTE	A CTE that refers to itself to traverse hierarchical or unknown-depth data.
Window function	A function that computes a value per row over a "window" of related rows, without collapsing them like GROUP BY.
PARTITION BY	The clause in a window function that resets the calculation within each group.
View	A saved query that behaves like a virtual table, re-executed each time it's queried.
Stored procedure	A named, precompiled block of SQL logic, stored in the database and invoked by name.
Trigger	A block of SQL the database runs automatically in response to an INSERT, UPDATE, or DELETE.
Set operation	UNION, UNION ALL, INTERSECT, or EXCEPT — combining whole result sets with compatible columns.
Index	An auxiliary structure (commonly a B-tree) that speeds up row lookups at the cost of extra storage and write overhead.

Common Mistakes

Misconception 1: "WHERE and HAVING are interchangeable ways to filter." Why it's wrong: WHERE filters individual rows before grouping happens; HAVING filters groups after aggregation. Trying to write WHERE COUNT(*) > 5 fails in most databases because COUNT(*) doesn't exist yet at the point WHERE runs. Correct understanding: Use WHERE to filter raw rows on non-aggregated columns, and HAVING to filter grouped results on aggregate expressions like COUNT, SUM, or AVG.

Misconception 2: "A LEFT JOIN and an INNER JOIN return the same rows if there are matches for everything." Why it's wrong: Students often test a LEFT JOIN against sample data where every row happens to match, conclude it behaves identically to INNER JOIN, and then get surprised in production when unmatched rows show up as extra rows with NULLs instead of disappearing. Correct understanding: INNER JOIN always excludes unmatched rows; LEFT JOIN always keeps every row from the left table regardless of whether a match exists. They only look identical when, by coincidence, every row in the sample data has a match.

Misconception 3: "A subquery is always slower than a join, so subqueries should be avoided." Why it's wrong: Non-correlated subqueries (like one computing a single aggregate value) are often just as fast as a join, and many modern query optimizers rewrite correlated subqueries into joins automatically. The real performance risk is specifically with correlated subqueries on large tables, not subqueries in general. Correct understanding: Judge subqueries case by case — a subquery used for a single independent lookup is fine; a correlated subquery re-evaluated per outer row on a large table is the one worth rewriting as a join.

Comparison and Connections

JOIN Types Compared

Join type	Rows returned	Typical use case
INNER JOIN	Only rows with a match in both tables	"Show me students who have placed orders"
LEFT JOIN	All rows from the left table, matches from the right or NULL	"Show me every student, with their orders if any"
RIGHT JOIN	All rows from the right table, matches from the left or NULL	"Show me every order, with student details if known"
FULL OUTER JOIN	All rows from both tables, NULL where unmatched on either side	"Show me every student and every order, matched where possible"
SELF JOIN	Rows from one table matched against other rows in the same table	"Show me each employee with their manager's name"
CROSS JOIN	Every row from one table paired with every row from the other	"Generate all size/color combinations for a catalog"

Subquery vs. JOIN vs. CTE

Approach	Best for	Readability trade-off
Join	Combining columns from related tables in one flat result	Can get hard to read with many tables chained together
Subquery	A single, self-contained intermediate calculation	Nested subqueries become hard to read a few levels deep
CTE	Multi-step logic, or logic that needs to be reused/reference itself	Reads top-to-bottom like named steps; best for complex or recursive logic

Practice Questions

Recall

1. What is the difference between RANK() and DENSE_RANK() when there are tied values? Answer guidance: RANK() skips the next rank number after a tie (1, 1, 3); DENSE_RANK() does not skip a number after a tie (1, 1, 2).
2. Name the four set operations covered in this page and state what each one does. Answer guidance: UNION (combine, remove duplicates), UNION ALL (combine, keep duplicates), INTERSECT (rows in both), EXCEPT (rows in the first but not the second).

Understanding

3. Explain why WHERE COUNT(*) > 3 is invalid in most SQL engines, but HAVING COUNT(*) > 3 works. Answer guidance: SQL logically evaluates WHERE before GROUP BY runs, so aggregate values like COUNT(*) don't exist yet at that stage; HAVING runs after grouping/aggregation, when COUNT(*) is defined.
4. Why is a LEFT JOIN often chosen over an INNER JOIN even when most rows do have a match? Answer guidance: A LEFT JOIN guarantees every row from the left table appears in the output, which matters for reporting completeness — e.g., you want to see "0 orders" for a student, not have that student silently disappear from the report.

Application

5. Write a query using a window function to find the second-highest-paid employee in each department. Answer guidance:

   SELECT * FROM (
       SELECT name, department, salary,
              DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
       FROM employees
   ) t
   WHERE rnk = 2;

6. A reporting tool needs a list of all students together with their total spend, including students who have never placed an order (shown as 0). Which join and which aggregate handling would you use? Answer guidance: LEFT JOIN from students to orders, then SUM(o.amount) grouped by student; wrap the sum in COALESCE(SUM(o.amount), 0) so students with no orders show 0 instead of NULL.

Analysis

7. Compare using a correlated subquery versus a self-join to find employees who earn more than their manager. Which would you choose, and why? Answer guidance: A self-join (employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.salary > m.salary) is generally clearer and often faster than a correlated subquery re-evaluating per row; the self-join expresses the relationship directly as a table-to-itself join instead of a per-row lookup.
8. A recursive CTE traversing a company's org chart never terminates and eventually errors out. What are two likely causes, and how would you fix each? Answer guidance: (1) A cyclic manager relationship (e.g., two employees each listed as the other's manager) — fix by adding cycle detection or correcting the data. (2) The recursive member's join condition doesn't correctly narrow toward the anchor/base case, so every iteration keeps matching the same or more rows — fix by re-checking the join condition and level-tracking logic so each iteration strictly progresses.

FAQ

Q: When should I use a subquery instead of a join, or vice versa? A: If you just need to combine columns from two related tables, a join is usually the clearest and most efficient choice. Reach for a subquery when you need a single computed value (like an average) to filter against, or when the intermediate step is easier to express as its own named query — a CTE is often the best of both worlds for anything more than one nesting level.

Q: Why does my window function fail when I try to filter on it directly in WHERE? A: Window functions are evaluated after WHERE and GROUP BY in the logical query order, so you can't reference them there. Wrap the query with the window function in a subquery or CTE, then filter in the outer query's WHERE clause.

Q: Is a CTE the same thing as a temporary table? A: No. A CTE only exists for the duration of the single statement it's attached to and is typically not materialized as physical storage (though some engines optimize this differently). A temporary table is a real table that persists for the session or transaction and can be indexed, reused across multiple statements, and explicitly dropped.

Q: Do views make queries faster? A: Not by themselves — a standard view is just a saved query definition, and querying it still re-runs the underlying SQL each time. What views improve is readability, reuse, and access control, not raw performance. A materialized view, by contrast, does store the computed results and can genuinely speed things up, at the cost of the data going stale until it's refreshed.

Q: Why would I use a trigger instead of just putting that logic in my application code? A: Because a trigger runs inside the database regardless of which application, script, or admin tool changes the data — so the rule can never be accidentally bypassed by one code path that forgot to enforce it. Application code can't make that guarantee if there's more than one way to modify the table.

Q: What actually happens if I forget the ON condition in a join? A: If you write a JOIN without any ON condition and without using CROSS JOIN explicitly, most databases will either error out or, in older/looser syntax, produce a full Cartesian product — every row of one table paired with every row of the other. On tables with any real size, this silently produces an enormous, mostly meaningless result set, so it's worth double-checking any join that returns far more rows than expected.

Quick Revision

• Logical query execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• WHERE filters rows before grouping; HAVING filters groups after aggregation.
• INNER JOIN keeps only matches; LEFT/RIGHT JOIN keep all rows from one side plus matches; FULL OUTER JOIN keeps all rows from both sides.
• SELF JOIN relates a table to itself (e.g., employee-manager); CROSS JOIN produces every row combination (Cartesian product).
• Correlated subqueries reference the outer query's columns and conceptually re-run per row; non-correlated subqueries run independently.
• UNION removes duplicates; UNION ALL keeps them and is faster; INTERSECT/EXCEPT find rows common to or exclusive to one result set.
• Window functions (ROW_NUMBER, RANK, DENSE_RANK with PARTITION BY) compute per-row rankings without collapsing rows, unlike GROUP BY.
• CTEs (WITH) make multi-step logic readable top-to-bottom; recursive CTEs traverse hierarchical data via an anchor member plus a repeating recursive member.
• Views save a query as a virtual table for reuse and access control, but don't inherently speed up queries unless materialized.
• Stored procedures and triggers push logic into the database so every application follows the same rules consistently.
• Indexes speed up reads on filtered/joined/sorted columns but add overhead to every write — index deliberately, not exhaustively.

Related Topics

Prerequisites

• Introduction to DBMS (what a DBMS is and why it exists)
• Relational Database Model (tables, keys, and constraints)

Related Topics

• Normalization and Normal Forms
• Transactions and ACID Properties
• Indexing and Query Optimization

Next Topics

• Query Optimization and Execution Plans
• NoSQL and Non-Relational Data Models
• Database Design Principles and ER Modeling