Introdution SQL joins are a fundamental feature for combining data from multiple tables based on a related column. Understanding the different types of joins and their applications is essential for working with relational databases effectively. In this article, we’ll explore various SQL join types with clear explanations and practical examples. Sample Tables We’ll use the following Customers and Orders tables for examples: Customers Table: CustomerID

Name

Country



1

Alice

USA



2

Bob

Canada



3

Charlie

UK



4

Diana

Germany Orders Table: OrderID

CustomerID

Product

Quantity



101

1

Laptop

2



102

1

Mouse

5



103

2

Keyboard

3



104

3

Monitor

1



105

5

Smartphone

2 1. INNER JOIN: Combining Matching Data INNER JOIN retrieves rows that have matching values in both tables. It is the most commonly used join type. Example: Combining Customers and Their Orders Query: SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; Result: Name

Product

Quantity



Alice

Laptop

2



Alice

Mouse

5



Bob

Keyboard

3



Charlie

Monitor

1 Explanation: Only customers with orders appear in the result.
Customer Diana and order 105 are excluded because they don’t have matching records in both tables. 2. LEFT JOIN: Including All Records From the Left Table LEFT JOIN returns all rows from the left table (Customers), along with matching rows from the right table (Orders). Rows with no match in the right table will have NULL values. Example: Including Customers Without Orders Query: SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; Result: Name

Product

Quantity



Alice

Laptop

2



Alice

Mouse

5



Bob

Keyboard

3



Charlie

Monitor

1



Diana

NULL

NULL Explanation: All customers are included, even if they don’t have orders.
Diana appears with NULL for Product and Quantity. 3. RIGHT JOIN: Including All Records From the Right Table RIGHT JOIN is the opposite of LEFT JOIN. It includes all rows from the right table (Orders) and matching rows from the left table (Customers). Rows with no match in the left table will have NULL values. Example: Including Orders Without Customers Query: SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; Result: Name

Product

Quantity



Alice

Laptop

2



Alice

Mouse

5



Bob

Keyboard

3



Charlie

Monitor

1



NULL

Smartphone

2 Explanation: All orders are included, even if they don’t have matching customers.
Order 105 appears with NULL for Name because CustomerID = 5 is not in the Customers table. 4. FULL OUTER JOIN: Including All Records from Both Tables FULL OUTER JOIN combines the results of LEFT JOIN and RIGHT JOIN, returning all rows from both tables. Rows with no match will have NULL values for the missing columns. Example: Combining All Customers and Orders Query: SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; Result: Name

Product

Quantity



Alice

Laptop

2



Alice

Mouse

5



Bob

Keyboard

3



Charlie

Monitor

1



Diana

NULL

NULL



NULL

Smartphone

2 Explanation: All customers and orders are included.
Diana (no orders) and order 105 (no matching customer) appear with NULL values. 5. CROSS JOIN: Cartesian Product CROSS JOIN returns the Cartesian product of two tables, pairing every row from the left table with every row from the right table. Example: Pairing Customers with Products Query: SELECT Customers.Name, Orders.Product
FROM Customers
CROSS JOIN Orders; Result: Name

Product



Alice

Laptop



Alice

Mouse



Alice

Keyboard



Alice

Monitor



Alice

Smartphone



Bob

Laptop



Bob

Mouse



...

... Explanation: Every customer is paired with every product, resulting in 20 rows (4 Customers x 5 Orders). 6. SELF JOIN: Joining a Table with Itself SELF JOIN is used to compare rows within the same table. It is useful for hierarchical or relationship data. Example: Employee-Manager Relationship Assume we have an Employees table: EmployeeID

Name

ManagerID



1

Alice

3



2

Bob

3



3

Charlie

NULL



4

Diana

1 Query: Find employees and their managers. SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
LEFT JOIN Employees E2
ON E1.ManagerID = E2.EmployeeID; Result: Employee

Manager



Alice

Charlie



Bob

Charlie



Charlie

NULL



Diana

Alice Explanation: The table is joined with itself using ManagerID and EmployeeID to associate employees with their managers. Summary of Joins Join Type

Description

Example Use Case



INNER JOIN

Matches rows in both tables.

Customers with orders.



LEFT JOIN

All rows from the left table, matching rows from the right.

Customers with or without orders.



RIGHT JOIN

All rows from the right table, matching rows from the left.

Orders with or without customers.



FULL OUTER JOIN

All rows from both tables, with NULLs for missing matches.

Complete customer and order data.



CROSS JOIN

Cartesian product of two tables.

Pairing customers with products.



SELF JOIN

Join a table with itself.

