SQL statement ON v WHERE

Question

Please see the tables below:

Customer table:

ID Name 

Order table:

 ID CustomerID 

A customer can place 0.1 or many orders. Please check out 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 a difference between the two queries? When would a developer use one method and not another?

I thought that there would be other questions, such as online, but I did not find the answer and therefore the reason for the question.

Answer 1

In terms of a set of results and query plans, they are likely to get the same results.

In this regard, they are the same.

Speaking of readability, I would use the WHERE version, since the join condition is explicit, and the WHERE explicitly relates to which results to include / exclude.

Answer 2

I will try to explain in my own way:

Consider the tables below.

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 

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 .

 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 that the filter PersonId = 1 OR PersonId Is NULL , when this filter is applied through the WHERE result, is ONE record, when it is applied to `ON ', the result is TWO records.

Answer 3

If you count the result, both will return the same. There is a difference. First request

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

First, it will apply the union to get the records, and on top of it it will apply the filter condition (where).

Second request,

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

Here during the connection you get the desired result. Performance will be good.

Hope this makes sense!

Answer 4

the first case will return results that will satisfy the condition

and

the second case will return a result that will combine under both conditions

Answer 5

The selected answer is incorrect.

The first request gives all customers who have no orders. The second request gives all customers.

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

In the second request, no matter what data you insert, there can be no records, so all customers without orders are present. There is no connection condition in which they can be true (nitpicker corner: if you do not have a customer record with a zero identifier and an order record with Null ID, and you use the declaration "SET ANSI_NULLS OFF"): "Customer.ID = Order.ID And CustomerID IS NULL. "