PostgreSQL - INNER JOIN
PostgreSQL
In this tutorial we will be learning about INNER JOIN in PostgreSQL.
Generally we have data stored in multiple tables and when we want to retrieve data we have to fetch them from those tables. This is where joining of tables comes into picture.
We use the INNER JOIN to fetch rows that have matching values in both the tables that we are joining.
We use the INNER JOIN to fetch rows that have matching values in the tables that are joined.
The following image represents INNER JOIN of two tables.
In this tutorial we will be using the employee and comments table.
Feel free to check out this tutorial PostgreSQL - CREATE Table and PostgreSQL - SELECT FROM Table on how to create tables and select rows from the tables.
Employee Table
Use the following to insert data into the employee table.
INSERT INTO employee ("employeeId","firstName","lastName",score,"lastModifiedAt","createdAt",birthday,email)
VALUES
('e01','Yusuf','Shakeel',0,'2018-02-04 06:08:10','2021-01-01 01:01:01','1900-01-01','yusuf@example.com'),
('e04','Tin','Tin',7,'2018-02-04 06:08:10','2018-01-01 01:02:03','1900-10-20','tintin@example.com'),
('e05','Bob','Coder',7,'2018-02-04 06:08:10','2018-01-01 01:02:10','1900-08-20','bobcoder@example.com'),
('e02','John','Doe',9,'2018-02-04 06:08:10','2018-01-01 01:01:04','1900-02-03','johndoe@example.com'),
('e03','Jane','Doe',9,'2018-02-04 06:08:10','2018-01-01 01:01:04','1900-05-20','janedoe@example.com');
To fetch the rows in the employee table run the following select query.
SELECT * FROM employee;
employeeId | firstName | lastName | score | lastModifiedAt | createdAt | birthday | email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
e01 | Yusuf | Shakeel | 0 | 2018-02-04 06:08:10 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
e04 | Tin | Tin | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
e05 | Bob | Coder | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
e02 | John | Doe | 9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
e03 | Jane | Doe | 9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(5 rows)
Comments Table
Use the following to insert the rows in the comments table.
INSERT INTO comments
("commentId", "employeeId", "commentBody", "lastModifiedAt", "createdAt")
VALUES
(1, 'e03', 'Awesome', '2018-01-01 02:03:04', '2018-01-01 02:03:04'),
(2, 'e03', 'Hello World', '2018-01-01 02:03:05', '2018-01-01 02:03:05'),
(3, 'e01', 'Happy', '2018-01-01 02:03:06', '2018-01-01 02:03:06'),
(4, 'e04', 'This is my comment.', '2018-01-01 02:03:07', '2018-01-01 02:03:07'),
(5, 'e01', 'Keep coding.', '2018-01-01 02:03:08', '2018-01-01 02:03:08');
To fetch the rows in the comments table run the following select query.
SELECT * FROM comments;
commentId | commentBody | lastModifiedAt | createdAt | employeeId
-----------+---------------------+---------------------+---------------------+------------
1 | Awesome | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 | e03
2 | Hello World | 2018-01-01 02:03:05 | 2018-01-01 02:03:05 | e03
3 | Happy | 2018-01-01 02:03:06 | 2018-01-01 02:03:06 | e01
4 | This is my comment. | 2018-01-01 02:03:07 | 2018-01-01 02:03:07 | e04
5 | Keep coding. | 2018-01-01 02:03:08 | 2018-01-01 02:03:08 | e01
(5 rows)
INNER JOIN syntax
Following the syntax for the inner join between two tables.
SELECT tbl1.column_name, tbl2.column_name
FROM table_1 tbl1
INNER JOIN table_2 tbl2
ON tbl1.column_X = tbl2.column_X;
INNER JOIN employee and comments table
In the following example we want to fetch all the comments and who posted those comments.
So, to achieve this we will INNER JOIN comments and the employee table on the column employeeId.
SELECT e."employeeId", e."firstName", e."lastName", c."commentId", c."commentBody", c."createdAt", c."lastModifiedAt"
FROM employee e
INNER JOIN comments c
ON e."employeeId" = c."employeeId"
ORDER BY c."createdAt";
employeeId | firstName | lastName | commentId | commentBody | createdAt | lastModifiedAt
------------+-----------+----------+-----------+---------------------+---------------------+---------------------
e03 | Jane | Doe | 1 | Awesome | 2018-01-01 02:03:04 | 2018-01-01 02:03:04
e03 | Jane | Doe | 2 | Hello World | 2018-01-01 02:03:05 | 2018-01-01 02:03:05
e01 | Yusuf | Shakeel | 3 | Happy | 2018-01-01 02:03:06 | 2018-01-01 02:03:06
e04 | Tin | Tin | 4 | This is my comment. | 2018-01-01 02:03:07 | 2018-01-01 02:03:07
e01 | Yusuf | Shakeel | 5 | Keep coding. | 2018-01-01 02:03:08 | 2018-01-01 02:03:08
(5 rows)
INNER JOIN multiple tables
To perform inner join of multiple tables we have to add another INNER JOIN clause to the first INNER JOIN clause.
Here is the syntax to INNER JOIN three tables.
SELECT tbl1.column_name, tbl2.column_name, tbl3.column_name
FROM table1 tbl1
INNER JOIN table2 tbl2
ON tbl1.columnX = tbl2.columnX
INNER JOIN table3 tbl3
ON tbl1.columnY = tbl3.columnY;
Lets consider the following three tables.
- customer
- order_detail
- payment_detail
And here is the create and insert queries for the tables.
--- create customer table
CREATE TABLE customer (
"customerId" varchar(20) NOT NULL,
"firstName" varchar(100) NOT NULL,
"lastName" varchar(100) NOT null,
"lastModifiedAt" timestamp NULL,
"createdAt" timestamp NOT null,
CONSTRAINT "customerId_customer_pk" PRIMARY KEY ("customerId")
);
--- create order_detail table
CREATE TABLE order_detail (
"orderId" varchar(20) NOT NULL,
"customerId" varchar(20) NOT NULL,
"orderStatus" varchar(20) NOT NULL,
"lastModifiedAt" timestamp NULL,
"createdAt" timestamp NOT null,
CONSTRAINT "orderId_order_detail_pk" PRIMARY KEY ("orderId")
);
--- create payment_detail table
CREATE TABLE payment_detail (
"paymentId" varchar(20) NOT NULL,
"orderId" varchar(20) NOT NULL,
"customerId" varchar(20) NOT NULL,
"paymentStatus" varchar(20) NOT NULL,
"cent" int NULL DEFAULT 0,
"fraction" int NULL DEFAULT 0,
"currency" varchar(5) NOT NULL,
"lastModifiedAt" timestamp NULL,
"createdAt" timestamp NOT null,
CONSTRAINT "paymentId_payment_detail_pk" PRIMARY KEY ("paymentId")
);
--- create some customers
INSERT INTO customer ("customerId","firstName","lastName","lastModifiedAt","createdAt")
VALUES
('c01','Yusuf','Shakeel','2018-02-04 06:08:10','2018-02-04 06:08:10'),
('c02','John','Doe','2018-02-04 06:08:10','2018-02-04 06:08:10'),
('c03','Jane','Doe','2018-02-04 06:08:10','2018-02-04 06:08:10'),
('c04','Tin','Tin','2018-02-04 06:08:10','2018-02-04 06:08:10'),
('c05','Bob','Coder','2018-02-04 06:08:10','2018-02-04 06:08:10');
--- create some orders
INSERT INTO order_detail ("orderId","customerId","orderStatus","lastModifiedAt","createdAt")
VALUES
('o1','c01','CREATED','2018-02-04 06:08:10','2018-02-04 06:08:10'),
('o2','c02','CREATED','2018-02-04 06:08:13','2018-02-04 06:08:13'),
('o3','c01','CREATED','2018-02-04 06:08:15','2018-02-04 06:08:15');
--- create some payments
INSERT INTO payment_detail ("paymentId","orderId","customerId","paymentStatus","cent","fraction","currency","lastModifiedAt","createdAt")
VALUES
('p1','o1','c01','PAID',1000,100,'INR','2018-02-04 06:08:10','2018-02-04 06:08:10'),
('p2','o2','c02','PAID',40000,100,'INR','2018-02-04 06:08:13','2018-02-04 06:08:13'),
('p3','o3','c01','PAID',10000,100,'INR','2018-02-04 06:08:15','2018-02-04 06:08:15');
Lets INNER JOIN the three tables and fetch the full detail of the customer, order and payment.
SELECT c."customerId", o."orderId", p."paymentId",
c."firstName", c."lastName", o."orderStatus", p."paymentStatus",
p."cent", p."fraction", p."currency",
o."createdAt" AS "orderCreatedAt",
p."createdAt" AS "paymentCreatedAt"
FROM customer c
INNER JOIN order_detail o
ON c."customerId" = o."customerId"
INNER JOIN payment_detail p
ON p."orderId" = o."orderId"
ORDER BY o."createdAt" DESC;
customerId | orderId | paymentId | firstName | lastName | orderStatus | paymentStatus | cent | fraction | currency | orderCreatedAt | paymentCreatedAt
------------+---------+-----------+-----------+----------+-------------+---------------+-------+----------+----------+---------------------+---------------------
c01 | o3 | p3 | Yusuf | Shakeel | CREATED | PAID | 10000 | 100 | INR | 2018-02-04 06:08:15 | 2018-02-04 06:08:15
c02 | o2 | p2 | John | Doe | CREATED | PAID | 40000 | 100 | INR | 2018-02-04 06:08:13 | 2018-02-04 06:08:13
c01 | o1 | p1 | Yusuf | Shakeel | CREATED | PAID | 1000 | 100 | INR | 2018-02-04 06:08:10 | 2018-02-04 06:08:10
(3 rows)