MySQL – Natural Join

When two or more tables are joined based on a common column, the operation is known as joining. Natural join is a type of join operation in which tables are combined based on columns with the same name and type. It is similar to an INNER or LEFT JOIN, but we cannot use the ON or USING clause with a natural join.

• To join columns, you do not need to specify column names.
• There is always a unique column in the resultant table.
• Performing a natural join on more than two tables is possible.
• It is not necessary to use the ON clause.

Syntax:

SELECT [column_names | *]   
FROM table_name1   
NATURAL JOIN table_name2;  

In this syntax, the column names to be included in the result set must be specified after the SELECT keyword. In order to select all columns from both tables, the * operator will be used. We will then specify the table names for joining after the FROM keyword and write the NATURAL JOIN clause between them.

Example

SELECT dept.emp_no, emp.salary   
FROM dept_emp AS dept   
NATURAL JOIN employees  AS emp;