Forming the Join ~ WHERE Clause

There are many ways to form a join between tables. The most universal is using the WHERE clause.

$.$guides$/$img$/$displayimage

As you can see from the diagram above, the primary key of one table needs to equal the foreign key of the other table in order for rows to be returned. We keep adding to this list as we include more tables in the join. Also notice how we include any filters the query may need within this statement.

To demonstrate this type of join, we are going to produce a report showing the Customer Last Name, the Order ID and the Order Date for all orders placed in $2020.$ The join for this table, as we already know is PersonID $=$ CustomerID. To run this query we need to load the Auntie B Database.

LOAD AUNTIE B$\text{'}$S DATABASE

Here is the query:

use auntieb;

select lastname

$,$ orderid

$,$ orderdate

from People, Orders

where people.peopleid $=$ orders.customerid

and year$($orderdate$)=\text{'}2020\text{'}$;

TRY IT

Your results should look like the following:

lastname orderid orderdate

$------------------------------------------$

Jackson $10422020-03-28$

Jackson $10432020-03-01$

$(2$ rows affected$)$

The only negative to using this method is how cluttered the WHERE clause can quickly become if we need to add more filters. The more cluttered, the harder it is to debug if there is something wrong with your query. This is why I prefer the next join method.