Employee-manager relationships. Conclusion Understanding SQL joins is key to working with relational databases. Each join type serves a unique purpose, and mastering them will help you combine and analyze data efficiently. Practice these examples to solidify your understanding! Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn. Happy exploring!👋 Introdution SQL joins are a fundamental feature for combining data from multiple tables based on a related column. Understanding the different types of joins and their applications is essential for working with relational databases effectively. In this article, we’ll explore various SQL join types with clear explanations and practical examples. Sample Tables We’ll use the following Customers and Orders tables for examples: Customers Table: Customers Table: CustomerID

Name

Country



1

Alice

USA



2

Bob

Canada



3

Charlie

UK



4

Diana

Germany CustomerID

Name

Country 1

Alice

USA 2

Bob

Canada 3

Charlie

UK 4

Diana

Germany CustomerID Name Country CustomerID CustomerID Name Name Country Country 1 Alice USA 1 1 Alice Alice USA USA 2 Bob Canada 2 2 Bob Bob Canada Canada 3 Charlie UK 3 3 Charlie Charlie UK UK 4 Diana Germany 4 4 Diana Diana Germany Germany Orders Table: Orders Table: OrderID

CustomerID

Product

Quantity



101

1

Laptop

2



102

1

Mouse

5



103

2

Keyboard

3



104

3

Monitor

1



105

5

Smartphone

2 OrderID

CustomerID

Product

Quantity 101

1

Laptop

2 102

1

Mouse

5 103

2

Keyboard

3 104

3

Monitor

1 105

5

Smartphone

2 OrderID CustomerID Product Quantity OrderID OrderID CustomerID CustomerID Product Product Quantity Quantity 101 1 Laptop 2 101 101 1 1 Laptop Laptop 2 2 102 1 Mouse 5 102 102 1 1 Mouse Mouse 5 5 103 2 Keyboard 3 103 103 2 2 Keyboard Keyboard 3 3 104 3 Monitor 1 104 104 3 3 Monitor Monitor 1 1 105 5 Smartphone 2 105 105 5 5 Smartphone Smartphone 2 2 1. INNER JOIN: Combining Matching Data INNER JOIN retrieves rows that have matching values in both tables. It is the most commonly used join type. Example: Combining Customers and Their Orders Example: Combining Customers and Their Orders Query : Query SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; Result : Result Name

Product

Quantity



Alice

Laptop

2



Alice

Mouse

5



Bob

Keyboard

3



Charlie

Monitor

1 Name

Product

Quantity Alice

Laptop

2 Alice

Mouse

5 Bob

Keyboard

3 Charlie

Monitor

1 Name Product Quantity Name Name Product Product Quantity Quantity Alice Laptop 2 Alice Alice Laptop Laptop 2 2 Alice Mouse 5 Alice Alice Mouse Mouse 5 5 Bob Keyboard 3 Bob Bob Keyboard Keyboard 3 3 Charlie Monitor 1 Charlie Charlie Monitor Monitor 1 1 Explanation : Explanation Only customers with orders appear in the result. Customer Diana and order 105 are excluded because they don’t have matching records in both tables. Only customers with orders appear in the result. Customer Diana and order 105 are excluded because they don’t have matching records in both tables. 2. LEFT JOIN: Including All Records From the Left Table LEFT JOIN returns all rows from the left table (Customers), along with matching rows from the right table (Orders). Rows with no match in the right table will have NULL values. Example: Including Customers Without Orders Example: Including Customers Without Orders Query : Query SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; Result : Result Name

Product

Quantity



Alice

Laptop

2



Alice

Mouse

5



Bob

Keyboard

3



Charlie

Monitor

1



Diana

NULL

NULL Name

Product

Quantity Alice

Laptop

2 Alice

Mouse

5 Bob

Keyboard

3 Charlie

Monitor

1 Diana

NULL

NULL Name Product Quantity Name Name Product Product Quantity Quantity Alice Laptop 2 Alice Alice Laptop Laptop 2 2 Alice Mouse 5 Alice Alice Mouse Mouse 5 5 Bob Keyboard 3 Bob Bob Keyboard Keyboard 3 3 Charlie Monitor 1 Charlie Charlie Monitor Monitor 1 1 Diana NULL NULL Diana Diana NULL NULL NULL NULL Explanation : Explanation All customers are included, even if they don’t have orders. Diana appears with NULL for Product and Quantity. All customers are included, even if they don’t have orders. Diana appears with NULL for Product and Quantity. 3. RIGHT JOIN: Including All Records From the Right Table RIGHT JOIN is the opposite of LEFT JOIN. It includes all rows from the right table (Orders) and matching rows from the left table (Customers). Rows with no match in the left table will have NULL values. Example: Including Orders Without Customers Example: Including Orders Without Customers Query : Query SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; Result : Result Name

