Javatpoint Logo
Javatpoint Logo

MySQL Natural Join

When we combine rows of two or more tables based on a common column between them, this operation is called joining. A natural join is a type of join operation that creates an implicit join by combining tables based on columns with the same name and data type. It is similar to the INNER or LEFT JOIN, but we cannot use the ON or USING clause with natural join as we used in them.

Points to remember:

  • There is no need to specify the column names to join.
  • The resultant table always contains unique columns.
  • It is possible to perform a natural join on more than two tables.
  • Do not use the ON clause.

Syntax:

The following is a basic syntax to illustrate the natural join:

In this syntax, we need to specify the column names to be included in the result set after the SELECT keyword. If we want to select all columns from both tables, the * operator will be used. Next, we will specify the table names for joining after the FROM keyword and write the NATURAL JOIN clause between them.

Natural Join Example

Let us understand how natural join works in MySQL through examples. First, we will create two tables named customer and balance using the below statements:

Next, we will fill some records into both tables using the below statements:

Next, we will execute the SELECT statement to verify the table data:

MySQL Natural Join

Now, we will see the condition that fulfills the criteria for natural join. We can do this by examining the table structure using the DESCRIBE statement. See the below image:

MySQL Natural Join

In this image, we can see that column names id and account and its data types are the same that fulfill the natural join criteria. Hence we can use natural join on them.

Execute the below statement for joining tables using natural join:

We will get the following result:

MySQL Natural Join

We can do the same job with the help of INNER JOIN using the ON clause. Here is the query to explain this join:

After successful execution, we will get the same result as the natural join:

MySQL Natural Join

Now, we will use (*) in the place of column names as follows:

Suppose we use the asterisk (*) in the place of column names, then the natural join automatically searches the same column names and their data types and join them internally. Also, it does not display the repeated columns in the output. Hence, we should get the below output after executing the above statement:

MySQL Natural Join

Natural Join with WHERE Clause

The WHERE clause is used to return the filter result from the table. The following example illustrates this with the natural join clause:

We will get the following result where customer information is displayed whose account balance is greater than 50000.

MySQL Natural Join

Natural Join Using Three Tables

We know that natural join can also perform a join operation on more than two tables. To understand this, we will use the syntax as follows:

Let us create another table named cust_info using the below statement:

Then, we will fill records into this table:

We can verify the data using the SELECT statement. See the below image:

MySQL Natural Join

To join three tables using natural join, we need to execute the statement as follows:

It will give the below result. Here we can see that the account number is present in all three columns but arrived only once in the output that fulfills the natural join criteria.

MySQL Natural Join

Difference between Natural Join and Inner Join

SN Natural Join Inner Join
1. It joins the tables based on the same column names and their data types. It joins the tables based on the column name specified in the ON clause explicitly.
2. It always returns unique columns in the result set. It returns all the attributes of both tables along with duplicate columns that match the ON clause condition.
3. If we have not specified any condition in this join, it returns the records based on the common columns. It returns only those rows that exist in both tables.
4. The syntax of natural join is given below:
SELECT [column_names | *]
FROM table_name1
NATURAL JOIN table_name2;
The syntax of inner join is given below:
SELECT [column_names | *]
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name;





Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA