Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server query

Posted on 2015-02-20
8
Medium Priority
?
98 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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 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 34

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 41

Accepted Solution

by:
Kyle Abrahams earned 1600 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 34

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
 
LVL 34

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 34

Author Comment

by:Mike Eghtebas
ID: 40622688
0
 
LVL 49

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 34

Author Comment

by:Mike Eghtebas
ID: 40625027
Thank you.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

810 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