SQL Server query

Just for practice purpose, I want to know how to rewrite the following good query removing ON and adding whatever is necessary to WHERE (remove ON altogether):
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';

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
I also don't recommend it.  But you take out the join entirely and select from both tables:

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

0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Here you go, although I don't recommend this.  Something about ANSI 92 syntax, and not all databases will execute this way..
SELECT S.companyname AS supplier, S.country,
P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
INNER JOIN Production.Products AS P
WHERE S.supplierid = P.supplierid AND S.country = N'Japan';

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I got:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHERE'.

at   WHERE S.supplierid = P.supplierid AND S.country = N'Japan';
0
Improved Protection from Phishing Attacks

WatchGuard DNSWatch reduces malware infections by detecting and blocking malicious DNS requests, improving your ability to protect employees from phishing attacks. Learn more about our newest service included in Total Security Suite today!

 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Jim and Kyle,

I appreciate the advise. But does it effect performance and if so; why and how does it.

I am more comfortable with Inner Join myself but for the study purpose, it is good to know the other option for comparison.

Thanks,

Mike


BTW, Kyle's solutions works

I see that with outer joins there maybe some problem but it looks like with inner joins both methods (ON vs WHERE) are the same.

Here is some text for your review from the book I am reading:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Q/A: 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.

D. Correct: ON determines matching and WHERE determines filtering.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
0
 
PortletPaulfreelancerCommented:
>>"with inner joins both methods (ON vs WHERE) are the same."

They can produce the same resultset, but they are not exactly the same

Always use ANSI join syntax if for no other reasons than "legibility and maintainability"
in simple queries it might make very little difference, but in complex queries combining join logic (matching) with filtering can be exceptionally confusing, and error prone.

I also would like to point out the number one reason the older syntax sucks

This is "valid (old) syntax" but a dreadful query:
SELECT S.companyname AS supplier, S.country,
P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S, Production.Products AS P
WHERE S.country = N'Japan';

Open in new window


Ooops, I forgot the join logic between the 2 tables. The query still runs without error but the results looks weird and it took a long time

That bad query produces a Cartesian product between the tables (the equivalent of a CROSS JOIN)
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.