JOINS and UNION

JOINS

• Visual example of SQL JOINS
• SQL JOIN Examples
• Wikipedia page on SQL JOINS

INNER JOIN

SELECT * FROM TableA INNER JOIN TableB
ON TableA.col_match = TableB.col_match

When only JOIN instead of INNER JOIN is specified, SQL performs an inner join.

The columns we join on will be duplicated. We can specify that we only want one of the two:

SELECT col1, TableA.col2, col3 FROM TableA INNER JOIN TableB
ON TableA.col2 = TableB.col2

FULL OUTER JOIN

SELECT * FROM TableA FULL OUTER JOIN TableB
ON TableA.col_match = TableB.col_match

FULL OUTER JOIN with WHERE

SELECT * FROM TableA FULL OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
WHERE TableA.id IS null OR TableB.id IS null

• Can use NULL or null.

LEFT OUTER JOIN

SELECT * FROM TableA LEFT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match

• Can use LEFT OUTER JOIN or simply LEFT JOIN.

RIGHT OUTER JOIN

SELECT * FROM TableA RIGHT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match

UNION

• Like concatenation in pandas.

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