SQL Intermediate Cheatsheet — Joins

The Syntax of JOIN

SELECT p.first_name, p.last_name, a.city, a.state
FROM person p
JOIN address a
ON p.person_ID = a.person_ID

INNER JOIN

SELECT i.date, COUNT(DISTINCT i.user_ID) AS num_users
FROM iPhone i
JOIN web w
ON i.user_ID = w.user_ID
AND i.date = w.date

OUTER JOINS

LEFT JOIN

FROM table1 LEFT JOIN table2
SELECT cities.name, users.ID
FROM cities
LEFT JOIN users
ON cities.ID = users.city_ID
WHERE users.ID IS NULL

RIGHT JOIN

FROM table2 RIGHT JOIN table2

FULL OUTER JOIN

UNION

(SELECT date, user1, message_count
FROM FB_messages)
UNION ALL
(SELECT date, user2, message_count
FROM FB_messages)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store