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 |