Working with Joins in SQL

Main Tables: Customers, Orders, and Products

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

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.

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.

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.

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.