SQL Cheat Sheet: Joins

1. INNER JOIN

Returns only matching rows from both tables.

SELECT *
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.id;

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, and matched rows from the right table.

SELECT *
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

3. RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, and matched rows from the left table.

SELECT *
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.id;

4. FULL OUTER JOIN

Returns all rows when there is a match in one of the tables.

SELECT *
FROM customers
FULL OUTER JOIN orders
ON customers.id = orders.customer_id;

5. CROSS JOIN

Returns the Cartesian product of both tables.

SELECT *
FROM products
CROSS JOIN categories;

6. SELF JOIN

Joins a table to itself.

SELECT A.name AS Employee, B.name AS Manager
FROM employees A
JOIN employees B
ON A.manager_id = B.id;

7. USING Clause

Simplified join syntax when columns have the same name.

SELECT *
FROM orders
JOIN customers
USING (customer_id);

8. NATURAL JOIN

Automatically joins on all columns with the same name.

SELECT *
FROM employees
NATURAL JOIN departments;

9. JOIN with WHERE vs ON

-- ON is for join condition
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.id;

-- WHERE filters results after join
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.id
WHERE customers.country = 'Germany';

10. Best Practices

• Always specify JOIN conditions explicitly.
• Use table aliases for readability.
• Index joined columns for better performance.
• Prefer USING or ON over NATURAL JOIN for clarity.