OUTER JOINs

SQL: SELECT: Joins and Sets

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

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;