MySQL NATURAL JOIN

The natural join combines multiple tables using a column with the same name and data type. This operation combines a row of the two tables using common columns. This join is similar to an inner join and a left join.  The natural join does not use "ON" and "USING" clauses with conditions. This join can also use with more than two tables.  The natural join does not need to identify a common column name to join tables. Instead, it includes unique columns automatically.

Rule of the natural join

The following rules always apply to the MySQL natural join.

• The natural join works similarly as an inner join with the ON clause.
• The natural join does not use the "ON" clause in the query.
• The natural join does not use the "USING" clause in the query.

Syntax

The syntax of the natural join shows below.

SELECT
Column1 data type constraint,
Column2 data type constraint,
Column3 data type constraint,
FROM table name1
NATURAL JOIN table2 ON condition1;

It is another syntax of the natural join.

SELECT *FROM table name1
NATURAL JOIN table2 ON condition1;

Examples of the MySQL natural join

1) Example: the NATURAL JOIN with two table's example shows below.

Execute the below query to join two tables. Two tables join uses required columns using similar conditions.

mysql> select d.department_name, d.department_id, 
s.subject_name, s.subject_id
FROM department d
NATURAL JOIN subject s;

OUTPUT

The above image shows combined columns of the two tables using natural join. The "department_name" and "department_id" columns display in the department table. The "subject_name" and "students" columns display in the subject table.

2) Example: Execute the below natural join query to join two tables. Two tables join with required columns using similar conditions. Here, the query selects the entire columns of the table.

mysql> select *
FROM department d
NATURAL JOIN subject s;

OUTPUT

The above image shows the entire columns of the department and student tables.

3) Example: Execute the below query to join two tables. Two tables join all columns using the "NATURAL JOIN" query. This query uses the WHERE clause with NATURAL JOIN.

mysql> select d.department_name, d.department_id, s.subject_name, s.subject_id
FROM department d
NATURAL JOIN subject s
WHERE subject_id > 3;

OUTPUT

The above image shows the required columns of the two tables using natural join. Here, you can see four columns and two rows.

4) Example: Execute the below query to join two tables with the required columns. Two tables join four columns using the "NATURAL JOIN" query. This query uses the "ORDER BY" clause with NATURAL JOIN.

mysql> select d.department_name, d.department_id, s.subject_name, s.subject_id
FROM department d
NATURAL JOIN subject s
ORDER BY 
department_id DESC,
subject_id DESC;

OUTPUT

The above output image shows the assigned columns of the two tables using natural join. Here, you can see four columns in descending order of data.

5) Example: Execute the below natural join query to join two tables. For example, the "NATURAL JOIN" query uses "WHERE" with "AND" conditions.

mysql> select d.department_name, d.department_id,
s.subject_name, s.subject_id
FROM department d
NATURAL JOIN subject s
WHERE d.department_name = "Bachelor of Technology"
AND  s.subject_name = "Information Technology";

OUTPUT

The above image shows the columns of the two tables. This query shows the required columns of the department table and student table. The cross query works with the logical condition. Both conditions fulfill using "AND" logical condition.

6) Example: Execute the below natural join query to join two tables. Two tables join required columns using conditions. For example, the "NATURAL JOIN" query uses "WHERE" with "OR" conditions.

mysql> select d.department_name, d.department_id,
s.subject_name, s.subject_id
FROM department d
NATURAL JOIN subject s
WHERE d.department_name = "Bachelor of Technology"
OR  s.subject_name = "computer science";

OUTPUT

The above image shows the columns of the two tables. This query shows the required columns of the department table and student table. The cross query works with the logical condition. Both conditions fulfill using the "OR" logical condition.

7) Example: the natural join with the "GROUP BY" clause example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions. For example, the "NATURAL JOIN" query uses the "GROUP BY" clause with the "subject_id" foreign key. The natural join uses AND logical operator.

mysql> select d.department_name, d.department_id,
s.subject_name, s.subject_id
FROM department d
NATURAL JOIN subject s
WHERE d.department_name = "Bachelor of Engineering"
GROUP BY subject_id;

OUTPUT

The above output image displays combined columns of the department and subject tables. The "subject_id" column connects two tables using a foreign key. The above table works natural join query with the "GROUP BY" clause.

8) Example: the natural join with the multiple clause example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions. For example, the "NATURAL JOIN" query uses "ORDER BY" and "using" clauses with the "subject_id" foreign key. The natural join uses OR logical operator.

mysql> select d.department_name, d.department_id,
s.subject_name, s.subject_id
FROM department d
NATURAL JOIN subject s
WHERE d.department_name = "Bachelor of Technology"
OR  s.subject_name = "Computer Science"
ORDER BY department_id DESC, subject_id DESC;

OUTPUT

The above output table displays combined columns of the department and subject tables. The "subject_id" column connects two tables using a foreign key. The above table works natural join query with the "USING" clause. Here, the table applies a natural join query with the "ORDER BY" clause. The table data display in descending order.