SQL Joins – A Comprehensive Guide

Deep dive into the world of SQL (Structured Query Language), specifically focusing on a pivotal concept – SQL Joins.

Joins are a crucial tool for working with relational databases, allowing us to combine data from multiple tables to generate meaningful insights.

Lets break down this complex topic into manageable chunks, topped with examples, to ensure you have a clear understanding of SQL Joins by the end of this post.

What is a SQL Join?

SQL Joins are used to combine rows from two or more tables based on a related column between them, often a primary key in one table that corresponds to a foreign key in another.

Types of SQL Joins

There are four main types of SQL Joins

1. INNER JOIN: Returns records that have matching values in both tables.

2. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.

3. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.

4. FULL (OUTER) JOIN: Returns all records when there is a match in either the left or the right table.

Let’s illustrate these concepts using a simple database with two tables, Customers and Orders.

Customers table:

| CustomerID | Name  |
|------------|-------|
| 1          | John  |
| 2          | Peter |
| 3          | Mary  |
| 4          | Sally |

Orders table:

| OrderID | CustomerID | Product |
|---------|------------|---------|
| 1       | 1          | Apples  |
| 2       | 2          | Bananas |
| 3       | 4          | Grapes  |
| 4       | 5          | Oranges |

1. INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

SQL Statement:

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

This returns:

| Name  | Product |
|-------|---------|
| John  | Apples  |
| Peter | Bananas |
| Sally | Grapes  |

Only the customers who have at least one order are shown.

2. LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (Customers), and the matched records from the right table (Orders). The result is NULL from the right side if there is no match.

SQL Statement:

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

This returns:

| Name  | Product |
|-------|---------|
| John  | Apples  |
| Peter | Bananas |
| Mary  | NULL    |
| Sally | Grapes  |

Mary, who has not made any orders, still appears in the results.

3. RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (Orders), and the matched records from the left table (Customers). The result is NULL from the left side when there is no match.

SQL Statement:

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

This returns:

| Name  | Product |
|-------|---------|
| John  | Apples  |
| Peter | Bananas |
| Sally | Grapes  |
| NULL  | Oranges |

The order of Oranges made by an unknown customer (CustomerID 5, not present in Customers table) still appears in the results.

4. FULL JOIN

The FULL JOIN keyword returns all records when there is a match in the left (Customers) or the right (Orders) table. Records that do not have a match in the other table are shown as NULL.

SQL Statement:

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

This returns:

| Name  | Product |
|-------|---------|
| John  | Apples  |
| Peter | Bananas |
| Mary  | NULL    |
| Sally | Grapes  |
| NULL  | Oranges |

Every record from both tables has been included in the results.

Conclusion

SQL Joins are a powerful tool in database manipulation and analysis. Understanding their functionality allows you to combine data in ways that can provide powerful insights. Hopefully, this post has given you a solid understanding of the different types of SQL Joins and how to use them effectively.