SQL Joins Cheat Sheet

Definitions used throughout this cheat sheet

• Primary key: A primary key is a field in a table that uniquely identifies each record in the table. In relational databases, primary keys can be used as fields to join tables on.
• Foreign key: A foreign key is a field in a table which references the primary key of another table. In a relational database, one way to join two tables is by connecting the foreign key from one table to the primary key of another.
• One-to-one relationship: Database relationships describe the relationships between records in different tables. When a one-to-one relationship exists between two tables, a given record in one table is uniquely related to exactly one record in the other table.
• One-to-many relationship: In a one-to-many relationship, a record in one table can be related to one or more records in a second table. However, a given record in the second table will only be related to one record in the first table.
• Many-to-many relationship: In a many-to-many relationship, records in a given table ‘A’ can be related to one or more records in another table ‘B’, and records in table B can also be related to many records in table A.

Sample Data Used 

SQL Joins Cheat Sheet

INNER JOINS

An inner join between two tables will return only records where a joining field, such as a key, finds a match in both tables.

INNER JOIN join ON one field

SELECT *
FROM ARTIST AS ART
INNER JOIN ALBUM AS ALB
ON ART.ARTIST_ID = ALB.ARTIST_ID;

INNER JOIN with USING

SELECT *
FROM ARTIST AS ART
INNER JOIN ALBUM AS ALB
USING (ARTIST_ID);

SELF JOIN

Self-joins are used to compare values in a table to other values of the same table by joining different parts of a table together.

SELECT
  alb1.artist_id,
  alb1.title AS alb1_title,
  alb2.title AS alb2_title
FROM album as alb1
INNER JOIN album as alb2
ON alb1.artist_id = alb2.album_id;

LEFT JOIN

A left join keeps all of the original records in the left table and returns missing values for any columns from the right table where the joining field did not find a match.

LEFT JOIN on one field

SELECT *
FROM ARTIST AS ART
LEFT JOIN ALBUM AS ALB
ON ART.ARTIST_ID = ALB.ALBUM_ID;

RIGHT JOIN

A right join keeps all of the original records in the right table and returns missing values for any columns from the left table where the joining field did not find a match. Right joins are far less common than left joins, because right joins can always be re-written as left joins.

RIGHT JOIN on one field

SELECT*
FROM ARTIST AS ART
RIGHT JOIN ALBUM AS ALB
ON ART.ARTIST_ID = ALB.ALBUM_ID;

FULL JOIN

A full join combines a left join and right join. A full join will return all records from a table, irrespective of whether there is a match on the joining field in the other table, returning null values accordingly.

FULL JOIN on one field

SELECT *
FROM ARTIST AS ART
FULL OUTER JOIN ALBUM AS ALB
ON ART.ARTIST_ID = ALB.ALBUM_ID;

CROSS JOIN

CROSS JOIN creates all possible combinations of two tables. CROSS JOIN does not require a field to join ON.

SELECT NAME, TITLE
FROM ARTIST
CROSS JOIN ALBUM;

UNION

The UNION operator is used to vertically combine the results of two SELECT statements. For UNION to work without errors, all SELECT statements must have the same number of columns and corresponding columns must have the same data type. UNION does not return duplicates.

SELECT ARTIST_ID
FROM ARTIST
UNION
SELECT ARTIST_ID
FROM ALBUM;

UNION ALL

The UNION ALL operator works just like UNION, but it returns duplicate values. The same restrictions of UNION hold true for UNION ALL

SELECT ARTIST_ID
FROM ARTIST
UNION ALL
SELECT ARTIST_ID
FROM ALBUM;

INTERSECT

The INTERSECT operator returns only identical rows from two tables.

SELECT ARTIST_ID
FROM ARTIST
INTERSECT
SELECT ARTIST_ID
FROM ALBUM;

EXCEPT

The EXCEPT operator returns only those rows from the left table that are not present in the right table.

SELECT ARTIST_ID
FROM ARTIST
EXCEPT
SELECT ARTIST_ID
FROM ALBUM;

SEMI JOIN

A semi join chooses records in the first table where a condition is met in the second table. A semi join makes use of a WHERE clause to use the second table as a filter for the first. 

SELECT *
FROM ALBUM
WHERE ARTIST_ID IN
(SELECT ARTIST_ID
FROM ARTIST);

ANTI JOIN

The anti join chooses records in the first table where a condition is NOT met in the second table. It makes use of a WHERE clause to use exclude values from the second table.

SELECT *
FROM ALBUM
WHERE ARTIST_ID NOT IN
(SELECT ARTIST_ID
FROM ARTIST);