Solved

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

Posted on 2015-02-20
7
85 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
  • 4
  • 2
7 Comments
 
LVL 14

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 33

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 33

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 33

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 33

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now