Introduction to PostgreSQL Outer Join
PostgreSQL outer join is mainly divided into three types, i.e. left outer join, full outer join and right outer join. Outer join is very important in PostgreSQL. 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 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 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 is as follows.
- While joining the table using the right outer join in PostgreSQL, first does 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.
- PostgreSQL full outer join returns all rows from the left table as well as a right table. It will put null when the full outer join condition was not satisfied.
- While joining a table using FULL OUTER JOIN first, it will join using an inner join. The combination of left and right join is known as 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 the examples. Here we have used Emp1 and Emp2 table.
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. It is mainly divided into three type’s left outer join, right outer join and full outer join. Using it, we are possible to combine the select and join statement into one statement.
Recommended Articles
This is a guide to PostgreSQL Outer Join. Here we discuss the Introduction, how PostgreSQL outer join works? And examples. You may also have a look at the following articles to learn more –
