SQL RIGHT OUTER JOIN EXAMPLE

SQL RIGHT OUTER JOIN is used to fetch records from more than one table when all the records from one table is required and only matching records from other table. For all the non-matching records the columns value will be NULL from other table. SQL RIGHT OUTER JOIN can be useful however is not that widely used in SQL environment. It is because most of the time, LEFT OUTER JOIN is preferred as in several complex JOINS, it is easy to understand and debug. Let us see an example of RIGHT OUTER JOIN:

SQL OUTER JOIN - Table1
SQL OUTER JOIN – Table1

 

SELECT CUST_ID,CUST_NAME, CUST_CITY, CUST_OPERATOR FROM CUSTOMERS;

 

SQL OUTER JOIN - Table2
SQL OUTER JOIN – Table2

 

 

SELECT ZONE_ID, CITY, STATE FROM OPRT_ZONE;

 

We need to fetch all the STATE along with the CUSTOMERS detail. There can be case where few STATES may not be having any CUSTOMER, still we need that information. In such cases , we use OUTER JOINS. In RIGHT OUTER JOIN, table at the right side i.e. table which is after the RIGHT OUTER JOIN keyword becomes driving table. Hence, all the records from this table will come in result set. The table which is before RIGHT OUTER JOIN keyword will be non- driving table and only matching records will be present in result set.

SQL RIGHT OUTER JOIN - EXAMPLE
SQL RIGHT OUTER JOIN – EXAMPLE

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

To fetch only those STATES where we don’t have any CUSTOMER details, we can add a condition in above SQL query as:

WHERE CUST_ID IS NULL

Leave a Reply

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