First understand the basic difference between INNER Join and OUTER Join.

In INNER Join it basically selects only those rows which are available in both tables and OUTER Join basically selects all rows (records) from both tables.

OUTER Join

Outer joins are joins that return matched values and unmatched values from either or both tables.

3 type of outer joins –

  1. Left outer join
  2. Right outer join
  3. Full outer join

We consider two tables –

According to the above image example, we have two tables called – ‘employee’ & ‘empinfo’.

We will perform a different set of left, right and full outer join on these tables.

Queries with right outer join operations are converted to equivalent queries containing only left join operations.

In the general case, the conversion is performed such that this right outer joins:

(T1, …) RIGHT JOIN (T2, …) ON P(T1, …, T2, …)

Becomes this equivalent left outer join:

 (T2, …) LEFT JOIN (T1, …) ON P(T1, …, T2, …)

All inner join expressions of the form T1 INNER JOIN T2 ON P(T1,T2) are replaced by the list T1,T2, P(T1,T2) being joined as a conjunct to the WHERE condition (or to the join condition of the embedding join, if there is any).

————————————————————————————-

A. Left Outer Join – The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

Syntax

SELECT attributes_name  FROM  table1  LEFT JOIN  table2  ON condition;

We consider two tables –   ‘employee’ & ‘empinfo’

Let’s implement this …

set 1:

set 2:

B. Right Outer Join – The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

Syntax

SELECT attribute_names FROM table1 RIGHT JOIN table2 WHERE condition;

We consider two tables –   ‘employee’ & ‘empinfo’

Let’s implement this …

set 1:

set 2:

C. Full Outer Join

In MySQL, we can’t do Full Outer Joins. For doing, full outer join we need to use ‘UNION’ or ‘UNION ALL’ operator. We will understand about ‘UNION’ operators in further articles.

Syntax –

SELECT attribute_names FROM table1 LEFT JOIN table2 WHERE condition UNION or, UNION ALL SELECT attribute_names FROM table1 RIGHT JOIN table2 WHERE condition;

We consider two tables – ‘employee’ & ‘empinfo’

Let’s implement this…

set 1:

set 2: