Natural Join in DBMS SQL | Board Infinity

Introduction

In DBMS SQL, a natural join is a join operation that combines tables based on matching column names and their data types. It returns a result set that contains only the standard columns between the two tables, without including duplicate columns.

The primary functions of Natural Join are as follows:

Combines data from two tables.
Eliminates duplicate columns.
Performs Cartesian product.

Syntax:

The syntax for combining 2 or more tables with the Natural Join technique is as follows,

SELECT column_name(s)

FROM table1

NATURAL JOIN table2;

In this syntax, table1 and table2 are the names of the tables you want to join, and column_name(s) are the columns you want to select from the joined table. When you use the NATURAL JOIN clause, SQL will automatically match the columns with the same name and data type in both tables.

Example:

Here is an example code in SQL for creating a database with two tables and performing a natural join operation. The code illustrates creating a database called “Board_Infinity”, a user table and orders table. Both the “user” and “orders” tables are joined by natural join.

Code Implementation

-- Create a new database named "Board_Infinity"
CREATE DATABASE Board_Infinity;

-- Switch to the new database
USE Board_Infinity;

-- Create a table named "users"
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

-- Insert some sample data into the "users" table
INSERT INTO users (id, name, age) VALUES
(1, 'John Doe', 30),
(2, 'Jane Doe', 25),
(3, 'Bob Smith', 45);

-- Create a table named "orders"
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
product_name VARCHAR(50),
quantity INT
);

-- Insert some sample data into the "orders" table
INSERT INTO orders (id, user_id, product_name, quantity) VALUES
(1, 1, 'Widget', 2),
(2, 1, 'Gizmo', 1),
(3, 3, 'Widget', 3);

-- Performing a natural join operation between the tables
SELECT users.name, orders.product_name, orders.quantity
FROM users
NATURAL JOIN orders;

Output:

write your code here: Coding Playground

The NATURAL JOIN operation will join the two tables based on the columns with the same name and data type, which in this case is the "id" column of the "users" table and the "user_id" column of the "orders" table.

Conclusion

Natural joins can be less efficient than other types of joins, especially if the tables being joined have many columns or if the matching columns have many duplicates. In some cases, it may be more efficient to use an explicit join condition using the ON or USING clause, which allows you to specify the columns to join explicitly.