Joins, set operations and nested queries
Clear, exam-focused notes on SQL joins (inner, outer, cross, natural), set operations (UNION, INTERSECT, EXCEPT/MINUS) and nested queries (subqueries, correlated and non-correlated) with definitions, laws, syntax, worked SQL examples and common mistakes.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
Database engineers use joins, set operations and nested queries every day to combine tables, remove duplicates, compare query results and express complex filters. These constructs let you write concise, efficient queries for reporting, data-cleaning, OLTP features and analytics.
Key ideas
- Cartesian product (cross join): R × S contains all ordered pairs (r, s). If R has m rows and S has n rows, R × S has m·n rows. Draw: two rectangles labelled R and S; make a grid of m by n cells.
- Theta-join: R ⋈{θ} S = σ{θ}(R × S). Here θ is any condition (e.g., R.a > S.b).
- Equi-join: special theta-join where θ uses only equality comparisons between attributes (R.a = S.b).
- Natural join (R ⋈ S): equi-join on all attributes with the same name, and then remove duplicate columns. Equivalent definition: R ⋈ S = π_{attributes}(σ_{for each common attr a: R.a = S.a}(R × S)). In exam diagrams: draw two circles labelled R and S, mark the common attributes, shade the intersection region and then project away duplicate columns.
- Inner join: same as theta-join — only matching row pairs appear; an equi-join is a special case that uses only equality predicates. Venn: shade only intersection.
- Outer joins: keep non-matching rows from one or both relations, padding with NULLs.
- Left outer join (R LEFT JOIN S): return all rows of R; for R rows with no matching S, S columns are NULL. Venn: shade entire left circle (intersection + left-only).
- Right outer join (R RIGHT JOIN S): symmetric: keeps all of S.
- Full outer join (R FULL OUTER JOIN S): keep all rows from both R and S; unmatched parts padded with NULL. Venn: shade union of both circles.
- Join properties:
- Inner join is commutative and associative under standard relational algebra (R ⋈ S = S ⋈ R, (R ⋈ S) ⋈ T = R ⋈ (S ⋈ T)), assuming attribute names and projections handled consistently.
- Outer joins are not generally commutative or associative; careful with order.
- Set operations (require same number of columns and compatible types in corresponding columns): UNION, UNION ALL, INTERSECT, EXCEPT (also called MINUS in some systems).
- UNION: all distinct rows from both queries (duplicates removed).
- UNION ALL: all rows from both queries, duplicates kept.
- INTERSECT: rows common to both queries (duplicates removed).
- EXCEPT / MINUS: rows in first query but not in second (duplicates removed).
- Draw Venn diagrams: UNION = union; INTERSECT = intersection; EXCEPT(A,B) = A minus overlap.
- Nested queries (subqueries): a query inside another. Types:
- Non-correlated subquery: independent; can be evaluated once.
- Correlated subquery: refers to outer query columns; evaluated per outer-row.
- Scalar subquery: returns single value.
- Single-row subquery: returns at most one row; used with =, <, >.
- Multi-row subquery: returns many rows; used with IN, ANY, ALL, EXISTS.
- EXISTS tests existence of any row from subquery; returns true/false. Often more efficient than IN for some correlated forms.
Exact derivation universities ask for (natural join from product):
- Start with R × S (all pairs).
- Apply selection σ_{for each common attr a: R.a = S.a} to keep only matching attribute values.
- Project to remove duplicate columns: π_{all attribute names once}( ... ). Thus natural join = projection of selection on Cartesian product.
Formulas (syntax to remember)
SELECT ... FROM A JOIN B ON A.x = B.x; — inner join (JOIN default is INNER).
SELECT ... FROM A LEFT JOIN B ON A.x = B.x; — left outer join.
SELECT ... FROM A RIGHT JOIN B ON A.x = B.x; — right outer join.
SELECT ... FROM A FULL OUTER JOIN B ON A.x = B.x; — full outer join (not supported in some DBMSs; check vendor support).
SELECT ... FROM A CROSS JOIN B; — Cartesian product.
SELECT ... FROM A NATURAL JOIN B; — natural join (matches same-named columns).
SELECT ... FROM A JOIN B USING (col); — join using column name shared by both.
(SELECT ... ) UNION (SELECT ... ); — set union (duplicates removed).
(SELECT ... ) UNION ALL (SELECT ... ); — set union keeping duplicates.
(SELECT ... ) INTERSECT (SELECT ... ); — intersection of results (duplicates removed).
(SELECT ... ) EXCEPT (SELECT ... ); — rows in Q1 not in Q2 (MINUS in some DBs; duplicates removed).
SELECT ... FROM T WHERE col IN (SELECT ...); — IN with subquery.
SELECT ... FROM T WHERE EXISTS (SELECT 1 FROM ... WHERE ...); — EXISTS correlated or non-correlated.
SELECT ... FROM T WHERE col > ALL (SELECT ...); — compare to all values (true only if greater than every value; vacuously true if the subquery returns no rows).
SELECT ... FROM T WHERE col > ANY (SELECT ...); — compare to any value (true if greater than at least one value; ANY is a synonym for SOME).
(Each above line is standard SQL syntax; vendor variations exist, e.g., MINUS vs EXCEPT, FULL JOIN support.)
Worked examples
We use these small tables. SQL shown is standard ANSI SQL.
Create and populate:
CREATE TABLE STUDENT(sid INT PRIMARY KEY, sname VARCHAR(20), dept VARCHAR(10));
CREATE TABLE ENROLL(sid INT, cid INT, grade INT);
CREATE TABLE COURSE(cid INT PRIMARY KEY, cname VARCHAR(30));
INSERT INTO STUDENT VALUES (1,'Alice','CSE'),(2,'Bob','ECE'),(3,'Carol','CSE'),(4,'Dave','ME'),(5,'Eve','BIO');
INSERT INTO COURSE VALUES (10,'DataStructures'),(20,'Signals'),(30,'Thermodynamics'),(40,'AI'),(50,'Robotics');
INSERT INTO ENROLL VALUES (1,10,9),(1,40,8),(2,20,7),(3,10,8),(4,30,6),(6,50,5);
Note: ENROLL has a row with sid=6 (no matching STUDENT) to show outer-join behavior.
Example 1 — Inner join: list student names with course ids they enrolled in.
SELECT s.sid, s.sname, e.cid
FROM STUDENT s JOIN ENROLL e ON s.sid = e.sid;
Output (rows): 1, Alice, 10 1, Alice, 40 2, Bob, 20 3, Carol, 10 4, Dave, 30 (ENROLL row with sid=6 is not shown because no STUDENT exists — inner join excludes it.)
Example 2 — Left outer join: show all students and their course ids (NULL if none).
SELECT s.sid, s.sname, e.cid
FROM STUDENT s LEFT JOIN ENROLL e ON s.sid = e.sid
ORDER BY s.sid;
Output: 1, Alice, 10 1, Alice, 40 2, Bob, 20 3, Carol, 10 4, Dave, 30 5, Eve, NULL
Example 3 — Full outer join (show unmatched enrollments too):
SELECT s.sid, s.sname, e.sid AS enroll_sid, e.cid
FROM STUDENT s FULL OUTER JOIN ENROLL e ON s.sid = e.sid
ORDER BY COALESCE(s.sid, e.sid);
Output rows (order by numeric sid): 1, Alice, 1, 10 1, Alice, 1, 40 2, Bob, 2, 20 3, Carol, 3, 10 4, Dave, 4, 30 5, Eve, NULL, NULL NULL, NULL, 6, 50 (The last row shows the ENROLL with sid=6 that has no matching STUDENT.)
Example 4 — Set operations and duplicates: Q1 returns student names from enrollments (duplicates allowed):
SELECT s.sname FROM STUDENT s JOIN ENROLL e ON s.sid=e.sid;
This yields: Alice, Alice, Bob, Carol, Dave, (and no Eve). If you need unique names:
SELECT sname FROM STUDENT s JOIN ENROLL e ON s.sid=e.sid
UNION
SELECT sname FROM STUDENT WHERE dept='CSE';
This returns distinct names from both queries (UNION removes duplicates).
Example 5 — Correlated subquery: students who scored above the average grade of the same course.
SELECT DISTINCT st.sname
FROM STUDENT st JOIN ENROLL e ON st.sid = e.sid
WHERE e.grade > (
SELECT AVG(e2.grade) FROM ENROLL e2 WHERE e2.cid = e.cid
);
Compute averages per cid: cid=10 avg=(9+8)/2 = 8.5; cid=40 avg=8; cid=20 avg=7; cid=30 avg=6; cid=50 avg=5. Rows with grade > avg: student 1 in cid 10 (9>8.5). So output: Alice.
Example 6 — IN / EXISTS difference (find students who took DataStructures cid=10): IN version:
SELECT sname FROM STUDENT WHERE sid IN (SELECT sid FROM ENROLL WHERE cid=10);
Output: Alice, Carol EXISTS (correlated) version:
SELECT sname FROM STUDENT st WHERE EXISTS (
SELECT 1 FROM ENROLL e WHERE e.sid=st.sid AND e.cid=10
);
Output: Alice, Carol
Common mistakes
- Forgetting set-compatibility rules: UNION/INTERSECT/EXCEPT require same number of columns and compatible types.
- Expecting UNION to keep duplicates (UNION removes duplicates; use UNION ALL when duplicates are needed).
- Using IN with a subquery that can return NULLs without understanding NULL semantics.
- Confusing MINUS vs EXCEPT — different DB vendors use different keywords (verify for your DB).
- Expecting correlated subqueries to be evaluated only once — they are evaluated per outer row unless the optimizer rewrites.
- Relying on ordering of rows without ORDER BY after set operations.
For your exam
Question types you will see:
- Definitions: define join types, nested query types, set operations — include formal conditions and diagrams.
- Short notes: differences between INNER and OUTER joins, when to use EXISTS vs IN, set operation properties.
- Derivations: show natural join = projection of selection over Cartesian product (write the selection and projection expressions).
- SQL writing: write queries using joins, set ops, and correlated subqueries — give table schemas, expected outputs.
- Diagrams: draw Venn diagrams or Cartesian grids and label shaded regions (intersection for inner join, left+intersection for left join, etc.). A full-marks answer includes: correct formal definition, clear diagram labelled, correct SQL or algebra expression, and an explanation of edge cases (NULLs, duplicates, set-compatibility).
Quick check
- Which join returns rows from the left table even when there is no matching row in the right table?
- What is the difference between UNION and UNION ALL?
- Is a correlated subquery evaluated once or per outer-row (in general)?
Answers: 1) Left outer join. 2) UNION removes duplicate rows; UNION ALL keeps duplicates. 3) Correlated subquery is evaluated per outer-row (unless the optimizer rewrites it).
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?