📘SQL JOINs Explained Simply - A Beginner's Guide

Introduction

SQL JOINs are used to combine data from two or more tables into a single result based on a shared column.

They allow you to retrieve related data that is stored separately.
For example, a database might have customer names in one table and purchase details in another.

A Join bridges the tables, giving insights that one table alone may not provide.

In this article, we’ll break down the different types of SQL JOINs and when to use them. Specifically, we’ll cover:

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL OUTER JOIN
5. CROSS JOIN
6. SELF JOIN

---

🔗 INNER JOIN

An INNER JOIN combines records based on a related column and returns only matching rows from both tables.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example; If the customers table and the orders table share a column - the customer_id, an INNER JOIN is used to identify the customers who made orders;

SELECT customers.customer_id, first_name, last_name, order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Result;

customer_id	first_name	last_name	order_id
1	john	doe	1
2	mary	wanjiku	2
3	peter	otieno	3
1	john	doe	4
4	lucy	njeri	5

An INNER JOIN can be used to combine more than two tables.

For example, to find out the customers, books they purchased and the corresponding order IDs;

SELECT orders.order_id, first_name, last_name, title
FROM customers
INNER JOIN orders 
ON customers.customer_id = orders.customer_id
INNER JOIN books b 
ON books.book_id = orders.book_id;

Result;

order_id	first_name	last_name	title
1	john	doe	Learning SQL
2	mary	wanjiku	Data Analytics Basics
3	peter	otieno	Python for Data Science
4	john	doe	Data Analytics Basics
5	lucy	njeri	Advanced SQL Queries

---

⬅️ LEFT JOIN/LEFT OUTER JOIN

The LEFT JOIN returns all records from the left table, and the matching records from the right table. Where no match exists, a NULL value is returned.

In a LEFT JOIN, the table specified in the FROM clause is treated as the left table.

Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

To show all the books and the customers who ordered them, a LEFT JOIN is used;

SELECT title,first_name,order_id
FROM books 
LEFT JOIN orders 
ON books.book_id = orders.book_id
LEFT JOIN customers 
ON orders.customer_id = customers.customer_id;

Result;

title	first_name	order_id
Learning SQL	john	1
Data Analytics Basics	mary	2
Python for Data Science	peter	3
Data Analytics Basics	john	4
Advanced SQL Queries	lucy	5
Machine Learning Intro	NULL	NULL

Notice that the row with Machine Learning Intro has NULL values because it was not ordered.

---

➡️ RIGHT JOIN/RIGHT OUTER JOIN

The RIGHT JOIN works like the LEFT JOIN, but it keeps all the rows from the right table and matching rows from the left table.

Syntax;

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

To show every book in the library, whether it was ordered or not;

SELECT books.book_id, title, order_id
FROM orders
RIGHT JOIN books
ON books.book_id = orders.book_id;

Result;

book_id	title	order_id
1	Learning SQL	1
2	Data Analytics Basics	2
3	Python for Data Science	3
2	Data Analytics Basics	4
4	Advanced SQL Queries	5
5	Machine Learning Intro	NULL

⚠️ Note: For both LEFT and RIGHT outer joins, the order in which you write your tables determines which one returns all of its values. You need to be keen about which table you put on the left and which goes on the right to get your intended result.

---

🔄 FULL JOIN/FULL OUTER JOIN

A FULL JOIN returns all rows from both tables.
As is the case in both the right and Left outer joins, for the rows that do not have a match, NULL is returned.

Syntax;

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

To retrieve all the customers and all the books, whether ordered or not, a FULL JOIN will be used;

SELECT order_id, first_name, last_name, title
FROM orders 
FULL JOIN customers  
ON customers.customer_id = orders.customer_id 
FULL JOIN books
ON orders.book_id = books.book_id;

Result;

order_id	first_name	last_name	title
1	john	doe	Learning SQL
2	peter	otieno	Python for Data Science
3	lucy	njeri	Advanced SQL Queries
2	mary	wanjiku	Data Analytics Basics
NULL	david	kimani	NULL
NULL	NULL	NULL	Machine Learning Intro

To retrieve only the matched or un-matched records, you can filter with the WHERE clause.

---

❌ CROSS JOIN

A CROSS JOIN returns the combination of each row from one table with each row from another table.

Syntax;

SELECT *
FROM table1
CROSS JOIN table2;

A CROSS JOIN does not have an ON clause, because we are not looking for matching rows.

If you have 2 tables; t-shirts color and t-shirt size tables, and wanted to find all possible combinations, a CROSS JOIN is used.

SELECT 
  tshirt_colors.color_name, 
  tshirt_sizes.size_name
FROM tshirt_colors
CROSS JOIN tshirt_sizes;

Result;

color_name	size_name
Black	Small
Black	Medium
Black	Large
Black	Extra Large
White	Small
White	Medium
White	Large
White	Extra Large
Navy	Small
Navy	Medium
Navy	Large
Navy	Extra Large

---

🔁 SELF JOIN

A SELF JOIN is used when comparing rows within the same table.

When using self joins, it is essential to use Aliases, to distinguish the two "roles" of the same table for exact execution.
AS is used to introduce an alias

Syntax;

SELECT column name (s) as alias
FROM employees A
JOIN employees B
ON A.column = B.column;

To best illustrate self joins, we will use the employees table below;

employee_id	name	department_id	manager_id	salary
1	Alice	1	NULL	50000
2	Bob	2	1	45000
3	Charlie	1	1	47000
4	Diana	3	NULL	60000
5	Eve	NULL	NULL	40000
6	Brian	2	4	50000
7	Joy	3	4	35000
8	Luke	5	1	45000

To show employees and their managers, we use a SELF JOIN as follows;

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m 
ON e.manager_id = m.employee_id;

Result;

employee	manager
Bob	Alice
Charlie	Alice
Brian	Diana
Joy	Diana
Luke	Alice

---

I hope this guide made joins less intimidating!
If you found this beginner's guide helpful, save it for later or share it with a friend who's just starting their SQL journey! Happy learning!!

Are there any joins I left out? Do you have any join tricks you want to share?
Let me know in the comment section below, I’d love to hear from you!