Yogesh Chauhan's Blog


in Postgres on October 24, 2020

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

If there are no matches at all, FULL OUTER JOIN can potentially return very large result-sets because it’s going to include everything from left and right tables!

The OUTER keyword doesn’t make any difference in the query. FULL OUTER JOIN and FULL JOIN are the same.

Basically, it looks like this.


The highlighted part from the image above is our query results when we apply FULL OUTER JOIN between 2 tables. 


SELECT a.column2, b.column2
FROM table a
ON a.column = b.column;

I am using alias as well in the syntax. You can use name of the table instead of alias. Alias makes it better to write the query. Learn more about alias in this post:

Column And Table Alias In Postgres

FULL OUTER JOIN Query Example 2 tables

I am using the database for all examples. It is available on my Github public repo

SELECT c.contact_name, o.order_date 
FROM customers c 
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id

contact_name.       order_date
"Guillermo Fernández"    "1998-05-05"
"Jytte Petersen"    "1998-05-06"
"Michael Holz"    "1998-05-06"
"Laurence Lebihan"    "1998-05-06"
"Paula Wilson"    "1998-05-06"
"Marie Bertrand"    null
"Diego Roel"        null
832 rows

As we can see there were no matches from the right table, so the query result included the data from left table with null. In this case the FULL OUTER join behaves like LEFT join.

What will happen if we apply LEFT join to 3 tables?

INNER JOIN Query Example 3 tables

SELECT c.contact_name, o.order_date, od.product_id
FROM customers c 
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
FULL OUTER JOIN order_details od ON od.order_id = o.order_id

contact_name.    order_date.   count
"Paula Wilson"    "1998-05-06"    73
"Paula Wilson"    "1998-05-06"    75
"Paula Wilson"    "1998-05-06"    77
"Marie Bertrand"        null    null
"Diego Roel"            null    null
2157 rows

As we can see we are getting all the data possible from LEFT tables and for right tables, if there are not any match, it simply returns null. If we want to filter this data more, we can add an aggregate function and add GROUP BY clause.

As we saw in the previous query, in this query the FULL OUTER join behaves like LEFT join as well.

Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
How to Recognize an Array in JavaScript?JavaScriptReview an intentionally vulnerable plugin in WordPressWordPressHow to verify your domain in Google Console with a TXT record?MiscellaneousHow to control file extensions with an .htaccess file?MiscellaneousHow to load a module with configuration in SCSS?SCSSHow to Skip or Exclude a Specific URL or File From .htaccess Rewrite Rule?PHP