question

SQL: Joins

A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

The most common type of join is a ‘natural join’. Natural joins return all rows from multiple tables where the join condition is met. For example:

SELECT inventory.product_no, inventory.product_name, supplier.supplier_no, supplier.name

FROM inventory, supplier

WHERE inventory.supplier_no = supplier.supplier_no;

This SQL statement returns all rows from the inventory and supplier tables where there is a matching Supplier_No value in both tables.

Inventory Table:

Product_No	Product_Name	Model	Quantity	Cost	Retail	Supplier_No
1001	Window Lock	12556	20	$1.88	$2.5	2
1002	Trim Replacement	598999	5	$18.88	$22.95	3

Supplier Table:

Supplier_No	Name	Address	City	State	Zip	Contact_No
1	ABC Supplier	1668 Ashton	Lee’s Summit	MO	64081	816-222-2565
2	Jameson Parts	1422 Jameson Ave.	Lee’s Summit	MO	64062	816-525-6620
3	Hancocks	112 Allendale	Ashton	MO	64565	816-555-5689

The result of the SQL statement above is as follows:

Product_No	Product_Name	Supplier_No	Name
1001	Window Lock	2	Jameson Parts
1002	Trim Replacement	3	Hancocks

Why is Supplier No. 1 excluded from the results? Because a natural join ONLY returns rows where the joined fields from both tables are equal (have values that are equal). In this case, products have not been purchased (put into inventory) from Supplier No. 1.

When a join returns unmatched rows as well as matched rows, it is called an ‘outer join’. The outer join syntax uses the terms, ‘left’ and ‘right’. The names are associated with the order of the table names in the FROM clause of the SELECT statement.

Say you want to produce a list of all product numbers, product names and their supplier names and numbers, regardless of whether or not you have purchased products from the supplier. You could use the SQL statement above and add a RIGHT JOIN, as follows:

SELECT inventory.product_no, inventory.product_name, supplier.supplier_no, supplier.name

FROM inventory RIGHT JOIN supplier

ON (inventory.supplier_no = supplier.supplier_no);

The results from the SQL statement above are:

Product_No	Product_Name	Supplier_No	Name
-	-	1	ABC Supplier
1001	Window Lock	2	Jameson Parts
1002	Trim	3	Hancocks

The RIGHT JOIN above will produce results that include all rows (even if there isn’t a match) from the RIGHT table (table listed to the right of the RIGHT JOIN in SQL statement, i.e. supplier) and matching rows from the table listed to the left of the RIGHT JOIN in SQL statement, i.e. inventory.

When using a RIGHT JOIN, the ON clause is used to identify the matching field names.

So you may be thinking…what if I changed the SQL statement above to a LEFT JOIN.

Here is your answer:

Product_No	Product_Name	Supplier_No	Name
1001	Window Lock	2	Jameson Parts
1002	Trim Replacement	3	Hancocks

You may be thinking…’this is the same set of results from the natural join example’…you are correct! In this case, The LEFT JOIN shown below will produce results that include all rows from the LEFT table (table listed to the left of the LEFT JOIN in SQL statement, i.e. inventory) and matching rows from the table listed to the right of the LEFT JOIN in SQL statement, i.e. supplier)

SELECT inventory.product_no, inventory.product_name, supplier.supplier_no, supplier.name

FROM inventory LEFT JOIN supplier

ON (inventory.supplier_no = supplier.supplier_no);

There may be instances where you want to list your results in alphabetical order by supplier ‘Name’. In order to do so, you would use the ORDER BY clause. For example,

SELECT inventory.product_no, inventory.product_name, supplier.supplier_no, supplier.name

FROM inventory JOIN supplier

ON (inventory.supplier_no = supplier.supplier_no)

ORDER BY supplier.name;

The results are as follows:

Product_No	Product_Name	Supplier_No	Name
1002	Trim Replacement	3	Hancocks
1001	Window Lock	2	Jameson Parts

In cases where you want to change your results, based on specific criteria, you would use the WHERE clause. For example, you may only want to see information for a particular product number. You could use the following select statement:

SELECT inventory.product_no, inventory.product_name, supplier.supplier_no, supplier.name

FROM inventory

RIGHT JOIN supplier

ON (inventory.supplier_no = supplier.supplier_no)

WHERE product_no = 1001;

The results are as follows:

Product_No	Product_Name	Supplier_No	Name
1001	Window Lock	2	Jameson Parts