Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

communitycreator
database
left join
right join
postgresql

What is FULL OUTER JOIN in PostgreSQL?

Rauf Tabassam

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Introduction to FULL OUTER JOIN

In FULL OUTER JOIN, we join two tables’ values with a common attribute. The non-matching records are joined with the NULL value for both tables.

In FULL OUTER JOIN, the following happens:

  1. First, an inner joinIn inner join, we match two table entries on some matching condition. is performed.
  2. Then, the rest of the entries in both tables are joined with NULL values.
Diagrammatical view of FULL OUTER join

Syntax

The syntax of the FULL OUTER JOIN query is as follows:

SELECT column_name(s) FROM table1
FULL OUTER JOIN table2
ON conditional_expression;
Syntax of FULL OUTER JOIN in PostgreSQL

We can better understand this with an example.

Example

Consider the following tables:

COMPANY

id

name

age

address

salary

join_date

1

Paul

32

London

20000

2001-08-13

2

Rock

23

Texas

25000

2002-03-24

3

Allen

27

Norway

22500

2004-02-02

4

Teddy

31

Houston

27500

2000-04-11

DEPARTMENT

id

dept

emp_id

1

IT

1

2

Accounts

2

3

Marketing

5

SELECT EMP_ID, NAME, DEPT FROM COMPANY
FULL OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
FULL OUTER join query in PostgreSQL

Explanation

The above query will join the values in the id column of the COMPANY table with the values of the emp_id column of the DEPARTMENT table. The rest of the entries in both tables will join with NULL values.

The above FULL OUTER JOIN query will produce the following table.

Result table

emp_id

name

dept

1

Paul

IT

2

Rock

Accounts

5


Marketing


Allen



Teddy


Let’s try the query in action.

Terminal 1
Terminal

RELATED TAGS

communitycreator
database
left join
right join
postgresql

CONTRIBUTOR

Rauf Tabassam
Copyright ©2022 Educative, Inc. All rights reserved

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring