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
1 2 3 4 | 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.
1 2 3 4 5 6 7 8 9 | +----+----------+-----+-----------+----------+ | 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.
1 2 3 4 5 6 7 8 | +-----+---------------------+-----------------+--------+ | 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
1 2 3 4 | 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.
1 2 3 4 5 6 7 8 9 10 | +----+----------+--------+ | 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!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started