Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • PostgreSQL - Get Started
  • Install PostgreSQL
  • Connect to PostgreSQL DB
  • Create Database
  • Create Table
  • Copy Table
  • Drop Table
  • Drop Database
  • Truncate Table
  • ALTER Table
  • Rename Table
  • Rename Columns
  • Add Columns
  • Modify Column Type
  • Set Default Value of Column
  • Remove Columns
  • Add Constraints to Table
  • Insert Data
  • Upsert Data
  • Update Data
  • Delete Data
  • SELECT Statement
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • DISTINCT Clause
  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self Join
  • Natural Join
  • Cross Join
  • LIMIT OFFSET Clause
  • GROUPING SETS
  • GROUPING() Function
  • GROUP BY CUBE
  • GROUP BY ROLLUP
  • Sub Query
  • ALL Operator
  • ANY Operator
  • UNION Operator
  • INTERSECT Operator
  • EXCEPT Operator
  • IS NULL Operator
  • BETWEEN Operator
  • LIKE Operator
  • CAST Operator
  • CASE Expressions
  • NULLIF()
  • COALESCE()
  • GREATEST(), LEAST()
  • WITH Queries (CTE)
  • Constraints
  • NOT NULL Constraint
  • Unique Constraint
  • Check Constraint
  • Primary Key
  • Foreign Key
  • Sequence
  • Serial Type
  • Identity Columns
  • Generated Columns
  • Data Types
  • Boolean Type
  • Character Type
  • Integer Type
  • Numeric Type
  • Date Type
  • Time Type
  • TimeStamp Type
  • Interval Type
  • Array Type
  • Json Type
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

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.

Example: Natural Join
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.

Example: Natural Join
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.

Example: Natural Join
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.

TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.