PostgreSQL Full Outer Join

What is Full Outer Join In SQL? How to use Full Outer Join in SQL?

In the last few tutorials, we have discussed Inner Join, Left Join, and Right Join SQL. Today, we are going to discuss the Full Outer Join.

What is Full Outer Join?

In the Full Outer Join or Full Join, it produces combined data from both tables: Left Table as well as Right Table. It returns all the data in a combined table which shows all the records from table 1 and table 2 where it needs to match a common field in both the table.

If the fields don’t match in any of the tables then, in that case, it will return all the results. The columns which don’t match with the common field, filled by NULL values.

Full Outer Join Syntax

The basic Full Join Syntax is given below for your understanding− Table 1: fulljointable1 Table 2: fulljointable2

SELECT fulljointable1.column1, fulljointable2.column2...
FROM fulljointable1
FULL JOIN fulljointable2
ON fulljointable1.common_field = fulljointable2.common_field;

In line 1, we are selecting columns from table 1 as well as table 2.  In line 2, we are using table 1 with from keyword to join with table 2. In line 3, we have used the SQL keyword “FULL JOIN”, you can also use “FULL OUTER JOIN” to implement Full Outer Join in to get our desired result. In line 4, we match on the common field in both the tables.

How to use Full Outer Join?

We are going to use the same table that we have used in our last tutorial. We’ll show the table data below.

TABLE A: BOOKCUSTOMERS

In this, we have listed customers with there address, salary, and age.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | SAMUAL   |  32 | Austin    |  8000.00 |
|  2 | Johnson  |  25 | Ohio      |  9500.00 |
|  3 | Mathew   |  23 | NEW York  |  7000.00 |
|  4 | Eva      |  25 | Texas     |  6500.00 |
|  5 | April    |  27 | San Marcos|  8500.00 |
+----+----------+-----+-----------+----------+

TABLE B: ORDERS

In this, we have saved a list of orders given by customers.

+-----+---------------------+-----------------+--------+
| OID | DATE                | BOOKCUSTOMER_ID | AMOUNT |
+-----+---------------------+-----------------+--------+
| 102 | 2019-12-08 00:00:00 |           3     |   4000 |
| 100 | 2019-09-08 00:00:00 |           3     |   3500 |
| 101 | 2019-08-20 00:00:00 |           2     |   6560 |
| 103 | 2018-05-20 00:00:00 |           4     |   2060 |
+-----+---------------------+-----------------+--------+

Let’s find out some data with the help of the right outer join.

SQL Query for Full Outer Join

SQL> SELECT  ID, NAME, AMOUNT
   FROM BOOKCUSTOMERS
   FULL JOIN ORDERS
   ON BOOKCUSTOMERS.ID = ORDERS.BOOKCUSTOMERSCUSTOMER_ID;

So let’s get a quick overview of the above syntax that we have written. In line 1, we have selected columns ID and name from table 1 and amount from table 2. After that, we are accessing the table 1 “BOOKCUSTOMERS” and joining with table 2 using the keyword “FULL JOIN”.  In the last line, we are matching the book customer ID with the order ID so that we can find all customers who have bought something or doesn’t buy anything.

Result

In the final result, you can see that all the result from table 1 and table is shown. “NULL” is shown those users who don’t buy anything and no data about them are available in the order table.

+----+----------+--------+
| ID | NAME     | AMOUNT |
+----+----------+--------+
|  1 | SAMUAL   |   NULL |
|  3 | Mathew   |   4000 |
|  3 | Mathew   |   3500 |
|  2 | Johnson  |   6560 |
|  4 | Eva      |   2060 |
|  5 | April    |   NULL |
+----+----------+--------+

Pilot the ObjectRocket platform free for 30 Days

It's easy to get started. Imagine the time you'll save by not worrying about database management. Let's do this!

PILOT FREE FOR 30 DAYS

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.