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:
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:
Code:
select * from Branch;
Output:
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:
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:
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:
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:
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:
Example #3
Example with Natural join Type
Code:
select * from student
natural join Branch;
Output:
Example #4
Example with EQUI join type:
Code:
select * from student
join Branch ON student.branch_id = Branch.branch_id;
Output:
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 –
