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:
SELECT [column_names | *]
FROM table_name1
NATURAL JOIN table_name2; 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:
/* -- Table name: customer -*/
CREATE TABLE customer (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(55),
account int,
email VARCHAR(55)
);
/* -- Table name: balance -*/
CREATE TABLE balance (
id INT AUTO_INCREMENT PRIMARY KEY,
account int,
balance FLOAT(10, 2)
); Next, we will fill some records into both tables using the below statements:
/* -- Data for customer table -*/
INSERT INTO customer(customer_name, account, email)
VALUES('Stephen', 1030, '[email protected]'),
('Jenifer', 2035, '[email protected]'),
('Mathew', 5564, '[email protected]'),
('Smith', 4534, '[email protected]'),
('David', 7648, '[email protected]');
/* -- Data for balance table -*/
INSERT INTO balance(account, balance)
VALUES(1030, 50000.00),
(2035, 230000.00),
(5564, 125000.00),
(4534, 80000.00),
(7648, 45000.00); Next, we will execute the SELECT statement to verify the table data:
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:
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:
mysql> SELECT cust. customer_name, bal.balance
FROM customer AS cust
NATURAL JOIN balance AS bal; We will get the following result:
We can do the same job with the help of INNER JOIN using the ON clause. Here is the query to explain this join:
mysql> SELECT cust. customer_name, bal.balance
FROM customer AS cust
INNER JOIN balance AS bal
ON cust.id = bal.id; After successful execution, we will get the same result as the natural join:
Now, we will use (*) in the place of column names as follows:
mysql> SELECT * FROM customer NATURAL JOIN balance; 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:
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:
mysql> SELECT cust. customer_name, bal.balance
FROM customer AS cust
NATURAL JOIN balance AS bal
WHERE bal.balance > 50000; We will get the following result where customer information is displayed whose account balance is greater than 50000.
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:
SELECT [column_names | *]
FROM table_name1
NATURAL JOIN table_name2
NATURAL JOIN table_name3; Let us create another table named cust_info using the below statement:
CREATE TABLE cust_info (
account int,
mobile VARCHAR(15),
address VARCHAR(65)
);Then, we will fill records into this table:
INSERT INTO cust_info(account, mobile, address)
VALUES(1030, '598675498654', '777 Brockton Avenue, Abington MA 251'),
(2035, '698853747888', '337 Russell St, Hadley MA 103'),
(5564, '234456977555', '20 Soojian Dr, Leicester MA 154'),
(4534, '987656789666', '780 Lynnway, Lynn MA 19'),
(7648, '756489372222', '700 Oak Street, Brockton MA 23'); We can verify the data using the SELECT statement. See the below image:
To join three tables using natural join, we need to execute the statement as follows:
mysql> SELECT C.customer_name, C.account, B.balance, I.mobile
FROM customer AS C
NATURAL JOIN balance AS B
NATURAL JOIN cust_info AS I;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.
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; |
Leave a Reply