EN
PostgreSQL - Full Outer Join
0 points
In this article, we would like to show you how to do FULL OUTER JOIN in PostgreSQL.
Quick solution:
xxxxxxxxxx1
SELECT * FROM "table1"2
FULL OUTER JOIN "table2" ON "table2"."column_name" = "table1"."column_name"To show how the FULL OUTER JOIN works, we will use the following tables:
Note:
At the end of this article you can find databases preparation SQL queries.
In this example, we will select all information about users and departments.
Query:
xxxxxxxxxx1
SELECT * 2
FROM "users" 3
FULL OUTER JOIN "departments" ON "departments"."id" = "users"."department_id"Output:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE "departments" (2
"id" SERIAL,3
"department_name" VARCHAR(50) NOT NULL,4
"location" VARCHAR(50) NULL,5
PRIMARY KEY ("id")6
);7
8
CREATE TABLE "users" (9
"id" SERIAL,10
"name" VARCHAR(50) NOT NULL,11
"surname" VARCHAR(50) NOT NULL,12
"department_id" INTEGER,13
PRIMARY KEY ("id"),14
FOREIGN KEY ("department_id") REFERENCES "departments" ("id")15
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO "departments"2
("id", "department_name", "location")3
VALUES4
(1, 'Sales', 'New York'),5
(2, 'Finance', NULL),6
(3, 'HR', 'Atlanta'),7
(4, 'Purchase', 'New Orlean'),8
(5, 'Operations', 'Boston');9
10
INSERT INTO "users"11
( "name", "surname", "department_id")12
VALUES13
('John', 'Stewart', 1),14
('Chris', 'Brown', 2),15
('Kate', 'Lewis', 3),16
('Ailisa', 'Gomez', NULL),17
('Gwendolyn', 'James', 2),18
('Simon', 'Collins', 4),19
('Taylor', 'Martin', 2),20
('Andrew', 'Thompson', NULL);