Product

Quantity



Alice

Laptop

2



Alice

Mouse

5



Bob

Keyboard

3



Charlie

Monitor

1



NULL

Smartphone

2 Name

Product

Quantity Alice

Laptop

2 Alice

Mouse

5 Bob

Keyboard

3 Charlie

Monitor

1 NULL

Smartphone

2 Name Product Quantity Name Name Product Product Quantity Quantity Alice Laptop 2 Alice Alice Laptop Laptop 2 2 Alice Mouse 5 Alice Alice Mouse Mouse 5 5 Bob Keyboard 3 Bob Bob Keyboard Keyboard 3 3 Charlie Monitor 1 Charlie Charlie Monitor Monitor 1 1 NULL Smartphone 2 NULL NULL Smartphone Smartphone 2 2 Explanation : Explanation All orders are included, even if they don’t have matching customers. Order 105 appears with NULL for Name because CustomerID = 5 is not in the Customers table. All orders are included, even if they don’t have matching customers. Order 105 appears with NULL for Name because CustomerID = 5 is not in the Customers table. 4. FULL OUTER JOIN: Including All Records from Both Tables FULL OUTER JOIN combines the results of LEFT JOIN and RIGHT JOIN, returning all rows from both tables. Rows with no match will have NULL values for the missing columns. Example: Combining All Customers and Orders Example: Combining All Customers and Orders Query: Query: SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID; Result : Result Name

Product

Quantity



Alice

Laptop

2



Alice

Mouse

5



Bob

Keyboard

3



Charlie

Monitor

1



Diana

NULL

NULL



NULL

Smartphone

2 Name

Product

Quantity Alice

Laptop

2 Alice

Mouse

5 Bob

Keyboard

3 Charlie

Monitor

1 Diana

NULL

NULL NULL

Smartphone

2 Name Product Quantity Name Name Product Product Quantity Quantity Alice Laptop 2 Alice Alice Laptop Laptop 2 2 Alice Mouse 5 Alice Alice Mouse Mouse 5 5 Bob Keyboard 3 Bob Bob Keyboard Keyboard 3 3 Charlie Monitor 1 Charlie Charlie Monitor Monitor 1 1 Diana NULL NULL Diana Diana NULL NULL NULL NULL NULL Smartphone 2 NULL NULL Smartphone Smartphone 2 2 Explanation : Explanation All customers and orders are included. Diana (no orders) and order 105 (no matching customer) appear with NULL values. All customers and orders are included. Diana (no orders) and order 105 (no matching customer) appear with NULL values. 5. CROSS JOIN: Cartesian Product CROSS JOIN returns the Cartesian product of two tables, pairing every row from the left table with every row from the right table. Example: Pairing Customers with Products Example: Pairing Customers with Products Query : Query SELECT Customers.Name, Orders.Product
FROM Customers
CROSS JOIN Orders; SELECT Customers.Name, Orders.Product
FROM Customers
CROSS JOIN Orders; Result : Result Name

Product



Alice

Laptop



Alice

Mouse



Alice

Keyboard



Alice

Monitor



Alice

Smartphone



Bob

Laptop



Bob

Mouse



...

... Name

Product Alice

Laptop Alice

Mouse Alice

Keyboard Alice

Monitor Alice

Smartphone Bob

Laptop Bob

Mouse ...

... Name Product Name Name Product Product Alice Laptop Alice Alice Laptop Laptop Alice Mouse Alice Alice Mouse Mouse Alice Keyboard Alice Alice Keyboard Keyboard Alice Monitor Alice Alice Monitor Monitor Alice Smartphone Alice Alice Smartphone Smartphone Bob Laptop Bob Bob Laptop Laptop Bob Mouse Bob Bob Mouse Mouse ... ... ... ... ... ... Explanation : Explanation Every customer is paired with every product, resulting in 20 rows (4 Customers x 5 Orders). Every customer is paired with every product, resulting in 20 rows (4 Customers x 5 Orders). 6. SELF JOIN: Joining a Table with Itself SELF JOIN is used to compare rows within the same table. It is useful for hierarchical or relationship data. Example: Employee-Manager Relationship Example: Employee-Manager Relationship Assume we have an Employees table: EmployeeID

Name

ManagerID



1

Alice

3



2

Bob

3



3

Charlie

NULL



4

Diana

1 EmployeeID

Name

ManagerID 1

Alice

3 2

Bob

3 3

Charlie

