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_id | name | novel |
|---|---|---|
| 1 | Scarlet O'Hara | Gone With the Wind |
| 2 | Hermione Granger | Harry Potter |
| 3 | Jane Porter | Tarzan |
| male_id | name | novel |
|---|---|---|
| 1 | Pierre Bezukhov | War and Peace |
| 2 | Rhett Butler | Gone With the Wind |
| 3 | George Smiley | Tinker 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 >
