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)
