When working with SQL Server, you may come across various types of joins such as INNER JOIN, OUTER JOIN, and CROSS JOIN. But did you know that SQL Server also supports NATURAL JOIN?
A NATURAL JOIN is a type of join that automatically maps similar columns from both tables. This can be useful when you want to retrieve data based on common column values without explicitly specifying the join condition.
Let’s take a look at an example to understand how NATURAL JOIN works:
CREATE TABLE items (
item_id INT,
item_description VARCHAR(100)
);
CREATE TABLE sales (
sales_id INT AUTO_INCREMENT PRIMARY KEY,
item_id INT,
sales_date DATETIME,
sales_amount DECIMAL(12, 2)
);
INSERT INTO items VALUES (1, 'Television');
INSERT INTO items VALUES (2, 'Mobile');
INSERT INTO items VALUES (3, 'Laptop');
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (1, '2014-01-01', 1200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-01-02', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-01-09', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-01-29', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-02-11', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (1, '2014-02-16', 1200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-16', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-20', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-20', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-22', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-02-24', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (1, '2014-02-24', 1200);
Now, let’s use the NATURAL JOIN to select only distinct columns from both tables:
SELECT * FROM items NATURAL JOIN sales;The result of the above query will be:
| item_id | item_description | sales_id | sales_date | sales_amount |
|---|---|---|---|---|
| 1 | Television | 1 | 2014-01-01 00:00:00 | 1200.00 |
| 2 | Mobile | 2 | 2014-01-02 00:00:00 | 200.00 |
| 3 | Laptop | 3 | 2014-01-09 00:00:00 | 1700.00 |
| 3 | Laptop | 4 | 2014-01-29 00:00:00 | 1700.00 |
| 3 | Laptop | 5 | 2014-02-11 00:00:00 | 1700.00 |
| 1 | Television | 6 | 2014-02-16 00:00:00 | 1200.00 |
| 2 | Mobile | 7 | 2014-02-16 00:00:00 | 200.00 |
| 2 | Mobile | 8 | 2014-02-20 00:00:00 | 200.00 |
| 2 | Mobile | 9 | 2014-02-20 00:00:00 | 200.00 |
| 2 | Mobile | 10 | 2014-02-22 00:00:00 | 200.00 |
| 3 | Laptop | 11 | 2014-02-24 00:00:00 | 1700.00 |
| 1 | Television | 12 | 2014-02-24 00:00:00 | 1200.00 |
As you can see, the NATURAL JOIN automatically selects the common column “item_id” from both tables and includes it in the result set. Other columns from the “items” and “sales” tables are also included.
It’s important to note that when using the “*” in the SELECT statement with an INNER JOIN, all columns from both tables will be selected, which can result in duplicate columns in the result set. To avoid this, you can use the USING clause with INNER JOIN to produce the same result:
SELECT * FROM items INNER JOIN sales USING (item_id);In the above statement, the “item_id” column is used to map both tables and retrieve the desired result.
Understanding different types of joins in SQL Server, including NATURAL JOIN, can greatly enhance your ability to retrieve and analyze data efficiently. Experiment with these joins in your own SQL Server environment to gain a deeper understanding of their functionality.