Working with Joins in SQL

Learn how to use SQL joins to combine data from multiple tables in your database.

Main Tables: Customers, Orders, and Products

Here are the main tables used for many of the queries below:

Customer ID Name
1 John Doe
2 Jane Smith
Order ID Customer ID Order Total
101 1 $1,200
102 2 $800
Product ID Product Name Price
201 Laptop $1,200
202 Smartphone $800

1. INNER JOIN

The INNER JOIN returns only the rows where there is a match in both tables. It discards rows where no match is found.

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

This query retrieves order IDs and customer names where the order has a matching customer ID in the `customers` table.

Order ID Customer Name
101 John Doe
102 Jane Smith

2. LEFT JOIN (LEFT OUTER JOIN)

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

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

This query retrieves all customer names, and if a customer has placed orders, their order IDs are displayed. If no order exists, NULL is returned.

Customer Name Order ID
John Doe 101
Jane Smith 102

3. RIGHT JOIN (RIGHT OUTER JOIN)

The RIGHT JOIN is the opposite of the LEFT JOIN. It returns all records from the right table (second table) and the matched records from the left table (first table). If no match is found, NULL values are returned for the left table's columns.

SELECT orders.order_id, customers.name
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.customer_id;

This query retrieves all customer names, and if a customer has placed an order, their order ID is displayed. If no order exists, NULL is returned for the order ID.

Order ID Customer Name
101 John Doe
102 Jane Smith
NULL David Johnson

4. FULL JOIN (FULL OUTER JOIN)

The FULL JOIN combines the results of both LEFT JOIN and RIGHT JOIN. It returns all records from both tables, and when there is no match, NULL values are returned for the missing matches.

SELECT customers.name, orders.order_id
FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id;

This query retrieves all customer names and order IDs, whether or not they match. If there’s no match, NULL values are returned for the unmatched side.

Customer Name Order ID
John Doe 101
Jane Smith 102
David Johnson NULL

Conclusion

Joins are an essential part of working with relational databases, allowing you to combine and retrieve data from multiple tables based on related columns. By mastering INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, you can build powerful queries to retrieve meaningful data from your database.