DBMS & SQL›Level 14
PART 3 • SQL MASTERY

Combine Tables without Losing Control of Rows

Match related records, preserve intentional non-matches and combine compatible results while predicting duplicates and cardinality.

Level 14 of 18Relational SQL160–200 minutes
BY THE END, YOU CAN
  • Trace inner and outer joins.
  • Place predicates correctly.
  • Predict row multiplication.
  • Write self and cross joins.
  • Compare UNION and UNION ALL.
  • Use INTERSECT and EXCEPT.
01 • MATCH RELATED ROWS

A Join Combines Rows When Its ON Condition Is TRUE

SELECT s.name, d.dept_name
FROM students AS s
JOIN departments AS d
  ON d.dept_id = s.dept_id;
Left input

Student rows carry a department foreign key.

Right input

Department rows provide descriptive attributes.

Match rule

d.dept_id = s.dept_id decides which pairs qualify.

02 • CHOOSE WHICH NON-MATCHES SURVIVE

Join Type Defines Row Preservation

INNER JOINMatched pairs only

Students without a matching department and departments without students disappear.

LEFT JOINEvery left row

Unmatched left rows survive with NULL values for right-side columns.

RIGHT JOINEvery right row

Equivalent logic can often be written as a LEFT JOIN after swapping table order.

FULL OUTER JOINEvery row from both sides

Preserves left-only, matched and right-only rows where supported.

INNER = intersection of matchesLEFT = preserve first tableRIGHT = preserve second tableFULL = preserve both tables
03 • KEEP OUTER JOINS OUTER

ON and WHERE Can Produce Different Results

PREDICATE IN ON
LEFT JOIN departments d
 ON d.dept_id = s.dept_id
AND d.active = TRUE

Every student remains; non-active or absent departments appear as NULL on the right.

PREDICATE IN WHERE
LEFT JOIN departments d
 ON d.dept_id = s.dept_id
WHERE d.active = TRUE

Rows with no qualifying right side fail WHERE, often making the result behave like an inner join.

04 • PREDICT RESULT SIZE

Each Matching Pair Becomes One Result Row

1 : 1

At most one right match per left row.

1 : many

One parent repeats once for every matching child.

many : many

Matching rows can multiply rapidly; a bridge table usually represents the relationship.

Missing predicate

Every left row pairs with every right row: m × n rows.

Debug unexpected duplicates at the relationship level

Do not immediately add DISTINCT. Inspect the join key, its uniqueness and the expected cardinality. DISTINCT can hide an incorrect join while leaving the logic wrong.

05 • SPECIAL JOIN SHAPES

Self Joins Compare Roles; Cross Joins Generate Combinations

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

Aliases give two logical roles to the same table.

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

Produces every combination. Useful deliberately; dangerous accidentally.

06 • COMBINE COMPLETE RESULT ROWS

Set Operations Stack Compatible Query Results

UNION

Returns rows from either input and removes duplicates.

UNION ALL

Returns every row from both inputs and preserves duplicates.

INTERSECT

Returns rows present in both inputs.

EXCEPT

Returns rows in the first input but not the second. Some products use MINUS.

Same number of columnsPositionally compatible typesColumn names normally come from the first queryOne final ORDER BY for the combined result
07 • TRACE EVERY MATCH

Interactive Join Visualizer

Switch join types and inspect matching pairs, preserved rows, NULL padding and exact result size.

GENERATED SQL
08 • COMPARE RESULT SETS

Set-Operation Laboratory

SET A — workshop registrationsAIMLCSEAIMLECE
SET B — internship applicationsCSEITAIMLIT
09 • CHECK YOUR UNDERSTANDING

Ten Formative Concept Checks

1. INNER JOIN returns:

2. LEFT JOIN preserves:

3. CROSS JOIN of 3 and 4 rows returns:

4. Unmatched outer-join columns contain:

5. Unexpected join duplicates should first prompt you to inspect:

6. UNION differs from UNION ALL because UNION:

7. INTERSECT returns rows:

8. EXCEPT returns rows:

9. A self join needs aliases mainly to:

10. A right-table condition in WHERE after LEFT JOIN may:

Answered correctly: 0 of 10
10 • EXPLAIN & PREPARE

University and Interview Questions

2-MARK
  1. Define join.
  2. INNER versus LEFT?
  3. What is a self join?
  4. UNION versus UNION ALL?
5-MARK / PRACTICAL
  1. Trace all outer joins.
  2. Explain row multiplication.
  3. Compare ON and WHERE filters.
  4. Write all set operations.
INTERVIEW
  1. Why does a join duplicate rows?
  2. How do you find missing matches?
  3. When is CROSS JOIN useful?
  4. What makes set inputs compatible?
Show the join answer framework
  1. Name both row sources and their grain.
  2. State expected cardinality.
  3. Write the exact match predicate.
  4. Choose which unmatched side must survive.
  5. Place filters at the intended stage.
  6. Predict result count and NULL padding.
  7. Validate duplicates against keys.
LEVEL 14 SUMMARY

You Can Now Combine Data Predictably

  • Joins create one row per qualifying pair.
  • Outer joins preserve designated non-matches.
  • Cardinality explains row multiplication.
  • Self joins assign roles to one table.
  • Set operations combine compatible complete rows.
  • UNION removes duplicates; UNION ALL retains them.
COURSE CHECKPOINT

Mark this level when you can predict every matched, unmatched and duplicate row.

Saved in this browser only.