SQL Reference - NATURAL

Example

SELECT *
FROM table1
NATURAL JOIN table2;

Output

+------+------+
| ID   | Name |
+------+------+
| 1    | John |
| 2    | Jane |
+------+------+

Explanation

The SQL command above executes a natural join between table1 and table2. In a natural join, only the rows with common values in both tables are returned. In our output, columns ID and Name are the common fields in both tables, so only the rows with these values are displayed.

Example

CREATE TABLE table1(
    id INT,
    name VARCHAR(20)
);

INSERT INTO table1 VALUES(1, 'John');
INSERT INTO table1 VALUES(2, 'Samantha');

CREATE TABLE table2(
    id INT,
    address VARCHAR(50)
);

INSERT INTO table2 VALUES(1, '123 Main St');
INSERT INTO table2 VALUES(2, '456 Oak St');

SELECT * FROM table1
NATURAL JOIN table2;

Output

 id |   name   |    address
----+----------+---------------
  1 | John     | 123 Main St
  2 | Samantha | 456 Oak St
(2 rows)

Explanation

The NATURAL JOIN clause is used to combine rows from two tables (table1 and table2) based on the common columns between them. In this case, the common column is ‘id’. So, the rows with the same ‘id’ values in both tables will be combined and displayed in the result.

Example

SELECT *
FROM Employees
NATURAL JOIN Departments;

Output

| EmployeeID | EmployeeName | DepartmentID | DepartmentName |
|------------|--------------|--------------|----------------|
| 1          | John Doe     | 1            | IT             |
| 2          | Jane Doe     | 2            | HR             |
| 3          | Jim Brown    | 2            | HR             |
| 4          | Jenny Smith  | 1            | IT             |

Explanation

The NATURAL JOIN keyword in Oracle is used to perform a join that returns rows with matching values in both tables. In the above example, the NATURAL JOIN keyword is used to return all rows from Employees and Departments where there is a match based on column names.

Example

CREATE TABLE Customers(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
);

CREATE TABLE Orders(
   O_ID          INT PRIMARY KEY    NOT NULL,
   O_DATE        DATE NOT NULL,
   CUSTOMER_ID   INT NOT NULL,
);

INSERT INTO Customers(ID, NAME, AGE, ADDRESS)
VALUES(1, 'John', 28, 'New York');

INSERT INTO Orders(O_ID, O_DATE, CUSTOMER_ID)
VALUES(1, '2022-09-16', 1);

SELECT * FROM Customers
NATURAL JOIN Orders;

Output

ID | NAME | AGE | ADDRESS  | O_ID | O_DATE
1  | John | 28  | New York | 1    | 2022-09-16

Explanation

In the example, customers and orders tables are joined with NATURAL JOIN. NATURAL JOIN automatically fetches the common column (in this case, the ID column) from both tables and merges them based on the common values. Hence, when selected, it displays the record of John who has made an order, combining data from both tables.