CODEWITHSUNDEEP
mysqlsql
hacaoideas
hacaoideas

Reputation: 115

Please explain differences between 2 SQL statements

I have wrote 2 SQL statements.

SELECT 
customers.CustomerID,
orders.OrderID
FROM customers LEFT JOIN orders ON customers.CustomerID = 
orders.CustomerID AND orders.EmployeeID=4
WHERE orders.OrderID IS NULL;

And 

SELECT 
customers.CustomerID,
orders.OrderID
FROM customers LEFT JOIN orders ON customers.CustomerID = 
orders.CustomerID AND orders.EmployeeID=4 AND orders.OrderID IS NULL;

The 1st one returns 16 entries, which is correct (per the solution). The 2nd one returns 91 entries. I am using Northwind for mysql version. I read from somewhere that placing condition in JOIN statement is equivalent to doing that in WHERE statement. However, here I can see the difference.

Upvotes: 2

Views: 83

Answers (3)

The Impaler
The Impaler

Reputation: 48875

The second query will return more rows. Why? Read on:

Second query

The second query will match orders rows to customers rows using the predicate:

customers.CustomerID = orders.CustomerID AND orders.EmployeeID=4 AND orders.OrderID IS NULL

Unmatched customers rows will always show up in the result.

First query

Now, the first query will match orders rows to customers rows using the predicate:

customers.CustomerID = orders.CustomerID AND orders.EmployeeID=4

Then it will filter out rows where orders.OrderID is null, removing customer rows that do not fullfil this last predicate, therefore producing less rows. Unmatched customers rows may not show up in the result.

Example:

create table customers (
  CustomerId int
);

create table orders (
  OrderId int,
  CustomerId int,
  EmployeeId int
);

insert into customers (CustomerId) values (1), (2), (3);

insert into orders (OrderId, CustomerId, EmployeeId) values
  (1001, 1, 3),
  (1002, 1, 4),
  (1003, 2, 1);

The first query returns:

CustomerId  OrderId      
----------  ----------------
2           <null>       
3           <null>

while the second query returns:

CustomerId  OrderId      
----------  ----------------
1           <null>       
2           <null>       
3           <null>

Upvotes: 1

dijksterhuis
dijksterhuis

Reputation: 1271

This is to do with the order execution on the statements and the fact you’re using a LEFT JOIN.

A LEFT JOIN will keep all the values from the left side intact, combining only values from the right side that match conditions.

A WHERE clause operates on the query as a whole (in this case).

Query 1:

  1. Get all values from left table
  2. Join on any values from the right table that match conditions
  3. Filter the joined output based on the where condition

Query 2:

  1. Get all values from the left table
  2. Join on any values from the right table that match conditions

The only time a WHERE is really analogous to a JOIN is something like a INNER JOIN, which gets only the relevant values from both left and right sides. I think at least, it has been a while since I flexed the SQL muscles.

Edit - count the number of rows (nothing else) in your customers table, it should return 91 as well.

Upvotes: 2

Himanshu
Himanshu

Reputation: 3970

This could be because of the order in which the Sql query runs FROM and JOIN s. The FROM clause, and subsequent JOIN s are first executed then WHERE and lastly SELECT.

In second case all the join conditions ran together but in the first case the where clause made the difference after joining the data and filtering not at the same time but as a whole after joining.

Upvotes: 0

Related Questions