EDUCBA

Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Outer Join

PostgreSQL Outer Join

Priya Pedamkar
Article byPriya Pedamkar

Updated May 9, 2023

PostgreSQL Outer Join

 

 

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

postgreSQL Outer Join 1

  • 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.

right table

  • 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.

PostgreSQL outer join 3

  • 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:

postgreSQL Outer Join 4

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:

postgreSQL Outer Join 5

Example #1: Left Outer Join

Code:

Select * from emp1 LEFT OUTER JOIN emp2 ON emp1.emp_id = emp2.emp_id;

Output:

select from left

Example #2: Right Outer Join

Code:

Select * from emp1 RIGHT OUTER JOIN emp2 ON emp1.emp_id = emp2.emp_id;

Output:

select from right

Example #3: Full Outer Join

Code:

Select * from emp1 FULL OUTER JOIN emp2 ON emp1.emp_id = emp2.emp_id;

Output:

postgreSQL Outer Join 8

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.

  1. PostgreSQL ROLLUP
  2. PostgreSQL Administration
  3. COMMIT PostgreSQL
  4. PostgreSQL OFFSET