Understanding SQL Joins

If you're new to SQL, understanding how to combine data from different tables is a crucial skill. SQL joins allow you to retrieve related data spread across multiple tables in a single query.

This article will explain SQL joins in simple terms, making it easy for you to grasp and apply them.

What is a SQL Join?

• A join in SQL is used to combine rows from two or more tables based on a related column between them.

• Think of it as linking pieces of a puzzle to see the complete picture.

Example Tables:

1. Customers

   | CustomerID | Name | Country | | --- | --- | --- | | 1 | Alice | USA | | 2 | Bob | Canada | | 3 | Charlie | UK |

2. Orders

   | OrderID | CustomerID | Product | | --- | --- | --- | | 101 | 1 | Laptop | | 102 | 2 | Smartphone | | 103 | 1 | Tablet |

Using joins, you can combine these tables to see which customer made which order.

Types of SQL Joins

There are several types of joins in SQL.

Let's go through each one with simple explanations and examples.

1. INNER JOIN

• An INNER JOIN returns only the rows where there is a match in both tables.

Example:

To find all customers who have made orders:

SELECT Customers.Name, Orders.Product
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

Explanation: Charlie isn't listed because he hasn't made any orders.

Imagine a Venn diagram where only the overlapping part of Customers and Orders is highlighted.

2. LEFT JOIN (LEFT OUTER JOIN)

• A LEFT JOIN returns all rows from the left table (Customers) and the matched rows from the right table (Orders).

• If there's no match, the result is NULL for the right table.

  

Example:

To list all customers and their orders, including those who haven't made any orders:

SELECT Customers.Name, Orders.Product
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

Explanation: Charlie has no orders, so the Product column shows NULL.

Imagine a Venn diagram where all Customers are shown, with Orders only where they overlap.

3. RIGHT JOIN (RIGHT OUTER JOIN)

• A RIGHT JOIN returns all rows from the right table (Orders) and the matched rows from the left table (Customers).

• If there's no match, the result is NULL for the left table.

Example:

To list all orders and their corresponding customers, including orders without matching customers:

SELECT Customers.Name, Orders.Product
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

Explanation: In this example, all orders have matching customers. If there were an order without a customer, the Name would show NULL.

Imagine a Venn diagram where all Orders are shown, with Customers only where they overlap.

4. FULL JOIN (FULL OUTER JOIN)

• A FULL JOIN returns all rows when there is a match in either left (Customers) or right (Orders) table.

• If there's no match, the result is NULL on the side that doesn't have a match.

Example:

To list all customers and all orders, matching them where possible:

SELECT Customers.Name, Orders.Product
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

Explanation: This includes all customers and all orders. Charlie appears with NULL for Product since he has no orders. If there were orders without customers, they'd appear with NULL for Name.

Imagine a Venn diagram showing all Customers and all Orders, highlighting both overlaps and non-overlapping parts.

5. CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables, meaning it combines each row of the first table with every row of the second table.

Example:

To list all possible combinations of customers and products:

SELECT Customers.Name, Orders.Product
FROM Customers
CROSS JOIN Orders;

Result:

Explanation: Every customer is paired with every product, regardless of actual orders.

Imagine a grid where every Customer is listed alongside every Order.

When to Use Each Join

• INNER JOIN: Use when you need only the records that have matches in both tables.

• LEFT JOIN: Use when you need all records from the left table, and matching records from the right table.

• RIGHT JOIN: Use when you need all records from the right table, and matching records from the left table.

• FULL JOIN: Use when you need all records from both tables, whether they have matches or not.

• CROSS JOIN: Use when you need every combination of the two tables' records.

Practical Example

Let's look at another example with Employees and Departments.

Tables:

1. Employees

   | EmployeeID | Name | DepartmentID | | --- | --- | --- | | 1 | John | 10 | | 2 | Jane | 20 | | 3 | Dave | NULL | | 4 | Mary | 30 |

2. Departments

   | DepartmentID | DepartmentName | | --- | --- | | 10 | Sales | | 20 | Engineering | | 30 | HR | | 40 | Marketing |

Example 1: INNER JOIN

List employees with their departments:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Explanation: Dave is excluded because his DepartmentID is NULL and doesn't match any department.

Example 2: LEFT JOIN

List all employees and their departments, including those without departments:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Explanation: Dave doesn't belong to any department, so DepartmentName is NULL.

Example 3: RIGHT JOIN

List all departments and their employees, including departments without employees:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Explanation: Marketing has no employees, so Name is NULL.

Example 4: FULL JOIN

List all employees and all departments, matching where possible:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Explanation: This includes all employees and all departments, showing NULL where there's no match.

---

Summary

SQL joins are powerful tools that let you combine data from multiple tables based on related columns. Here's a quick recap of the main types:

• INNER JOIN: Retrieves only the records that have matching entries in both tables.

• LEFT JOIN: Retrieves all records from the left table and the matching records from the right table. Non-matching records from the right table show NULL.

• RIGHT JOIN: Retrieves all records from the right table and the matching records from the left table. Non-matching records from the left table show NULL.

• FULL JOIN: Retrieves all records when there is a match in either the left or right table. Non-matching records show NULL for the missing side.

• CROSS JOIN: Retrieves the Cartesian product of both tables, pairing every row of the first table with every row of the second table.