UNION SQL

Technically a UNION is not a JOIN, but it certainly works a lot like one. While a JOIN meshes two tables together, a UNION meshes the results of two SELECT Statements. Nevertheless, there are several requirements, the rows selected from each table must be of the same type, they must be selected in the same order, and must be of the same number.

The UNION syntax has the look of marrying together two SELECT statements. Indeed, this is the intention. The results will NOT show duplicate data. To show every cell in the columns will require the addition of the word "ALL" after UNION.

SELECT column(s) FROM table1
UNION
SELECT column(s) FROM table2

UNIONs are handy for combining the results of two similar tables. For example, we may have a table of female fictional characters (fem_fict_chars) and one containing only males (male_fict_chars).

fem_idnamenovel
1Scarlet O'HaraGone With the Wind
2Hermione GrangerHarry Potter
3Jane PorterTarzan


male_idnamenovel
1Pierre BezukhovWar and Peace
2Rhett ButlerGone With the Wind
3George SmileyTinker Taylor Soldier Spy

A UNION of the information from these two tables could be effected using the following:

SELECT name FROM fem_fict_chars
UNION
SELECT name FROM male_fict_chars

The result would be this single column table:

name
Scarlet O'Hara
Hermione Granger
Jane Porter
Pierre Bezukhov
Rhett Butler
George Smiley

Had there been a character in both tables, it would only have occurred once in the results. When deciding whether to use a UNION or a JOIN consider that in a UNION the table columns should be substantially similar and the results are a vertical meshing of the two columns, while a JOIN is used as a way to make one table add information into the rows of another table in a more horizontal structure.

< FULL JOIN SQL | Return to Index >


Send notes in disappearing ink!

Interesting Pages