Just received a question in email:
“What is the Difference Between An INNER JOIN and WHERE Clause?”
Well, the answer can be quite long but I will try to answer in few words and a very simple one.
If you are talking about resultset – there will be not much different. If you are talking about performance – SQL Server engine is much smarter that it will automatically re-write your quest in most of the cases so you will have no difference in performance. With that said, I still prefer that you use INNER JOIN when a query involves more than one table as that is the ANSI valid syntax.
If you are ever going to use OUTER JOIN, the question of JOIN vs WHERE does not make sense at all, as the answer may be different in most of the cases. Here is the quick example of the same.
Let us create two tables and select the sample data from the table.
USE TempDB
GO
-- Creating Sales Rep Table
CREATE TABLE SalesRep (ID INT, SalesRep VARCHAR(10))
GO
INSERT INTO SalesRep (ID, SalesRep)
SELECT 1, 'Pinal'
UNION ALL
SELECT 2, 'Vinod'
UNION ALL
SELECT 3, 'Balmukund'
UNION ALL
SELECT 4, 'Chandra'
UNION ALL
SELECT 5, 'DJ'
GO
-- Creating Sales Orders Table
CREATE TABLE Orders (ID INT, Amount INT)
GO
INSERT INTO Orders (ID, Amount)
SELECT 1, 100
UNION ALL
SELECT 1, 200
UNION ALL
SELECT 2, 500
UNION ALL
SELECT 2, 300
UNION ALL
SELECT 3, 600
GO
-- Select Data
SELECT *
FROM SalesRep
GO
SELECT *
FROM Orders
GO
Here is the resultset of the data selected from above two tables.
Now let us see a quick example of the INNER JOIN and WHERE condition.
-- JOIN
SELECT s.SalesRep, o.Amount
FROM SalesRep s
INNER JOIN Orders o ON s.id = o.id
GO
-- WHERE
SELECT s.SalesRep, o.Amount
FROM SalesRep s, Orders o
WHERE s.id = o.id
GO
You will notice from the resultset both of the query returns exactly same result. As I mentioned earlier when we are using INNER JOIN and WHERE clause, there is no impact of the resultset if the JOIN condition and WHERE clause have almost same condition.
Let us see a quick example where Outer Join gives absolutely different results compared to where as there is totally different business logic when you have to use outer join. As I mentioned above, it is like comparing apples and oranges if you compare outer join and where clause. They are not the same logically.
-- JOIN
SELECT s.SalesRep, o.Amount
FROM SalesRep s
LEFT OUTER JOIN Orders o ON s.id = o.id
GO
-- WHERE
SELECT s.SalesRep, o.Amount
FROM SalesRep s, Orders o
WHERE s.id = o.id
GO
Here is the script to clean up the tables created.
-- Clean up
DROP TABLE SalesRep
DROP TABLE Orders
GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
It would be appreciated if you expanded the post to explain why the end result is different.
Your outer join result set is different because you didn’t use the correct form for conducting a left outer join in the where clause. If you had used WHERE s.id *= o.id, which is the correct syntax for a left outer join in the where clause, your result sets would be the same. I do agree that you should use the “join” method as it increases readability by keeping the join criteria separate from the filter criteria.
Dear Pinal,
Actually I share the same question. But in cases when a table with few million rows is combined with a smaller table, we had seen slightly noticable differences with the where condition having a slight edge.
Kindly correct me if I am wrong.
You are correct about Apples and Oranges. The left join example with the where clause is not in fact an outer join it is an inner join. It the earlier syntax you would use either *= for left outer join or =* for right outer join.
So in your second example you are comparing the output of a inner join to the output of an inner join.
If you try the older Where clause syntax in SQL 2008R2 you will receive Error 4147 and the message that “The query uses non-ANSI outer join operators (“*=” or “=*”). ….. You have to set the db compat level to 8.0 to use the older syntax.
Ray
I don’t feel your example goes far enough. What I was hoping to get was the difference between where and the join based on what result sets we see when we try to restrict the query on the join rather than in the where clause.
For instance, when I put s.id = 2 on the left join and do not use a where clause I get all the names with null amounts except for Vinod. (same happened when I used o.id=2) . If I moved the search down to the where clause only Vinod came back with his two totals. An explanation I saw on one web site for this was due to the results set of the two tables not being joined yet, as for the Where clause, it doesn’t make an evaluation until after the result set is back. I think this needs to be pointed out in your article to show the dangers of placing restrictions on the join and not the where when it comes to using outer joins.
The thing I’m still trying to wrap my head around is why I get the same results in the join whether I pick the SalesRep table or the orders table?
Dear Pinal, Which one of the following 2 queries is the preferred way. The goal is same and the results are same between the 2 queries. The difference is in the Join vs Where clause.
–Query Goal: Return a list of Sales Reps who have Sales Orders
SELECT DISTINCT SR.ID, SR.SalesRep
FROM SalesRep SR
INNER JOIN Orders O
ON SR.ID = O.ID
SELECT ID, SalesRep
FROM SalesRep
WHERE ID IN(SELECT ID FROM Orders)