Which departments have students or instructors? Two queries, one result:

SELECT dept_name FROM student
UNION
SELECT dept_name FROM instructor;

UNION returns the rows of both queries and removes duplicate rows. It compares whole rows, not one column.

UNION ALL

UNION ALL keeps every row, duplicates included. It is faster, because the database does not search for duplicates. With the full tables above, UNION returns 6 rows and UNION ALL returns 19 (10 students + 9 instructors).

Rules

• Both queries return the same number of columns, with compatible types, in the same order.
• The first query names the result columns.
• One ORDER BY at the end sorts the whole result.

SELECT name, 'student' AS role FROM student WHERE dept_name = 'Math'
UNION ALL
SELECT name, 'instructor' FROM instructor WHERE dept_name = 'Math'
ORDER BY name;

FULL JOIN in MySQL

Chapter 8 said that MySQL has no FULL JOIN. A left join gives the matches and the left rows without a partner. A right join gives the matches and the right rows without a partner. UNION of the two removes the duplicate matches. The result is the full join.