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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – SSMS Query Command(s) completed successfully without ANY Results – Part 2
Next Post
SQL SERVER – Database Testing and Waitfor Delay – Notes from the Field #099

Related Posts

3 Comments. Leave new

  • Benjamin Steinfeld
    October 8, 2015 10:10 am

    In my experience, the single biggest advantage of consistently using ANSI style JOINs is that the query doesn’t have to be rewritten to use other JOIN operators (Left/Right JOINs) when including other tables.

    Reply
  • Tribhuwan Mishra
    October 8, 2015 11:27 am

    Sir, Very Nice Query For Inner Join

    Reply
  • Hi ,
    I tried executing the same SQL over SQL 2005 and it worked as expected (both sql returns same result). Could you please let me know why this has happened.

    Reply

Leave a Reply

Menu