Querying
UNION and Set Operations
UNION and other set operations combine the results of two or more SELECT statements into a single result set.
Overview
UNION and other set operations combine the results of two or more SELECT statements into a single result set. Standard SQL defines UNION, UNION ALL, INTERSECT, and EXCEPT (also known as MINUS in some dialects). These operations work on tables that are union-compatible: they must have the same number of columns, and corresponding columns must have compatible data types. The column names in the result are taken from the first SELECT statement. Set operations treat their inputs as sets or multisets, depending on whether the ALL keyword is used. UNION removes duplicate rows, while UNION ALL retains all rows, including duplicates. INTERSECT returns rows that appear in both inputs, and EXCEPT returns rows from the first input that do not appear in the second. These operations are useful for combining data from multiple sources, finding common or differing rows, and implementing certain types of joins or filters. They are part of the SQL-92 standard and are widely supported, though exact syntax and behavior for INTERSECT and EXCEPT may vary slightly across vendors.
Syntax and usage
The general syntax for a set operation is: SELECT column_list FROM table1 UNION [ALL] SELECT column_list FROM table2. The SELECT statements must have the same number of columns, and the data types of corresponding columns must be compatible. The column names in the result come from the first SELECT. You can combine more than two queries by chaining set operators. Parentheses can be used to control the order of evaluation, though the default is left-to-right. The ALL keyword is supported for UNION, INTERSECT, and EXCEPT in some dialects; for example, UNION ALL keeps duplicates, while INTERSECT ALL and EXCEPT ALL are less commonly supported. In standard SQL, INTERSECT and EXCEPT have higher precedence than UNION and EXCEPT? Actually, standard SQL gives INTERSECT higher precedence than UNION and EXCEPT, which have equal precedence. Use parentheses to avoid ambiguity. ORDER BY can only appear at the end of the entire set operation, not within individual SELECT statements, unless those statements are enclosed in parentheses. The final ORDER BY applies to the combined result.
How it works
Set operations combine rows from two input queries. Conceptually, each input is evaluated independently, then the results are combined. For UNION without ALL, the database removes duplicate rows from the combined result. Duplicate elimination is typically based on all columns in the result. For UNION ALL, all rows from both inputs are concatenated without removing duplicates. INTERSECT returns distinct rows that appear in both inputs; if ALL is specified, it returns rows that appear in both, with duplicates preserved according to the minimum multiplicity in each input. EXCEPT returns distinct rows from the first input that do not appear in the second; with ALL, it returns rows from the first input that do not appear in the second, preserving duplicates based on multiplicity differences. Implementations may use sorting, hashing, or other techniques to perform these operations efficiently. The result set is unordered unless an ORDER BY clause is specified. Set operations are not the same as joins; they combine rows vertically, not horizontally.
Examples
Combine two tables with UNION: SELECT name FROM employees UNION SELECT name FROM contractors; This returns all distinct names from both tables. To keep duplicates, use UNION ALL: SELECT name FROM employees UNION ALL SELECT name FROM contractors; Find names that appear in both tables: SELECT name FROM employees INTERSECT SELECT name FROM contractors; Find names in employees but not in contractors: SELECT name FROM employees EXCEPT SELECT name FROM contractors; You can also use set operations with more than two queries: SELECT name FROM employees UNION SELECT name FROM contractors UNION SELECT name FROM interns; To order the final result: SELECT name FROM employees UNION SELECT name FROM contractors ORDER BY name; Note that ORDER BY applies to the entire union, not to individual SELECTs. If you need to order individual queries, enclose them in parentheses: (SELECT name FROM employees ORDER BY name LIMIT 5) UNION ALL (SELECT name FROM contractors ORDER BY name LIMIT 5); However, not all databases support ORDER BY in parenthesized subqueries within a set operation.
Common pitfalls
A frequent mistake is assuming that UNION and UNION ALL are interchangeable. UNION removes duplicates, which can be expensive and may change the result if duplicates are meaningful. Another pitfall is mismatched column counts or incompatible data types, which cause errors. Column names are taken from the first SELECT, so aliases in subsequent SELECTs are ignored. ORDER BY can only appear once at the end of the entire set operation; placing it inside individual SELECTs without parentheses is invalid in many databases. Parentheses may be required to combine set operations with other clauses like ORDER BY or LIMIT. Some databases have different precedence rules for INTERSECT and EXCEPT; always use parentheses to clarify. NULL handling can be surprising: set operations treat NULLs as equal for duplicate elimination, unlike some other comparisons. Performance can degrade with large inputs, especially when duplicate elimination is required. Finally, not all databases support INTERSECT and EXCEPT, or they may use different names (e.g., MINUS for EXCEPT in Oracle).
Best practices
Use UNION ALL when you know duplicates are not present or when duplicates are acceptable, as it avoids the overhead of duplicate elimination. Ensure that corresponding columns have the same data type or are implicitly convertible; explicit casts can prevent errors. Always specify column aliases in the first SELECT to give meaningful names to the result columns. Use parentheses to group set operations when mixing with other operators or when the order of evaluation matters. Add an ORDER BY clause at the end if the result order is important. Be cautious with INTERSECT and EXCEPT: they may not be supported in all databases, and their behavior with ALL may vary. Test queries on representative data to understand performance characteristics. Consider whether a join or a subquery might be more appropriate for the task. Document the intent of set operations in comments, especially when using UNION ALL for performance reasons. Finally, be aware that set operations can sometimes be rewritten as joins or EXISTS clauses for better performance, but this depends on the database and query.
Frequently asked questions
What is the difference between UNION and UNION ALL? UNION removes duplicate rows from the combined result, while UNION ALL retains all rows, including duplicates. UNION ALL is generally faster because it does not require duplicate elimination. Can I use ORDER BY with UNION? Yes, but only once at the end of the entire set operation. It applies to the final combined result. If you need to order individual SELECT statements, you must enclose them in parentheses, and even then, support varies. How do NULLs behave in set operations? For duplicate elimination, NULLs are considered equal to each other, so two rows with NULL in the same column are treated as duplicates. This differs from the usual NULL comparison semantics in WHERE clauses. What about INTERSECT and EXCEPT? They return common or differing rows, respectively. Not all databases support them, and the ALL variant may not be available. Check your database documentation for exact behavior.