?
Solved

SQL Server query

Posted on 2015-02-20
8
Medium Priority
?
94 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 40

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Need protection from advanced malware attacks?

Look no further than WatchGuard's Total Security Suite, providing defense in depth against today's most headlining attacks like Petya 2.0 and WannaCry. Keep your organization out of the news with protection from known and unknown threats.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

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