Solved

"ON" determines matching and "WHERE" determines filtering.  TSQL

Posted on 2015-02-20
7
109 Views
Last Modified: 2015-03-03
I have this question from a book: What is the difference between the ON clause and the WHERE clause?

A. The ON clause uses two-valued logic and the WHERE clause uses three-valued logic.
B. The ON clause uses three-valued logic and the WHERE clause uses two-valued logic.
C. In outer joins, the ON clause determines filtering and the WHERE clause determines matching.
D. In outer joins, the ON clause determines matching and the WHERE clause determines filtering.

The answer is: D: ON determines matching and WHERE determines filtering.

My Question to you: Can you explain/ break it down so I can see what the book talking about?

ON and WHERE examples:
SELECT S.companyname AS supplier, S.country,
P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
INNER JOIN Production.Products AS P
ON S.supplierid = P.supplierid
WHERE S.country = N'Japan';

SELECT S.companyname AS supplier, S.country,
P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S, Production.Products AS P
WHERE S.supplierid = P.supplierid AND S.country = N'Japan';

Open in new window


Thanks.

Related Text from the book is:
Because the Production.Suppliers table is the preserved side of the join, Supplier XYZ is returned even though it has no matching products. As you recall, an inner join did not return this supplier.
It is very important to understand that, with outer joins, the ON and WHERE clauses play very different roles, and therefore, they aren’t interchangeable. The WHERE clause still plays a simple filtering role—namely, it keeps true cases and discards false and unknown cases. In our query, the WHERE clause filters only suppliers from Japan, so suppliers that aren’t from Japan simply don’t show up in the output.
However, the ON clause doesn’t play a simple filtering role; rather, it’s more a matching role. In other words, a row in the preserved side will be returned whether the ON predicate finds a match for it or not. So the ON predicate only determines which rows from the nonpreserved side get matched to rows from the preserved side—not whether to return the rows from the preserved side. In our query, the ON clause matches rows from both sides by comparing
their supplier ID values. Because it’s a matching predicate (as opposed to a filter), the join won’t discard suppliers; instead, it only determines which products get matched to each supplier. But even if a supplier has no matches based on the ON predicate, the supplier is still returned. In other words, ON is not final with respect to the preserved side of the join. WHERE is final. So when in doubt whether to specify the predicate in the ON or WHERE clauses, ask yourself: Is the predicate used to filter or match? Is it supposed to be final or nonfinal?

Open in new window

0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 250 total points
ID: 40622689
Hello,

The join on clause will give you matching value if it is Inner join and will give you matching values and in case of non matching it will return NULL in outer join.

But to filter out the records you have to add that condition to where clause.
You can also do this without where clause and putting that in Join condition and it should be inner join.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40623071
Following the submitted text above, we have:
"Can you guess what happens if you specify both the predicate that compares the supplier IDs from both sides and the one comparing the supplier country to Japan in the ON clause? "

What is your answer (guess) for this?
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 40623158
No need to guess, can tell you exactly what happens.

FROM Production.Suppliers AS S
INNER JOIN Production.Products AS P
ON S.supplierid = P.supplierid


When the inner join operation completes you will have a result set that only returns you those rows that have the same supplierid in both tables.

WHERE S.country = N'Japan';

Then the filter comes into play.

Using the result set returned after the inner join, it then filters the remaining results so that only those where s.country = 'Japan' are left.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 0 total points
ID: 40623274
Thanks for the comments. See the attached image for more on the subject:

MikeUse Of ON and Where
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40623288
This matches what we told you in our replies ... do you have a further question that needs answering ?

(Noting that your question mentioned inner joins so I didn't cover outer - but Vikas touched on that).

Is there still something here that you need more explanation on ?
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40623308
re:> This matches what we told you in our replies .

Your comments was not something new. They all did exist in the text I had included. Because of too much reading, at one point I got mental block and couldn't see it.

I was asking for demo of some sort which was not provided. But at the end I managed to put together one for later use by others.

Thanks,

Mike
0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40641443
This post shows what is the difference when ON, Where clause is used. It also demos WHERE is final but ON is not.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question