SQL Joins

SQL joins are a crucial aspect of database querying, allowing users to combine data from multiple tables based on related columns. In this article, we will explore the fundamentals of SQL joins, various types of joins, and examples to help you master this essential skill.

Understanding SQL Joins

A SQL join is a method used to combine rows from two or more tables based on a related column between them. The primary goal is to retrieve a result set that includes columns from both tables, providing a comprehensive view of related data. The columns used for joining tables are typically foreign keys that establish relationships between them.

We'll work with our Customer / Order / Product data model

Types of SQL Joins

Consider the following tables

Customers

Orders

In this case, the customer_id in the Orders table is the foreign key that helps us link the two tables together to associate a Customer to a certain Order

1. INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. It returns only the rows where there is a match in the specified columns.

SELECT customers.Customer_id, customers.first_name, orders.Order_id, orders.item
FROM customers
INNER JOIN orders ON customers.Customer_id = orders.Customer_id;

Resulting query data:

We can see the item each customer had ordered only if there was a link to the order. This means, the customer with ID 5 named Betty was not joined since the INNER JOIN did not match on the customers.Customer_id = orders.Customer_id condition.

2. LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table (the table mentioned before the JOIN keyword), and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table.

-- left join Customers and Orders tables based on their shared customer_id columns
-- Customers is the left table
-- Orders is the right table

SELECT Customers.customer_id, Customers.first_name, Orders.item
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Output

Resulting data:

We can see that every row from the LEFT table (the Customers table) was joined with every order it was associated to. However, Customer's with no orders in the Orders table did not result in any order (ID 5, Betty). Therefore the rest of the row for the Orders columns is kept NULL but still included in the results from the Customer data as part of the LEFT JOIN.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table.

SELECT customers.CustomerID, customers.CustomerName, orders.OrderID
FROM customers
RIGHT JOIN orders ON customers.CustomerID = orders.CustomerID;

4. FULL JOIN (or FULL OUTER JOIN)

The FULL JOIN keyword returns all records when there is a match in either the left (table1) or the right (table2) table records. If there is no match, NULL values are returned for columns from the table without a match.

SELECT customers.CustomerID, customers.CustomerName, orders.OrderID
FROM customers
FULL JOIN orders ON customers.CustomerID = orders.CustomerID;

Conclusion

SQL joins are a powerful feature for combining data from multiple tables, enabling users to create meaningful and comprehensive result sets. Understanding the types of joins and when to use them is essential for effective database querying. Whether you're retrieving employee details, analyzing sales data, or working with any relational database, mastering SQL joins is a key skill for any SQL developer or database administrator.