How do clauses like UNION, INTERSECT, and EXCEPT interact with ORDER BY and LIMIT in MySQL?

Q: How do clauses like UNION, INTERSECT, and EXCEPT interact with ORDER BY and LIMIT in MySQL?

In MySQL, **UNION**, **INTERSECT** (simulated using INNER JOIN or EXISTS), and **EXCEPT** (simulated using LEFT JOIN ... IS NULL or NOT IN) are set operation clauses that combine results from multiple queries. Their interaction with **ORDER BY** and **LIMIT** depends on whether these clauses are applied to individual subqueries or the final combined result. **UNION** combines results of two queries and removes duplicates by default. Use **UNION ALL** to retain duplicates. **INTERSECT** can be simulated using INNER JOIN or EXISTS to return rows common to both queries. **EXCEPT** can be simulated using LEFT JOIN ... IS NULL or NOT IN to return rows present in the first query but not in the second. An **ORDER BY** clause applied after the set operation sorts the final combined result set, not the individual subqueries. A **LIMIT** clause applied after the set operation restricts the number of rows returned from the combined result set. Applying ORDER BY or LIMIT inside individual subqueries may require parentheses to preserve logical grouping, but final sorting and limiting is usually done after the set operation for clarity and correctness. Essentially, MySQL treats ORDER BY and LIMIT at the end of a set operation as instructions for the **overall output**, ensuring that the combined results are sorted and limited as intended. SELECT name, salary FROM employees WHERE department_id = 1 UNION SELECT name, salary FROM employees WHERE department_id = 2 ORDER BY salary DESC LIMIT 10; SELECT e1.name, e1.salary FROM employees e1 INNER JOIN employees e2 ON e1.name = e2.name WHERE e1.department_id = 1 AND e2.department_id = 2 ORDER BY e1.salary DESC; SELECT name, salary FROM employees WHERE department_id = 1 AND name NOT IN (SELECT name FROM employees WHERE department_id = 2) LIMIT 5;

Difficulty: 5/10

set operators, order by, limit

Using Set Operation Clauses with ORDER BY and LIMIT in MySQL

In MySQL, UNION, INTERSECT (simulated using INNER JOIN or EXISTS), and EXCEPT (simulated using LEFT JOIN ... IS NULL or NOT IN) are set operation clauses that combine results from multiple queries. Their interaction with ORDER BY and LIMIT depends on whether these clauses are applied to individual subqueries or the final combined result.

Key Points About Set Operations with ORDER BY and LIMIT

1
UNION combines results of two queries and removes duplicates by default. Use to retain duplicates.

How do clauses like UNION, INTERSECT, and EXCEPT interact with ORDER BY and LIMIT in MySQL?

Using Set Operation Clauses with ORDER BY and LIMIT in MySQL

Scenario Questions

Follow-up Questions