EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Inner Join
Secondary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • Postgres Command-Line
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL BIGINT
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
    • Postgres like query
    • PostgreSQL encode
    • PostgreSQL Cheat Sheet
    • PostgreSQL List Databases
    • PostgreSQL Rename Database
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL INSERT INTO
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL ORDER BY DESC
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL Drop Schema
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL User Password
    • PostgreSQL log_statement
    • PostgreSQL repository
    • PostgreSQL shared_buffer
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL where in array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL CHECK Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • PostgreSQL List Tables
    • PostgreSQL TRUNCATE TABLE
    • PostgreSQL Table Partitioning
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL datediff
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL LENGTH()
    • PostgreSQL blob
    • PostgreSQL Median
    • PostgreSQL kill query
    • PostgreSQL Formatter
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL UUID
    • PostgreSQL Merge
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Show Databases
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL Show Tables
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL Encryption
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL Incremental Backup
    • PostgreSQL JSON vs JSONNB
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions

PostgreSQL Inner Join

By Sohel SayyadSohel Sayyad

PostgreSQL Inner Join

Introduction to PostgreSQL Inner Join

PostgreSQL Inner Join is one of the most important concepts in the database, which allows users to relate the data in multiple tables. Suppose if you want to retrieve data from two tables named table1 and table2. The table2 table has the foreign_key_table1 field that relates to the primary key of the table1 table. To retrieve the data from both tables considered as table1 and table2, we need to use the SELECT statement with an INNER JOIN clause as follows:

Code:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

SELECT
table1.primary_key_table1,
table1.column1,
table2.primary_key_table2,
table2.column2
FROM
table1
INNER JOIN table2 ON table1.primary_key_table1 = table2.foreign_key_table1;

Explanation: To join table1 to table2, We need to specify the column names in the SELECT clause from both of the tables to select data. We need to specify the name of the main table, which is table1 in the FROM clause. We need to specify the name of the table on which the main table will be joining, that is table2 in the INNER JOIN clause. We can add a join condition in addition after the ON keyword like table1.primary_key_table1 = B.foreign_key_table1. For every row in the table1 table, PostgreSQL scans the table2 table to verify if there is any row that satisfies the condition, i.e., table1.primary_key_table1 = table2.foreign_key_table1. If it satisfies the condition; as a result, we will get the combined result of both rows into a single row. The primary key column ( primary_key_table1) and foreign key column ( foreign_key_table1) are typically indexed for performance improvement. Sometimes, table1 and table2 tables have the same column name, so to avoid ambiguity, we have to refer to the column as table_name.column_name.

Syntax

SELECT [* | column_name_list] FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

OR

SELECT [* | column_name_list] FROM table_name1
INNER JOIN table_name2
USING (column_name);

OR

SELECT [* | column_name_list] FROM table_name1,table_name2
WHERE table_name1.column_name=table_name2.column_name;

Types of Inner Joins

Below are the types:

Theta join

Based on the condition that is represented by theta, a theta join allows us to join two tables. Theta joins supports all comparison operators. It returns all rows from the tables participated in joining after the JOIN condition is satisfied.

Code:

SELECT column_name_list
FROM table_name_1
INNER JOIN table_name_2
ON table_name_1.column_name = table_name_2.column_name;

EQUI join

With the help of primary key or foreign key relationships, we can join multiple tables using EQUI join:

Code:

SELECT *
FROM table_name_1
JOIN table_name_2 ON table_name_1.<column_id> = table_name_2.<column_id>;

Natural join

We can improve EQUI join with the help NATURAL keyword. This is the same as EQUI join.

Code:

SELECT *
FROM table_name_1
NATURAL JOIN table_name_2;

How does PostgreSQL Inner Join work?

The Inner Join is used to determine which rows of the tables participated in JOIN needs to be considered to return after verifying the condition. Also, to join the condition, we can use the ON or USING clause.

ON clause

It matches the common columns of the tables participating in JOIN from the boolean expression and evaluates which rows need to be added in joining.

USING clause

It takes a comma-separated list of column names, which is common in both the tables and performed a join on matching each of these pairs of columns.

Examples to Implement PostgreSQL Inner Join

