SQL JOINs

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;