MySQL: Natural Join

Unlike any other joins in MySQL, the natural join is a unique type of join where the tables are joined automatically using the clause NATURAL JOIN.

During this join the tables which are meant to join are automatic sets together using the common column present on both sides which is similar to inner and left join.So here we don't need to use the ON and USING clause too.

Rules For Using NATURAL JOIN

• No need for an ON clause.
• No need of specifying the column names to join the tables.
• Can be done on more than 2 tables too.

Syntax

SELECT * from TableName NATURAL JOIN TableName2;

Downside Of NATURAL JOIN

• Although we don't need to explicitly mention columns and the database engine will find the common one to match and easy to code but they can be a little dangerous.
• Because we are letting the database engine guess the join we don't have control over it for this very reason natural join can produce unexpected results and so can't be relied often upon.

Let us dive into a real-time example.

We will be using the orders and customers table to fetch the order_id from orders and the names of the customers who placed them.

Example

select o.order_id, c.first_name from orders o natural join customers c;

• Here we have got the order_id and first name where the database engine decided to match the customer id concerning both the orders and customers table so it has yielded the relevant result we expected.
• So from this, we can now understand how the natural join works.