1. sql
  2. /references
  3. /union

SQL UNION Keyword

Definition

In SQL, the UNION keyword is used to combine the results of two or more SELECT statements into a single result set. The result set returned by the UNION query includes all rows from the first SELECT statement, as well as all rows from any subsequent SELECT statements, with duplicate rows removed.

Example

For example, suppose you have two tables, employees and consultants, and you want to see a combined list of both, you could use the UNION keyword to combine the results of a SELECT statement on the employees table with a SELECT statement on the consultants table, like so:

SELECT name
FROM employees 
UNION SELECT name 
FROM consultants;

This query would return a result set containing all employee and consultant names with any duplicate rows removed.

It is important to note that the SELECT statements used in a UNION query must have the same number of columns and compatible data types. The resulting column names will be the ones used in the first SELECT statement.

It is also worth noting that UNION ALL also exists as an alternative for UNION, but this will return all the rows including duplicate ones.

Best Practices

When using the UNION keyword in SQL, there are a few best practices you should keep in mind to ensure that your query runs efficiently and returns the expected results:

  • Make sure that the number of columns and the data types of the columns in the SELECT statements being combined are the same. If they are not, the query will return an error.

  • Use the UNION operator instead of the UNION ALL operator when you want to eliminate duplicate rows from the result set. The UNION operator automatically removes duplicates, while the UNION ALL operator does not.

  • Always include an ORDER BY clause in your query if you want the results to be ordered in a specific way. Without an ORDER BY clause, the order of the results is not guaranteed.

  • Use table aliases to make your query more readable and to avoid ambiguity when columns have the same name in different tables.

  • If the query is slow, consider breaking it into smaller queries and saving the results to a temporary table to improve performance.