If you are working with SQL Server and need to join two tables, there are two ways to do it: using ANSI compliant INNER JOIN or using the old style join. However, it is highly advisable to avoid using the old style join. Let’s explore the reasons behind this recommendation.
First, let’s create some sample data sets to work with:
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;
Now, let’s say we want to display each item description along with the total quantity sold. We can achieve this using two different methods:
Method 1: ANSI Compliant 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;
Method 2: 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 methods will return the following result:
| item_description | qty |
|---|---|
| LG | 25 |
| Samsung | 2 |
However, the problem arises when you accidentally omit the WHERE condition in Method 2:
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 will be:
| item_description | qty |
|---|---|
| LG | 27 |
| Samsung | 27 |
This is completely wrong as it leads to a cross join, resulting in incorrect data. On the other hand, Method 1 will throw an error if no JOIN condition 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 message will be: “Incorrect syntax near the keyword ‘group’.”
In summary, it is always recommended to use an ANSI compliant INNER JOIN when working with SQL Server. This ensures that your queries are accurate and avoids potential errors or incorrect results. So, next time you need to join tables in SQL Server, remember to use the ANSI compliant INNER JOIN.
Reference: Pinal Dave (https://blog.sqlauthority.com)