Below are the examples mentioned:

Example #1

To understand the examples of considering the following ‘student’ and ‘Branch’ table structures.

Code:

CREATE TABLE student(
rollno int PRIMARY KEY,
firstname VARCHAR (50) NOT NULL,
lastname VARCHAR (50) NOT NULL,
branch_id int NOT NULL,
result boolean,
joining_date DATE NOT NULL
);
CREATE TABLE Branch(
branch_id int primary key,
branch VARCHAR (50) NOT NULL
);

Example #2

Insert some data in ‘student’ and ‘Branch’ tables after creating the above SQL queries. Let’s consider the following SQL queries to insert data:

Code:

INSERT INTO Branch (branch_id, branch)
values
('1',  'Civil'),
('2', 'Computer'),
('3', 'IT'),
('4',  'Mechanical'),
('5',  'Eletrical');
INSERT INTO student (rollno, firstname, lastname, branch_id, result, joining_date)
values
('101', 'Oliver','Jake', '1', false, '06-01-2020'),
('102', 'Jack','Connor', '1', false, '06-01-2020'),
('103', 'Harry','Callum', '2', false, '06-01-2020'),
('104', 'Jacob','John', '2', false, '06-01-2020'),
('105', 'Thomas','David', '3', false, '06-01-2020');

Code:

select * from student;

Output:

PostgreSQL Inner Join1

Code:

select * from Branch;

Output:

PostgreSQL Inner Join2

Examples to Implement PostgreSQL INNER JOIN with Two Tables

Below are the examples mentioned for two tables:

Example #1

Each student will have a branch assigned. The branch_id field establishes the link between two tables. You can use the INNER JOIN clause to join the student table to the Branch table as follows:

Code:

select * from student inner join Branch ON student.branch_id = Branch.branch_id;

Output:

PostgreSQL Inner Join3

Example #2

You can add the ORDER BY clause [ASC | DESC ] to sort the result set by branch id as follows:

Code:

select * from student inner join Branch ON student.branch_id = Branch.branch_id order by branch.branch_id desc;

Output:

PostgreSQL Inner Join4

Example #3

You can also use a WHERE clause to filter students. The following query returns student data for the student whose branch_id is 1:

Code:

select * from student inner join Branch ON student.branch_id = Branch.branch_id where student.branch_id = '1';

Output:

PostgreSQL Inner Join5

Examples to Implement PostgreSQL Inner Join to join Three Tables

The following section explains the relationship between the three tables:

Example #1

student, Branch, and Teacher. Create a Teacher table with the following SQL queries to understand this example:

Code:

CREATE TABLE Teacher(
teacher_id int primary key,
teacher_name VARCHAR (50) NOT null,
branch_id int
);
INSERT INTO Teacher (teacher_id, teacher_name, branch_id)
values
('11', 'Patrik', '1'),
('12', 'Johan', '1'),
('13', 'Robert','2'),
('14', 'Anne', '2');

Code:

select * from Teacher;

Output:

student table

Example #2

Code:

select * from student
inner join Branch ON student.branch_id = Branch.branch_id
inner join Teacher ON student.branch_id = Teacher.branch_id;

Output:

Branch table

Example #3

Example with Natural join Type

Code:

select * from student
natural join Branch;

Output:

Teacher table

Example #4

Example with EQUI join type:

Code:

select * from student
join Branch ON student.branch_id = Branch.branch_id;

Output:

EQUI join type

Conclusion

We hope you have understood the INNER JOIN properly. This tutorial shows you how to select data from multiple tables by joining one table to other tables using the PostgreSQL INNER JOIN clause. We hope you have learned how to use an INNER JOIN of PostgreSQL joins to query data from multiple tables.

Recommended Articles

This is a guide to PostgreSQL Inner Join. Here we discuss an introduction to PostgreSQL Inner Join, syntax, how does it work and examples. You can also go through our other related articles to learn more –

  1. PostgreSQL Alias
  2. PostgreSQL Subquery
  3. PostgreSQL Full Join
  4. MySQL Self Join
Popular Course in this category
PostgreSQL Course (2 Courses, 1 Project)
  2 Online Courses |  1 Hands-on Project |  7+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more