SQL - Joins in SQL

What Are Joins in SQL?

Joins in SQL are used to combine rows from two or more tables based on a related column (usually a foreign key). This allows you to query and analyze data across multiple tables as if they were one.

Why Use Joins?

• To retrieve data that is spread across multiple tables.

• To enforce and use relationships between tables (like customers and orders).

• To avoid data duplication by normalizing tables.

Types of Joins

1. INNER JOIN

Returns only rows that have matching values in both tables.

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

• Only shows orders where there is a matching customer.

2. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table, and matching rows from the right table. If no match, returns NULL.

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

• Shows all customers, including those without orders.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table, and matching rows from the left table. If no match, returns NULL.

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

• Shows all orders, including those without a customer (rare in well-structured databases).

4. FULL JOIN (or FULL OUTER JOIN)

Returns all rows when there is a match in one of the tables. If no match, fills with NULL.

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

• Combines LEFT and RIGHT JOIN: returns all customers and all orders, matched or not.

5. CROSS JOIN

Returns the Cartesian product of the two tables—every row in the first table is combined with every row in the second.

SELECT a.name, b.product
FROM customers a
CROSS JOIN products b;

• If 3 customers and 4 products exist, you get 12 rows (3 × 4).