PostgreSQL: Natural Join

If two or more tables have parent - child relationships defined using the same column name then NATURAL JOIN can be used to take join between them.

SELECT <table_1.column_name(s)>, <table_2.column_name(s)>
FROM <table_1>
NATURAL [ INNER, LEFT, RIGHT ] JOIN <table_2>;

The Natural join can be INNER JOIN, LEFT JOIN or RIGHT JOIN with default as INNER JOIN.

For the demo purpose, we will use the following Department and Employee tables in all examples.

Notice Department table is parent table with dept_id as a primary key. Employee table is child table that has dept_id as foreign key referencing dept_id column of Department table.

Now let's join both the first table (Employee) with a second table (Department) by matching values of dept_id column. Please note we are using table alias here, referring to Employee table as emp and Department table as dept.

We can use NATURAL JOIN as Department and Employee table have same column name dept_id as joining column.

SELECT emp.emp_id, emp.first_name, emp.last_name,
	dept.dept_id, dept.dept_name
FROM Employee emp NATURAL JOIN Department dept
ORDER BY emp.emp_id;

Notice above, we do not need to specify any join predicate if we use NATURAL JOIN. The above query is equivalent to below query and will return the same result.

SELECT emp.emp_id, emp.first_name, emp.last_name,
	dept.dept_id, dept.dept_name
FROM Employee emp INNER JOIN Department dept
USING (dept_id)
ORDER BY emp.emp_id;

Same way, Natural join can be used to perform LEFT JOIN and RIGHT JOIN also.