SQL SERVER – Why Should You Not to Use Old Style JOIN?

If you want to JOIN two tables you can do it in two ways. One using ANSI compliant INNER JOIN and another with old style join

But it is advisable not to use old style join. Here is the reason.

Let us create the following data sets

CREATE TABLE item_master(item_id INT, item_description VARCHAR(100));
CREATE TABLE item_sales(item_id INT, sales_date DATETIME, qty INT);
INSERT INTO item_master
SELECT 1,'Samsung' UNION ALL
SELECT 2,'LG'; 
INSERT INTO item_sales
SELECT 1,'2015-04-03 12:10:10',2 UNION ALL
SELECT 2,'2015-06-11 07:22:00',3 UNION ALL
SELECT 2,'2015-06-12 11:00:48',22; 

If you want to show each item_item description with total quantity that are sold, you can use this code

Method 1 : INNER JOIN

SELECT item.item_description,SUM(details.qty) AS qty FROM item_master AS item
INNER JOIN item_sales AS details
ON item.item_id=details.item_id
GROUP BY item.item_description;

Method2 : Old style join with WHERE clause

SELECT item.item_description,SUM(details.qty) AS qty FROM item_master AS item
,item_sales AS details
WHERE item.item_id=details.item_id
GROUP BY item.item_description;

Both returns the following result

item_description qty
 ------------------- ----------
 LG 25
 Samsung 2

But what happens if you omit the WHERE condtion in method 2 by mistake

SELECT item.item_description,SUM(details.qty) AS qty FROM item_master AS item
,item_sales AS details
GROUP BY item.item_description;

The result is

item_description qty
 ------------------- ----------
 LG 27
 Samsung 27

Which is totally wrong as it leads to cross join

But method 1 will throw error if no JOIN is specified

SELECT item.item_description,SUM(details.qty) AS qty FROM item_master AS item
INNER JOIN item_sales AS details
GROUP BY item.item_description;

The error is

Incorrect syntax near the keyword 'group'.

In summary, always use an ANSI compliant INNER JOIN.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.