Lab Tutorial 7: Joins

Sample Database:

Assume you have the same sample database as before:

1. books:
   • Columns: book_id, title, author, price, quantity.
2. customers:
   • Columns: customer_id, name, email, phone.
3. orders:
   • Columns: order_id, customer_id, order_date.
4. order_items:
   • Columns: order_item_id, order_id, book_id, quantity, unit_price.

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.