One Way to Think About SQL Joins: It's just a WHERE Clause

When I teach relational database, I have several ways that I present the concept of joins.

One way to think about a join is that it is simply a WHERE clause.

Take the following data model from the MS Northwinds reference database. 

I've modified the data so that we can focus on a limited number of records, and see what's going on more clearly.

    SELECT  SupplierID,

            CompanyName

    FROM    Suppliers

gives:

    SupplierID  CompanyName
    ----------- ----------------------------------------
    3           Grandma Kelly's Homestead

    9           PB Knäckebröd AB

and

    SELECT  ProductID,

            SupplierID,

            ProductName

    FROM    Products

gives:

    ProductID   SupplierID  ProductName

    ----------- ----------- --------------------------------

    7           3           Grandma's Boysenberry Spread

    8           3           Uncle Bob's Organic Dried Pears

    9           3           Northwoods Cranberry Sauce

    23          9           Gustaf's Knäckebröd

    24          9           Tunnbröd

Given those tables and data, consider this statement:

SELECT   Suppliers.SupplierID,
         Suppliers.CompanyName,
         Products.ProductID,
         Products.SupplierID,
         Products.ProductName
FROM     Suppliers,
         Products
ORDER BY Suppliers.SupplierID,
         Products.ProductID   

Notice that there is no explicit JOIN statement. (So this is a CROSS JOIN) This SELECT statement returns:

SupplierID  CompanyName                 ProductID   SupplierID  ProductName

----------- --------------------------- ----------- ----------- -------------------------------

3           Grandma Kelly's Homestead   7           3           Grandma's Boysenberry Spread

3           Grandma Kelly's Homestead   8           3           Uncle Bob's Organic Dried Pears

3           Grandma Kelly's Homestead   9           3           Northwoods Cranberry Sauce

3           Grandma Kelly's Homestead   23          9           Gustaf's Knäckebröd

3           Grandma Kelly's Homestead   24          9           Tunnbröd

9           PB Knäckebröd AB            7           3           Grandma's Boysenberry Spread

9           PB Knäckebröd AB            8           3           Uncle Bob's Organic Dried Pears

9           PB Knäckebröd AB            9           3           Northwoods Cranberry Sauce

9           PB Knäckebröd AB            23          9           Gustaf's Knäckebröd

9           PB Knäckebröd AB            24          9           Tunnbröd

A CROSS JOIN gives all possible combinations of the records in Suppliers and the records in Products. Think of the database engine as saying "You haven't specified how to match up the records, so I'm assuming any record can match with any other record."

But clearly, there in the resulting combinations, some suppliers are shown next to products that they do not supply, i.e., the supplierID from the supplier record does not match the supplierID from the product record (highlighted in red below):

SupplierID  CompanyName                 ProductID   SupplierID  ProductName

----------- --------------------------- ----------- ----------- -------------------------------

3           Grandma Kelly's Homestead   7           3           Grandma's Boysenberry Spread

3           Grandma Kelly's Homestead   8           3           Uncle Bob's Organic Dried Pears

3           Grandma Kelly's Homestead   9           3           Northwoods Cranberry Sauce

3           Grandma Kelly's Homestead   23          9           Gustaf's Knäckebröd

3           Grandma Kelly's Homestead   24          9           Tunnbröd

9           PB Knäckebröd AB            7           3           Grandma's Boysenberry Spread

9           PB Knäckebröd AB            8           3           Uncle Bob's Organic Dried Pears

9           PB Knäckebröd AB            9           3           Northwoods Cranberry Sauce

9           PB Knäckebröd AB            23          9           Gustaf's Knäckebröd

9           PB Knäckebröd AB            24          9           Tunnbröd

But some suppliers are shown next to products that they do supplier, i.e., the supplied ID from the supplier record does match the supplierID from the product record (highlighted in green below):

SupplierID  CompanyName                 ProductID   SupplierID  ProductName

----------- --------------------------- ----------- ----------- -------------------------------

3           Grandma Kelly's Homestead   7           3           Grandma's Boysenberry Spread

3           Grandma Kelly's Homestead   8           3           Uncle Bob's Organic Dried Pears

3           Grandma Kelly's Homestead   9           3           Northwoods Cranberry Sauce

3           Grandma Kelly's Homestead   23          9           Gustaf's Knäckebröd

3           Grandma Kelly's Homestead   24          9           Tunnbröd

9           PB Knäckebröd AB            7           3           Grandma's Boysenberry Spread

9           PB Knäckebröd AB            8           3           Uncle Bob's Organic Dried Pears

9           PB Knäckebröd AB            9           3           Northwoods Cranberry Sauce

9           PB Knäckebröd AB            23          9           Gustaf's Knäckebröd

9           PB Knäckebröd AB            24          9           Tunnbröd

So, if we were to add a condition in the WHERE clause that enforced that only the records in green above should be shown:

SELECT   Suppliers.SupplierID,

         Suppliers.CompanyName,

         Products.ProductID,

         Products.SupplierID,

         Products.ProductName

FROM     Suppliers,

         Products

WHERE    Suppliers.SupplierID = Products.SupplierID

ORDER BY Suppliers.SupplierID,

         Products.ProductID 

Then we would get the suppliers matched up with the correct products:

SupplierID  CompanyName                 ProductID   SupplierID  ProductName

----------- --------------------------- ----------- ----------- -------------------------------

3           Grandma Kelly's Homestead   7           3           Grandma's Boysenberry Spread

3           Grandma Kelly's Homestead   8           3           Uncle Bob's Organic Dried Pears

3           Grandma Kelly's Homestead   9           3           Northwoods Cranberry Sauce

9           PB Knäckebröd AB            23          9           Gustaf's Knäckebröd

9           PB Knäckebröd AB            24          9           Tunnbröd