With this SQL Joins Cheat Sheet, you’ll have a handy reference guide to joining data in SQL.
SQL, also known as Structured Query Language, is a powerful tool to search through large amounts of data and return specific information for analysis. Learning SQL is crucial for anyone aspiring to be a data analyst, data engineer, or data scientist, and helpful in many other fields such as web development or marketing.
In this cheat sheet, you’ll find a handy list of functions covering SQL Joins —all collected from our SQL Tutorials.
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
| Artist Table | |
| artist_id | name |
| 1 | AC/DC |
| 2 | Aerosmith |
| 3 | Alanis Morissette |
| Album table | ||
| album_id | title | artist_id |
| 1 | For those who rock | 1 |
| 2 | Dream on | 2 |
| 3 | Restless and wild | 2 |
| 4 | Let there be rock | 1 |
| 5 | Rumours | 6 |
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
ART1.ARTIST_ID,
ART1.TITLE AS ART1_TITLE,
ART2.TITLE AS ART2_TITLE
FROM ARTIST AS ART1
INNER JOIN ARTIST AS ART2
ON ART1.ARTIST_ID = ART2.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);Download SQL Joins Cheat Sheet
