4

Please have a look at the tables below:

Customer Table:

ID
Name

Order Table:

ID
CustomerID

A customer can place 0,1 or many orders. Please have a look at the SQL query below:

SELECT Customer.* 
FROM Customer LEFT JOIN Order ON Customer.ID=Order.CustomerID 
WHERE CustomerID IS NULL

and

SELECT Customer.* 
FROM Customer LEFT JOIN Order ON Customer.ID=Order.CustomerID AND
      CustomerID IS NULL

Is there any difference between these two queries? When would a developer use one technique rather than the other?

I thought there would be other questions like this online, but I have not found the answer and hence the reason for the question.

w0051977
  • 15,099
  • 32
  • 152
  • 329
  • @Royi Namir, I didn't come across that question earlier when I was searching. The question in your link asks specifically about Oracle. My question is more about SQL Server so I have tagged it. – w0051977 Jan 01 '13 at 11:07
  • ok . you didn't mention it.:-) – Royi Namir Jan 01 '13 at 11:10
  • The proposed [duplicate](http://stackoverflow.com/questions/121631/inner-join-vs-where) is about INNER JOIN; this question is about LEFT OUTER JOIN. The considerations are different, and the two questions are not duplicates. (The Oracle vs SQL Server distinction is substantially immaterial.) – Jonathan Leffler Jan 01 '13 at 12:41
  • 2
    Problems: Are `Customer.ID` and `Order.ID` really the same property? That wrong for any correctly designed database. And what table is `CustomerID` in? It seems much more likely that `Customer.ID = Order.CustomerID` is intended here. – RBarryYoung Jan 01 '13 at 12:49

5 Answers5

2

In terms of result set and query plans the two are likely to produce identical results.

In that respect, they are the same.

If talking about readability, I would use the WHERE version, as the join condition is explicit and the WHERE clause is explicit to what results to include/exclude.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • This is incorrect: The first query gives all customers that have no orders. The second query gives all customers. – user662852 Jan 03 '13 at 22:03
  • @user662852 - What are you talking about? The second query is also constrained with `AND CustomerID IS NULL`. – Oded Jan 03 '13 at 22:41
  • Unless you use "set ansi_nulls off" order.customerid cannot equal any value of customer.id and be null at the same time so the on condition is always false; left join returns all customers with no joined order records. – user662852 Jan 03 '13 at 22:48
1

I will try to explain in my own way:

Consider below tables

tblQuestions:

QuestionId                   1                    2
QuestionTitle                Your Name?           Your Age?

tblPersons:

PersonId                     1                    2
PersonName                   Person1              Person2

tblAnswers:

AnswerId                     1
PersonId                     1
QuestionId                   1
Answer                       My Name is Person1

NOW try this query with WHERE, it will return only one result.

SELECT  q.QuestionId, q.QuestionName, a.Answer
FROM    tblQuestions q
        LEFT OUTER JOIN tblAnswers a ON q.QuestionId = a.QuestionId
WHERE   a.PersonId = 2 OR a.PersonId IS null

Now try this query with ON, it will return two results.

SELECT  q.QuestionId, q.QuestionName, a.Answer 
FROM    tblQuestions q 
        LEFT OUTER JOIN tblAnswers a ON q.QuestionId = a.QuestionId AND
    (a.PersonId = 2 OR a.PersonId IS null)

The difference in the results is because of the the filer PersonId = 1 OR PersonId Is NULL, when this filter is applied through WHERE result is ONE record, when it is applied within `ON' result is TWO records.

Yaqub Ahmad
  • 27,569
  • 23
  • 102
  • 149
0

If you consider the result both will return the same. There is a difference. First Query,

SELECT Customer.* 
FROM Customer LEFT JOIN Order ON Customer.ID=Order.ID 
WHERE CustomerID IS NULL

first will apply the join get the records and on top of it, it will apply the filter (Where) condition.

Second Query,

SELECT Customer.* 
FROM Customer LEFT JOIN Order ON Customer.ID=Order.ID AND
      CustomerID IS NULL

Here during join itself you are getting the desired result. Performance wise this will be good.

Hope this makes some sense!!

Mariappan Subramanian
  • 9,527
  • 8
  • 32
  • 33
  • 2
    It is simplistic to think that the different parts of a query run in any particular order - the query planner may very well not choose to do so as you expect. – Oded Jan 01 '13 at 10:53
  • yeah but this what happens. Obviously when you join two tables it will get the result based on join condition and will apply further join conditions!! – Mariappan Subramanian Jan 01 '13 at 10:56
  • You are contradicting yourself. – Oded Jan 01 '13 at 10:57
  • 1
    It depends on the data and the statistics. The query planner will plan the execution according to the statistics - it is not possible to tell in advance what it will choose. – Oded Jan 01 '13 at 11:00
  • The first query gives all customers that have no orders. The second query gives all customers. – user662852 Jan 03 '13 at 22:04
0

first case will return results which will satisfy where condition

and

second case will return result which will join on both condition

Nipun Jain
  • 626
  • 4
  • 6
0

The selected answer is incorrect.

The first query gives all customers that have no orders. The second query gives all customers.

In the first query, customers with orders join to their orders. Customers without orders are present because of the left join. Then the Where is applied - customers with orders are filtered out because CustomerID is not null; customers without orders remain.

In the second query, no matter what data you put in, there can be no records joined, so all customers with no orders are present. There is no join condition where these can both be true (nitpicker's corner: unless you have a Customer record with null ID, and order record with Null ID, and you use the "SET ANSI_NULLS OFF" declaration) : "Customer.ID=Order.ID AND CustomerID IS NULL".

user662852
  • 380
  • 1
  • 9