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 (https://blog.sqlauthority.com)
