SQL · Lesson 8 of 8

Subqueries & CTEs

Queries inside queries, and readable WITH clauses.

A subquery is a SELECT inside another statement. Use it to compare against a calculated value — “jobs paying more than the average” — or with IN to match a list built by another query.

A CTE (Common Table Expression) names a subquery with WITH name AS (…) at the top, so long queries read step by step.

Many interview questions — second-highest salary, top-N per group, duplicates — are easiest with a subquery or CTE.

Try it yourself

Code editor

Press Run to see the results.

Runs on a small fictional sample database (companies, jobs) that is rebuilt on every run, so you can't break anything.

Key points

Subqueries can return one value or a list.
WITH … AS names a step for readability.
Great for 'compare with an aggregate' questions.

Quick check

Which keyword starts a CTE?

CTE WITH DEFINE AS

Practice task

List companies that have at least one job paying 8 LPA or more, using WHERE id IN (subquery).

Interview corner

Correlated vs non-correlated subquery?

A non-correlated subquery runs once on its own. A correlated subquery refers to the outer row, so it is evaluated per row — often rewritten as a JOIN for speed.

How do you find duplicate emails in a table?

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;