Solved

SQL Server query

Posted on 2015-02-20
8
88 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 40

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

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

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

21 Experts available now in Live!

Get 1:1 Help Now