Operations on relations

The essentials

SQL can combine data from two tables using set operations and joins.

Key points

• UNION: rows from both queries, with duplicates removed. The queries must have the same number of columns with compatible types. UNION ALL keeps duplicates.
• INTERSECT: rows present in both results.
• MINUS (also called EXCEPT in some systems): rows in the first result but not in the second.
• Some MySQL versions do not support INTERSECT and MINUS directly; the same result can be obtained with IN or NOT IN subqueries. The concepts are what matter for the exam.
• Cartesian product: SELECT * FROM A, B; pairs every row of A with every row of B. Rows = rows(A) × rows(B); columns = columns(A) + columns(B).
• JOIN: a Cartesian product with a condition that matches related rows, for example: SELECT Name, DName FROM Emp, Dept WHERE Emp.DeptNo = Dept.DeptNo;
• The same can be written with JOIN … ON: SELECT * FROM Emp JOIN Dept ON Emp.DeptNo = Dept.DeptNo;
• Equi-join: a join using the = operator; the common column appears twice.
• Natural join: SELECT * FROM Emp NATURAL JOIN Dept; joins on columns with the same name and shows the common column only once.
• When a column name exists in both tables, qualify it with the table name (Emp.DeptNo) or a table alias.

Exam tips

• For a natural join of tables with degrees 3 and 2 sharing one column, the result has 3 + 2 − 1 = 4 columns.
• A join with n tables needs at least n − 1 join conditions to avoid an unintended Cartesian product.

Common mistakes

• Forgetting the join condition, which produces a full Cartesian product.
• Using UNION on queries with different numbers of columns.