Solved

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

Posted on 2015-02-20
7
92 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

863 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

20 Experts available now in Live!

Get 1:1 Help Now