What the SQL?! JOIN me at UNION Station¶
JOIN and UNION are staples in SQL. In English they're synonyms to each other, but in SQL they behave very differently. They work in different directions.
TL;DR - Use
JOIN to add columns. Use
UNION to add rows.
According to Wikipedia a
An SQL join clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.
And here's their definition for
In SQL the UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite
UNION adds rows
UNder the results.
JOIN has not tip, but it's not a
We're going to use a 2 table database. People belong to a zip code and zip codes have a named region.
CREATE TABLE people AS SELECT * FROM ( VALUES (1, 'Candy', '60001'), (2, 'Mandy', '70001'), (3, 'Randy', '80001'), (4, 'Andy', '89991') ) AS t (id, name, zip_code); CREATE TABLE zip_codes AS SELECT * FROM ( VALUES ('60001', 'North'), ('70001', 'South'), ('80001', 'East'), ('90001', 'West') ) AS t (code, region);
The best time to use a
JOIN is when we need to show a column from another table which correlates to a column from a previously declared table.
The Sales Team would like a list of people's names with their regions.
Create a query with each person correlated to the zip code
SELECT people.id, people.name, people.zip_code, zip_codes.region FROM people JOIN zip_codes ON zip_codes.code = people.zip_code;
List all the people who belong to an unknown zip code?
SELECT people.id, people.name, people.zip_code, zip_codes.region FROM people LEFT JOIN zip_codes ON zip_codes.code = people.zip_code WHERE zip_codes.code IS NULL;
JOINare interpreted identically. The later saves typing 6 characters.
INNER JOINis great for finding correlation, but bad for finding unmatched records.
LEFT JOINtells the query engine we want to return all data on the
LEFTor earlier tables even though the correlated value doesn't exist.
RIGHT JOINis the opposite of
LEFT. They retain rows from tables mentioned later in the query.
RIGHT JOINin the same query should be avoided.
WHEREcondition states we only want the uncorrelated rows, the
UNION clause allows us to conform 2 or more unrelated results into a single uniform results.
The search department wants a list of all keywords for their search index.
Return a list of keywords from both the people and the zip code tables.
SELECT name AS keyword FROM people UNION SELECT region FROM zip_codes;
The search department likes the results, but wants to be able to correlate the row back to its source table and row.
SELECT 'people' AS source, id::varchar AS key, name AS keyword FROM people UNION SELECT 'zip_codes', code, region FROM zip_codes ORDER BY 1, 2, 3;
UNIONclause requires all the columns from its sub queries to be the same number and type. This is why
::varcharcasting was added.
UNIONdefaults to only returning distinct rows between the result sets. If you want all rows regardless of duplicates try
UNIONworks on the final results. When ordering is needed within a sub query use parenthesis. Ex:
UNION (SELECT ... ORDER BY ... LIMIT 1)
Return the people with invalid zip codes without using
EXCEPTis the inverse of a
EXCEPTremoves the results from the source query instead of appending them.
INcan be used as a matching clause in a
SELECT people.* FROM people WHERE zip_code IN ( SELECT zip_code FROM people EXCEPT SELECT code FROM zip_codes );
DROP TABLE people; DROP TABLE zip_codes;
UNION are an essential part of a SQL users tool belt. On a day to day basis,
JOIN is used more often than
UNION, so we would recommend understanding it first.
UNION gets it power when we realize our data is more alike than originally intended. Both clauses should be withheld at dinner parties and other social events.