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
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).

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

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
SELECT name FROM male_fict_chars

The result would be this single column table:

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 >

Interesting Pages