SQL Natural Join

What is SQL Natural Join?

Natual Join is a type of join in SQL which combines row from 2 tables based on common column which has same name and datatype. It automatically matches the 2 columns and eliminates the duplicate column. read more

Example

Database

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    City VARCHAR(50)
);

CustomerID	FirstName	LastName	Email	City
1	John	Doe	[email protected]	New York
2	Jane	Smith	[email protected]	Los Angeles
3	Emily	Johnson	[email protected]	Chicago

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    City VARCHAR(50),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

OrderID	CustomerID	OrderDate	TotalAmount	City
101	1	2024-01-10	250.50	New York
102	2	2024-01-11	150.00	Los Angeles
103	3	2024-01-12	300.75	Chicago

Syntax

SELECT *

FROM TABLE1

NATURAL JOIN TABLE2;

Query

SELECT * 
FROM Orders
NATURAL JOIN Customers;

Explanation

In this scenario, SQL combines the Orders and Customers tables using a natural join. The join is performed based on the common columns, CustomerID and City, which appear at the beginning of the resulting table.

As a result, SQL brings together columns like FirstName, LastName, and Email from the Customers table, along with OrderID, OrderDate, and TotalAmount from the Orders table. The shared columns, CustomerID and City, are seamlessly included in the result without duplication, making the output clear and concise.

---

I hope this explanation was helpful for you, consider sharing this with your friends. thank you.

• Write a query to identify the employee(s) whose salary is closest to the average salary of the company.
• Display all months where sales exceeded the average monthly sales.
• Find the most common value (mode) in a specific column.
• Identify employees who have never received a performance review.
• Write a query to retrieve the first order placed by each customer.