SQL FULL OUTER JOIN Example

SQL Full OUTER JOIN is used to retrieve all the records from both the tables participating in join irrespective of the fact that any match exist in the other table or not. This means that FULL OUTER JOIN will return all the rows from the joining tables. So FULL OUTER JOIN returns matching rows as well as all the rows which are not matching from both the tables. In actual Datawarehouse environment we tend to avoid using FULL OUTER JOIN as it may give us some unexpected results. The general SQL query for FULL OUTER JOIN can be :

SELECT tb1.column1, tb1.column2 , tb2.column1 FROM table1 tb1 FULL OUTER JOIN table2 tb2 ON tb1.column1 = tb2.column1;

In this case it will result in retrieving all the rows from both the tables tb1 and tb2. In our example, say we need all the details about CUSTOMERS as well as OPERATOR ZONE. In such case, we may use FULL OUTER JOIN SQL query:

SQL FULL OUTER JOIN EXAMPLE
SQL FULL OUTER JOIN EXAMPLE

SELECT OZON.STATE, OZON.CITY, CUST.CUST_ID, CUST.CUST_NAME, CUST.CUST_OPERATOR FROM OPRT_ZONE OZON FULL OUTER JOIN CUSTOMERS CUST ON CUST.CUST_CITY = OZON.CITY;

Leave a Reply

Your email address will not be published. Required fields are marked *