Two Table Joins with WHERE

Two Table Joins with WHERE

The best way to understand joins is to look at an example. Let’s try our ingredients/vendor

list again. Using a join, we derive a new, virtual table that includes the information we need

from both the ingredients and vendors table.

Query 5.1 For each ingredient, find its name and the name and ID of the vendor that supplies it

SELECT vendors.vendorid, name, companyname

FROM ingredients, vendors

WHERE ingredients.vendorid = vendors.vendorid;

Let’s dissect this query. First, the FROM clause creates the new table that has the

combined attributes of all tables in the list. In the previous example, the new table has a

total of 12 attributes (5 from vendors + 7 from ingredients). Note that we now have two

attributes in the new table with the name vendorid: one from the ingredients table and one

from the vendors table. If we reference vendorid in the new table, do we mean the vendorid

from ingredients or vendors? It is impossible to know which, so SQL requires us to prefix

these attributes with their original table names. For example, the vendorid from vendors

is called vendors.vendorid. The table name prefix is not required if the attribute name is

unambiguous, as with name in the SELECT clause.

Next, the WHERE clause describes how to connect the tables. For this query, we want

to match the rows in ingredients with the rows in vendors that supply them. We know that

the vendorid in ingredients references the vendorid in vendors. The foreign key constraint

between the two tables tells us these attributes have an association. We want to match

rows from the two tables where this common attribute contains the same value. To do

this, we add a condition (called the join predicate) to the WHERE clause that describes

this relationship. To combine our two tables, we use WHERE ingredients.vendorid =

vendors.vendorid.

We can think of executing this query by taking every row in ingredients and searching

the vendors table for matching rows. Once a match is found, all of the attributes in both

tables are processed by the rest of the SQL query. This allows us to display the name of the

ingredient and the name and ID of the vendor that supplies it. After executing the FROM

and WHERE clauses, we have a table with the attributes of both ingredients and vendors

and the rows with matched vendor IDs. Finally SQL executes the SELECT clause, keeping

only the attributes in the SELECT list.

Once we create our new table, we can use it just like any other table. We can apply

conditions in the WHERE clause to the matched rows. Query 5.2 uses the same join predicate

as Query 5.1, but it also applies the additional constraint that the company name is

Veggies_R_Us.

■

SELECT name

FROM ingredients, vendors

WHERE ingredients.vendorid = vendors.vendorid AND companyname = 'Veggies_R_Us';

Let’s try an example: Find the orders made at our California store in Query 5.3. In

Step 1, we list all of the tables that we need to answer the query. In this example, we need

the orders table for the order information and the stores table to find out the state of each

store. We list these tables in the FROM clause of our query. For Step 2, we need to give

the criteria for connecting these two tables. We know that the storeid in orders references

the storeid in stores from the foreign key constraint. We want to match rows from the two

tables where this common attribute contains the same value. We add the join predicate

WHERE orders.storeid = stores.storeid. At this point, we have the orders made in all

stores. To finish we add the condition state = 'CA' and select the desired attributes.

Query 5.3 Find the store ID and price of all orders made in our California stores

SELECT stores.storeid, price

FROM orders, stores

WHERE orders.storeid = stores.storeid AND state = 'CA';

Here we join the two tables from Step 1 using the join predicate from Step 2 to derive a

new table. We can then treat this like any other table, applying additional conditions and

selecting specific attributes.

We can even apply aggregation and grouping to a derived table. Suppose you want

to know the total sales in each state. Step 1 tells us we need the stores (to find the states)

and the orders tables. We connect our tables using the storeid column in Step 2. Now that

we have the derived table, we perform aggregation and grouping.

Query 5.4 Find the total sales by state

SELECT state, SUM(price)

FROM stores, orders

WHERE stores.storeid = orders.storeid

GROUP BY state;