Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-02-20
7
Medium Priority
?
111 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 1000 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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 1000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 23

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

661 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