OUTER JOIN


OUTER JOIN will join the all columns from left table and right table based on the condition. If the matching rows missed in other table, the columns will be filled with NULLs of missed table in result table.

Let us say, A and B table are joining. A has the matching row for a specific value in matching columns and B also should have the row with the matching value. Then only the row will appear in result table.

Let us say, A and B table are joining. A has the matching row for a specific value in matching columns. But B has no row with the matching value. Still the row is joined in OUTER JOIN, but the columns of B will be filled up with NULL values in result table.

Let us say, A and B table are joining. A has no matching row for a specific value in matching columns. But B has row with the matching value. Still the row is joined in OUTER JOIN, but the columns of A will be filled up with NULL values in result table.

In other words, it will match all the rows from both tables even though other table has no matching row in it. This is also called as non EQUI JOIN. This is also well known as non FULL OUTER JOIN.

Syntax -


SELECT A-columns-list,
       B-columns-list
  FROM table-A OUTER JOIN table-B
 WHERE condition
    ON joining-column(s)

In the above case, It will retrieve all the matched and unmatched rows also from table-A and table-B. In other words, OUTER JOIN will retrieve all the rows from two tables. OUTER JOIN is divided into two types based on the how tables joined.

  1. LEFT OUTER JOIN
  2. RIGHT OUTER JOIN

LEFT OUTER JOIN -


LEFT OUTER JOIN will retrieves all the rows from the tables which are provided before LEFT OUTER JOIN keyword and only matched rows from the tables provide after the keyword.

Let us say, A and B table are joining. A has the matching row for a specific value in matching columns and B also should have the row with the matching value. Then only the row will appear in result table. LEFT OUTER JOIN explained in detail here .

RIGHT OUTER JOIN -


RIGHT OUTR JOIN will retrieves matched rows from the tables which are provided before RIGHT OUTR JOIN keyword and all rows from the tables provide after the keyword.

Let us say, A and B table are joining. A has the matching row for a specific value in matching columns and B also should have the row with the matching value. Then only the row will appear in result table. RIGHT OUTER JOIN explained in detail here .