6.10: SQL Union and Union All

Last updated
Save as PDF

Page ID: 23946

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

The Union and Union All operators merge the results of two or more queries that are given as SQL Select statements. With MS Access you must switch to SQL View to use Union/Union All

UNION removes duplicates and sorts the results
UNION ALL returns all values (includes duplicates) without sorting
The output fields must be identical (number and type) for each SELECT.

The syntax for UNION of two Select’s:

Figure \(\PageIndex{1}\): Union and Union All syntax.

Any number of Select statements can be UNIONed. A requirement for using UNION is that the queries are union-compatible – the queries must retrieve the same number of fields, and fields in the same position across the multiple Select clauses must be of matching types.

Example

Consider the Employee table in the Company database. To list all names (first and last) in a single column construct two queries: one to list the first names of employees and one to list the last names of employees.

These two queries can be combined to produce a single list of names. Now, in SQL view type and run:

Exercises

Exercise \(\PageIndex{1}\)

Modify the example above so that duplicates are eliminated.