Skip to main content
HomeCheat sheetsSQL

SQL Joins Cheat Sheet

With this SQL Joins cheat sheet, you'll have a handy reference guide to joining data in SQL.
Jul 2022  · 6 min read

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 for Joining Data Course.

Joining Data in SQL.png

Have this cheat sheet at your fingertips

Download PDF

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
  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);
Topics
Related

A Data Science Roadmap for 2024

Do you want to start or grow in the field of data science? This data science roadmap helps you understand and get started in the data science landscape.
Mark Graus's photo

Mark Graus

10 min

Top 5 SQL Server Certifications: A Complete Guide

Unlock SQL Server certification success with our guide on paths, preparation with DataCamp, and the top certifications to enhance your career.
Matt Crabtree's photo

Matt Crabtree

8 min

Mastering SQL ROUND: Precision Handling in Data Analysis

Learn how to use the SQL ROUND function in this tutorial. It covers the implementation of the ROUND function in Oracle, MySQL, PostgreSQL, and SQL Server.
Laiba Siddiqui's photo

Laiba Siddiqui

10 min

Mastering SQL NOT EQUAL Operator: A Beginner's Guide

Unlock the power of SQL NOT EQUAL with our expert guide. Learn to refine data queries with practical examples and optimization tips for better analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL NOT IN Operator: A Comprehensive Guide for Beginners

Master SQL's NOT IN operator with this beginner's guide. Learn to filter data effectively, avoid common pitfalls, and explore efficient alternatives
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL CONTAINS: A Comprehensive Tutorial

Unlock the power of SQL CONTAINS for advanced text searches. Dive into logical operators, proximity searches, and wildcard uses for precise data analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

See MoreSee More