JOINs merge related data from multiple tables together in to result set.
The two most common types of joins are:
INNER JOIN
OUTER JOIN
INNER JOINs
INNER JOINs return rows that match from both tables.
SELECT <columns> FROM <table 1>
INNER JOIN <table 2> ON <table 1>.<column> = <table 2>.<column>;
SELECT <columns> FROM <table 1> AS <table 1 alias>
INNER JOIN <table 2> AS <table 2 alias> ON <table 1 alias>.<column> = <table 2 alias>.<column>;
Examples:
SELECT product_name, category FROM products
INNER JOIN product_categories ON products.category_id = product_categories.id;
SELECT products.product_name, product_categories.category FROM products
INNER JOIN product_categories ON products.category_id = product_categories.id;
SELECT p.product_name, c.category FROM products AS p
INNER JOIN product_categories AS c ON p.category_id = c.id;
INNER JOINing multiple tables:
SELECT <columns> FROM <table 1>
INNER JOIN <table 2> ON <table 1>.<column> = <table 2>.<column>
INNER JOIN <table 3> ON <table 1>.<column> = <table 3>.<column>;
Examples:
SELECT users.full_name, sales.amount, products.name FROM sales
INNER JOIN users ON sales.user_id = users.id
INNER JOIN products ON sales.product_id = products.id;
OUTER JOINs
There are 3 types of OUTER JOINs:
LEFT OUTER JOIN - JOINs all matching data and all non-matching rows from the left table in the query
RIGHT OUTER JOIN - JOINs all matching data and all non-matching rows from the right table in the query
FULL OUTER JOIN - JOINs all matching data and then all non-matching rows from both tables.
SELECT <columns> FROM <left table>
LEFT OUTER JOIN <right right> ON <left table>.<column> = <right table>.<column>;
SELECT <columns> FROM <left table> AS <left alias>
LEFT OUTER JOIN <right table> AS <right alias>
ON <left alias>.<column> = <right alias>.<column>;
Example
If you wanted to get the product count for every category, even categories without products, an OUTER JOIN is the best solution. The following two examples will yield the same results, however one is an
SELECT categories.name, COUNT(products.id) AS "Product Count" FROM categories
LEFT OUTER JOIN products ON categories.id = products.category_id;
SELECT categories.name, COUNT(products.id) AS "Products Count" FROM products
RIGHT OUTER JOIN categories ON categories.id = products.category_id;