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: Inner Join

The INNER JOIN query is used to retrieve the matching records from two or more tables based on the specified condition. PostgreSQL follows the SQL standards for inner join queries.

Here is a diagram that represents INNER JOIN.

Syntax
SELECT <table_1.column_name(s)>, <table_2.column_name(s)>
FROM <table_1>
INNER JOIN <table_2>
    ON <table_1.column_name> = <table_2.column_name>;

As per the above syntax, we have table_1 and table_2 and they have some matching columns between them. This way, we can retrieve data from both tables based on matching conditions specified.

Let's use the following Employee and Department tables to demonstrate inner joins.

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

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

Example: Inner Join
SELECT emp.emp_id, emp.first_name, emp.last_name, dept.dept_id, dept.dept_name 
FROM Employee emp INNER JOIN Department dept
ON emp.dept_id = dept.dept_id;

Here, the INNER JOIN compares the value in dept_id column of every row of the Employee table with the value of dept_id column of every row of the Department table. It will show only rows from both tables that have matching values. Hence it does not show emp_id = 6, who does not belong to any department.

From the resultset, we can select any column from Employee or Department table in the SELECT clause. As you can see, in the above query we selected emp_id, first_name, and last_name from the Employee table and dept_id and dept_name from the Department table.

If both the tables have same column name used for joining in ON clause, like in our case dept_id is joining column from Employee and Department tables, then you can use the USING syntax like this

Example: USING()
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);
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.