Solved

SQL Server query

Posted on 2015-02-20
8
87 Views
Last Modified: 2015-02-22
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

0
Comment
Question by:Mike Eghtebas
8 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 40622293
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40622303
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
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 400 total points
ID: 40622317
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40622324
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40622574
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40622688
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40625017
>>"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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40625027
Thank you.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Resolve DNS query failed errors for Exchange
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

16 Experts available now in Live!

Get 1:1 Help Now