MySQL – How to do Natural Join in MySQL? – A Key Difference Between Inner Join and Natural Join

As you know MySQL supports ANSI JOINs like INNER JOIN, OUTER JOIN, CROSS JOIN etc. Do you know that it supports NATURAL JOIN?. A NATURAL JOIN is a type of JOIN which automatically maps the similar columns from both the tables.

Let us create the following tables
CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment 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);

By using the NATURAL JOIN, we can select only distinct columns by using * in the SELECT statement.
SELECT * FROM items NATURAL JOIN sales;

The result is

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 have noticed, * does not return all the columns from both the tables. The common column item_id is chosen first, followed by other columns in the tables items and sales. But if you use INNER JOIN with SELECT * all the columns will be selected which will result to duplicate columns in the resultset.

You can also 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 item_id is used to map both the tables.

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s