SQL LEFT 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 LEFT OUTER JOIN is very useful and is widely used in any SQL environment. Let us see an example of LEFT OUTER JOIN:
SELECT CUST_ID,CUST_NAME, CUST_CITY, CUST_OPERATOR FROM CUSTOMERS;
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 LEFT OUTER JOIN, table at the left side i.e. table which is before the LEFT OUTER JOIN keyword becomes driving table. Hence, all the records from this table will come in result set. The table which is after LEFT OUTER JOIN keyword will be non- driving table and only matching records will be present in result set.
SELECT OZON.STATE, OZON.CITY, CUST.CUST_ID, CUST.CUST_NAME, CUST.CUST_OPERATOR FROM OPRT_ZONE OZON LEFT OUTER JOIN CUSTOMERS CUST 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