Updated May 9, 2023
Introduction to PostgreSQL Outer Join
PostgreSQL outer join is divided into three types, i.e., left outer join, full outer join, and right outer join. Outer join is very important. PostgreSQL left outer join retrieves all rows from the left table and all matching rows from the right table; if there is no match in both tables, the right tables have null values. PostgreSQL right join retrieves all rows from the right table and all matching rows from the left table; if there is no match in both tables, the left tables have null values.
It is divided into three types.
- Left outer join
- Right outer join
- Full outer join
Syntax:
1. Left outer join
Select column1, …, columnN from table_name1 LEFT OUTER JOIN table_name2 on table_name1.column = table_name2.column;SELECT (*) FROM table_name1 LEFT OUTER JOIN table_name2 on table_name1.column = table_name2.column;Below is the parameter description of the above syntax.
- Column1 to columnN: Column used in left outer join to retrieve data from tables.
- Table name: Table name used in the left outer join to retrieve data from a table.
- Left outer join: Left outer join is used to join two tables.
- Asterisk (*): Asterisk (*) defines all table column to retrieve data from a table.
2. Right Outer Join
Select column1, …, ColumnN from table_name1 RIGHT OUTER JOIN table_name2 on table_name1.column = table_name2.column;SELECT (*) FROM table_name1 RIGHT OUTER JOIN table_name2 on table_name1.column = table_name2.column;Below is the parameter description of the above syntax.
- Column1 to columnN: Column used in right outer join to retrieve data from tables.
- Table name: Table name used in the right outer join to retrieve data from a table.
- Right outer join: Right outer join is used to join two tables.
- Asterisk (*): Asterisk (*) defines all table column to retrieve data from a table.
3. Full Outer Join
Select column1, …, columnN from table_name1 FULL OUTER JOIN table_name2 on table_name1.column = table_name2.column;SELECT (*) FROM table_name1 FULL OUTER JOIN table_name2 on table_name1.column = table_name2.column;Below is the parameter description of the above syntax.
- Column1 to columnN: Column used in full outer join to retrieve data from tables.
- Table name: Table name used in full outer join to retrieve data from a table.
- Full outer join: Full outer join is used to join two tables.
- Asterisk (*): Asterisk (*) defines all table column to retrieve data from a table.
How PostgreSQL Outer Join Works?
- While joining the table using a left outer join, PostgreSQL first does a normal join, and then it starts scanning from the left table.
- It retrieves all rows from the left table and all matching rows from the right table. If there is no match in both tables, the right tables have null values.
Below is the image representation of the left outer join as follows.
- While joining the table using the right outer join in PostgreSQL, it first does a normal join, and then it starts scanning from the right table.
- It retrieves all rows from the right table and all matching rows from the left table. If there is no match in both tables, the left tables have null values.
Below is the image representation of the right outer join.
- The PostgreSQL full outer join returns all rows from the left and right tables. If the full outer join condition is unsatisfied, null values are returned.
- While joining a table using FULL OUTER JOIN first, it will join using an inner join. We can combine the left and right join to create a full outer join.
Below is the image representation of the full outer join.
- We can join a single table with multiple names as an alias. It will retrieve data according to the condition.
Examples of PostgreSQL Outer Join
Given below are examples. Here we have used Emp1 and Emp2 tables.
Table1: Emp1:
Code:
CREATE TABLE Emp1 ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL, PRIMARY KEY(emp_id));INSERT INTO Emp1(emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (1, 'ABC', 'Pune', '1234567890', 20000, '01-01-2020');
INSERT INTO Emp1(emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (2, 'PQR', 'Pune', '1234567890', 20000, '01-01-220');
INSERT INTO Emp1(emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (3, 'XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');select * from Emp1;Output:
Table2: Emp2:
Code:
CREATE TABLE Emp2 ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL, PRIMARY KEY(emp_id));INSERT INTO Emp2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (1, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');
INSERT INTO Emp2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (2, 'XYZ', 'Mumbai', '1234567890', 35000, '2-01-2020');
INSERT INTO Emp2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (3, 'BBS', 'Mumbai', '1234567890', 45000, '02-01-2020');select * from Emp2;Output:
Example #1: Left Outer Join
Code:
Select * from emp1 LEFT OUTER JOIN emp2 ON emp1.emp_id = emp2.emp_id;Output:
Example #2: Right Outer Join
Code:
Select * from emp1 RIGHT OUTER JOIN emp2 ON emp1.emp_id = emp2.emp_id;Output:
Example #3: Full Outer Join
Code:
Select * from emp1 FULL OUTER JOIN emp2 ON emp1.emp_id = emp2.emp_id;Output:
Conclusion
PostgreSQL outer join is most important in PostgreSQL.The join operation in SQL is mainly divided into three types: left outer join, right outer join, and full outer join. Using it, we are possible to combine the select and join statements into one statement.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL Outer Join” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
