CODEWITHSUNDEEP
sql
PlayPhil1
PlayPhil1

Reputation: 301

Whats the difference between AND and WHERE in the join statement

I've been sitting at W3 schools and scratching my brain, by not understanding why the result changes when i execute some SQL commands.

So this is W3 schools example:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
where LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

The output result is: Davolio 29

So my question is, when i change the code to 

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
and LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

The output result is: Davolio 29 Fuller 196

What is happening here, I have not found a proper explanation for why Fuller of 196 orders comes up when just changing it to AND, why is there a difference?

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your logic in the second case is:

ON (Orders.EmployeeID = Employees.EmployeeID and LastName = 'Davolio') OR
   (LastName = 'Fuller')

You would get the same results with parentheses:

ON Orders.EmployeeID = Employees.EmployeeID and
   (LastName = 'Davolio' OR LastName = 'Fuller')

Or more simply with IN:

ON Orders.EmployeeID = Employees.EmployeeID and
   LastName IN ('Davolio', 'Fuller')

Upvotes: 2

Related Questions