by BehindJava

What is the difference between "INNER JOIN" and "OUTER JOIN"

Home » interview » What is the difference between "INNER JOIN" and "OUTER JOIN"

In this blog, we are going to learn about the difference between “INNER JOIN” and “OUTER JOIN” in detail.

What are Joins

Data from two tables are combined using joins to create a new, temporary table. Predicates, which define the condition to utilize in order to make a join, are used to perform joins. An inner join returns only the rows that truly match the join predicate, as opposed to an outer join, which returns all rows.

Considering that you’re joining on columns without duplicates, which is a pretty typical scenario:

  • An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
  • An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.
  • Examples:Suppose you have two tables, with a single column each, and data as follows:
A    B
-    -
1    3
2    4
3    5
4    6

Note: that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

  • Returns matching columns from both the tables.
select * from a INNER JOIN b on a.a = b.b;
(or)
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

  • Returns all records from left table and matching records from right table.
select * from a LEFT OUTER JOIN b on a.a = b.b;
(or)
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

  • Returns all the records from Right table and matching records from left table.
select * from a RIGHT OUTER JOIN b on a.a = b.b;
(or)
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn’t have a corresponding datum in B, then the B portion is null, and vice versa.

  • Returns all the records from two tables.
select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5