NULL 4

Diana

1 EmployeeID Name ManagerID EmployeeID EmployeeID Name Name ManagerID ManagerID 1 Alice 3 1 1 Alice Alice 3 3 2 Bob 3 2 2 Bob Bob 3 3 3 Charlie NULL 3 3 Charlie Charlie NULL NULL 4 Diana 1 4 4 Diana Diana 1 1 Query : Find employees and their managers. Query SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
LEFT JOIN Employees E2
ON E1.ManagerID = E2.EmployeeID; SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
LEFT JOIN Employees E2
ON E1.ManagerID = E2.EmployeeID; Result : Result Employee

Manager



Alice

Charlie



Bob

Charlie



Charlie

NULL



Diana

Alice Employee

Manager Alice

Charlie Bob

Charlie Charlie

NULL Diana

Alice Employee Manager Employee Employee Manager Manager Alice Charlie Alice Alice Charlie Charlie Bob Charlie Bob Bob Charlie Charlie Charlie NULL Charlie Charlie NULL NULL Diana Alice Diana Diana Alice Alice Explanation : Explanation The table is joined with itself using ManagerID and EmployeeID to associate employees with their managers. The table is joined with itself using ManagerID and EmployeeID to associate employees with their managers. Summary of Joins Join Type

Description

Example Use Case



INNER JOIN

Matches rows in both tables.

Customers with orders.



LEFT JOIN

All rows from the left table, matching rows from the right.

Customers with or without orders.



RIGHT JOIN

All rows from the right table, matching rows from the left.

Orders with or without customers.



FULL OUTER JOIN

All rows from both tables, with NULLs for missing matches.

Complete customer and order data.



CROSS JOIN

Cartesian product of two tables.

Pairing customers with products.



SELF JOIN

Join a table with itself.

Employee-manager relationships. Join Type

Description

Example Use Case INNER JOIN

Matches rows in both tables.

Customers with orders. LEFT JOIN

All rows from the left table, matching rows from the right.

Customers with or without orders. RIGHT JOIN

All rows from the right table, matching rows from the left.

Orders with or without customers. FULL OUTER JOIN

All rows from both tables, with NULLs for missing matches.

Complete customer and order data. CROSS JOIN

Cartesian product of two tables.

Pairing customers with products. SELF JOIN

Join a table with itself.

Employee-manager relationships. Join Type Description Example Use Case Join Type Join Type Description Description Example Use Case Example Use Case INNER JOIN Matches rows in both tables. Customers with orders. INNER JOIN INNER JOIN Matches rows in both tables. Matches rows in both tables. Customers with orders. Customers with orders. LEFT JOIN All rows from the left table, matching rows from the right. Customers with or without orders. LEFT JOIN LEFT JOIN All rows from the left table, matching rows from the right. All rows from the left table, matching rows from the right. Customers with or without orders. Customers with or without orders. RIGHT JOIN All rows from the right table, matching rows from the left. Orders with or without customers. RIGHT JOIN RIGHT JOIN All rows from the right table, matching rows from the left. All rows from the right table, matching rows from the left. Orders with or without customers. Orders with or without customers. FULL OUTER JOIN All rows from both tables, with NULLs for missing matches. Complete customer and order data. FULL OUTER JOIN FULL OUTER JOIN All rows from both tables, with NULLs for missing matches. All rows from both tables, with NULLs for missing matches. Complete customer and order data. Complete customer and order data. CROSS JOIN Cartesian product of two tables. Pairing customers with products. CROSS JOIN CROSS JOIN Cartesian product of two tables. Cartesian product of two tables. Pairing customers with products. Pairing customers with products. SELF JOIN Join a table with itself. Employee-manager relationships. SELF JOIN SELF JOIN Join a table with itself. Join a table with itself. Employee-manager relationships. Employee-manager relationships. Conclusion Understanding SQL joins is key to working with relational databases. Each join type serves a unique purpose, and mastering them will help you combine and analyze data efficiently. Practice these examples to solidify your understanding! Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn . Happy exploring!👋 LinkedIn

Every

Best Practices for Faster Queries: A SQL Performance Tuning Tutorial

SQL Data Modification Commands With Examples: A Fast and Easy Guide

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

How to Master SQL Joins - Inner, Outer, Cross, and Self-Joins With Examples

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

NotionNext: The All-in-One Platform for Building Notion-Powered Websites

10 Ways to Optimize Your Database

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Principles of Proper Database Benchmarking

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

NotionNext: The All-in-One Platform for Building Notion-Powered Websites

10 Ways to Optimize Your Database

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Principles of Proper Database Benchmarking

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps