SQL Joins

See Database Tables

INNER JOIN ALL TABLES (5 TABLES)

product_id productName productColor productPrice productQuantity productPage department_id departmentName manager_id managerFirstName managerLastName manufacturer_id manufacturerName manufacturerWebsite
1 AquaBottle Blue 14.99 25 https://example.com/aquabottle 1 Outdoors 1 Alice Johnson 1 HydroWorks https://hydroworks.com 
SELECT p.product_id, p.productName, p.productColor, p.productPrice, p.productQuantity, p.productPage, d.department_id, d.departmentName, d.manager_id, 
        dm.manager_id, dm.managerFirstName, dm.managerLastName, mm.manufacturer_id, m.manufacturer_id, m.manufacturerName, m.manufacturerWebsite
            FROM product p
            JOIN department d
            ON p.department_id = d.department_id
            JOIN departmentManager dm
            ON d.manager_id = dm.manager_id
            JOIN manufacturerManager mm
            ON dm.manager_id = mm.manager_id
            JOIN manufacturer m
            ON mm.manufacturer_id = m.manufacturer_id

LEFT OUTER JOIN (Left: product, Right: department)

product_id productName productColor productPrice productQuantity productPage department_id departmentName manager_id
1 AquaBottle Blue 14.99 25 https://example.com/aquabottle 1 Outdoors 1
2 SolarLamp Yellow 29.50 10 https://example.com/solarlamp
3 EcoBackpack Green 49.95 5 https://example.com/ecobackpack 3 Travel Gear 
SELECT p.product_id, p.productName, p.productColor, p.productPrice, p.productQuantity, p.productPage, d.department_id, d.departmentName, d.manager_id
            FROM product p
            LEFT JOIN department d
            ON p.department_id = d.department_id

RIGHT OUTER JOIN (Left: product, Right: department)

product_id productName productColor productPrice productQuantity productPage department_id departmentName manager_id
1 AquaBottle Blue 14.99 25 https://example.com/aquabottle 1 Outdoors 1
2 Lighting 2
3 EcoBackpack Green 49.95 5 https://example.com/ecobackpack 3 Travel Gear 
SELECT p.product_id, p.productName, p.productColor, p.productPrice, p.productQuantity, p.productPage, d.department_id, d.departmentName, d.manager_id
            FROM product p
            RIGHT JOIN department d
            ON p.department_id = d.department_id

Join Explanation (JOIN vs. LEFT OUTER JOIN vs. RIGHT OUTER JOIN)

A JOIN (INNER JOIN) returns only the rows that match in both tables. If a row in one table doesn't have a corresponding match in the other, it's excluded from the result. This is great for combining related records when incomplete records shouldn't be shown. For example, showing only customers who have placed orders.

A LEFT OUTER JOIN returns all rows from the left table (the table listed first in the query), plus the matching rows from the right table. If there is no match, the left table's row still appears with NULL values in the right table's columns. This is great for showing all records, regardless if they are incomplete or not. For example, showing all products even if they haven't had any sales yet.

A RIGHT OUTER JOIN works the opposite way of the LEFT OUTER JOIN. It returns all rows from the right table (the table listed after the JOIN keyword), plus the matching rows from the left table. If there is no match, the right table's row still appears with NULL values in the left table's columns. This one can be used similarly to the LEFT OUTER JOIN. For example, showing all employees at a company and their corresponding department (department on the left). Even if some employees aren't assigned to a department, all employees will still show.