SQL Joins Cheat Sheet

1. **Quick Reference**

| Join Type | Description |

| --- | --- |

| INNER JOIN | Returns records with matching values in both tables |

| LEFT JOIN | Returns all records from the left table, and matching records from the right table |

| RIGHT JOIN | Returns all records from the right table, and matching records from the left table |

| FULL OUTER JOIN | Returns all records from both tables, with NULL values where no match exists |

2. **Syntax**

The basic syntax for an SQL join is:

SELECT *
FROM `table1`
`join_type` JOIN `table2`
ON `table1`.`column_name` = `table2`.`column_name`;

* join_type is one of the join types listed above (e.g. INNER JOIN, LEFT JOIN, etc.)

* table1 and table2 are the names of the tables to join

* column_name is the name of the column to join on

3. **Common Patterns**

Here are some common join patterns:

* Simple Inner Join:

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

* Left Join with Filtering:

SELECT *
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

* Multiple Joins:

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

* Full Outer Join:

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

4. **Gotchas**

* Missing Join Condition: If you forget to specify the join condition using the ON clause, you will get a Cartesian product of all rows in both tables.

* Incorrect Join Type: Using the wrong join type can give you incorrect results. For example, using an INNER JOIN when you meant to use a LEFT JOIN.

* Null Values: When joining tables, null values can cause unexpected results. Make sure to handle null values correctly in your join conditions.

5. **Related Commands**

* UNION and UNION ALL for combining the results of multiple queries

* SUBQUERY for using a query as a table in a join

* GROUP BY and HAVING for grouping and filtering joined data

* EXISTS and IN for checking if a value exists in a joined table