Sample Database:
Assume you have the same sample database as before:
books
:- Columns:
book_id
,title
,author
,price
,quantity
.
- Columns:
customers
:- Columns:
customer_id
,name
,email
,phone
.
- Columns:
orders
:- Columns:
order_id
,customer_id
,order_date
.
- Columns:
order_items
:- Columns:
order_item_id
,order_id
,book_id
,quantity
,unit_price
.
- Columns:
Explanation of Joins:
LEFT JOIN:
LEFT JOIN returns all records from the left table (customers in this case) and the matched records from the right table (orders), if any.
SELECT c.name, oorder_id, o.order_date FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;
This query will return all customers along with their orders, if they have placed any orders. If a customer has not placed any orders, the columns from the orders
table will be NULL
.
RIGHT JOIN:RIGHT JOIN
returns all records from the right table (orders
in this case) and the matched records from the left table (customers
), if any.
SELECT c.name, o.order_id, o.order_date FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id;
This query will return all orders along with the corresponding customer information. If an order does not have a corresponding customer, the columns from the customers
table will be NULL
.
FULL JOIN:FULL JOIN
returns all records when there is a match in either left or right table.
SELECT c.name, o.order_id, o.order_date FROM customers c FULL JOIN orders o ON c.customer_id = o.customer_id;
This query will return all customers and orders. If there is no match, the missing side will contain NULL
values.
These explanations should help students understand the differences between LEFT JOIN
, RIGHT JOIN
, and FULL JOIN
and how they can be used to combine data from multiple tables based on related columns.