Natural Join
Sql tutorial · PySpark.in
7.1 NATURAL JOIN
Until now, we have queried data from a single table. In real-world databases, however, related information is often stored in multiple tables. For example, course details may be stored in one table and instructor details in another. To combine related data from different tables, we use a JOIN.
A NATURAL JOIN automatically combines two tables using the column or columns that have the same name in both tables. Unlike other joins, you do not need to explicitly write the join condition.
Syntax:
```
hide
sql
SELECT * FROM table1
NATURAL JOIN table2;
```
Example:
Suppose we have two tables: course and instructor.Fetch the details of courses that are being taught by "Alex".Solving this problem involves querying on data stored in two tables, i.e., course & instructor. Both the tables have common column instructor_id. Hence, we use Natural Join.
```
sql
-- Create instructor table
CREATE TABLE instructor (
instructor_id INT PRIMARY KEY,
full_name VARCHAR(100)
);
-- Insert data into instructor
INSERT INTO instructor (instructor_id, full_name) VALUES
(1, 'Alex'),
(2, 'John'),
(3, 'Emma');
-- Create course table
CREATE TABLE course (
course_id INT PRIMARY KEY,
name VARCHAR(100),
instructor_id INT
);
-- Insert data into course
INSERT INTO course (course_id, name, instructor_id) VALUES
(101, 'SQL Basics', 1),
(102, 'Python Basics', 2),
(103, 'Advanced SQL', 1),
(104, 'Data Analytics', 3),
(105, 'Database Design', 1);
-- Execute query
SELECT course.name, instructor.full_name
FROM course
NATURAL JOIN instructor
WHERE instructor.full_name = 'Alex';
```
How It Works
- The course table contains course information.
- The instructor table contains instructor information.
- Since both tables have a column named instructor_id, SQL automatically matches rows where the values of instructor_id are equal.
-
After joining the tables, the Where clause filters only those rows where the instructor’s name is Alex.
Important Point
A NATURAL JOIN uses all columns with the same name in both tables. This makes it easy to write, but sometimes risky. If another common column is added later, SQL will use that too, which may produce unexpected results.
For this reason, in real projects developers often prefer an INNER JOIN with an explicit condition.
More Sql tutorials
All tutorials · Try the free PySpark compiler · Practice challenges