What is the difference between “INNER JOIN” and “O

Question

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Answer 1

• 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.

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

• 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 versay

Answer 2

Assuming you're joining on columns with no duplicates, which is a very common case:

• 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.

select * from a INNER JOIN b on a.a = b.b;
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.

select * from a LEFT OUTER JOIN b on a.a = b.b;
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.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
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.

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

Answer 3

In simple words:

An inner join retrieve the matched rows only.

Whereas an outer join retrieve the matched rows from one table and all rows in other table ....the result depends on which one you are using:

• Left: Matched rows in the right table and all rows in the left table

• Right: Matched rows in the left table and all rows in the right table or

• Full: All rows in all tables. It doesn't matter if there is a match or not

Answer 4

INNER JOIN requires there is at least a match in comparing the two tables. For example, table A and table B which implies A ٨ B (A intersection B).

LEFT OUTER JOIN and LEFT JOIN are the same. It gives all the records matching in both tables and all possibilities of the left table.

Similarly, RIGHT OUTER JOIN and RIGHT JOIN are the same. It gives all the records matching in both tables and all possibilities of the right table.

FULL JOIN is the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN without duplication.

Answer 5

In Simple Terms,

1.INNER JOIN OR EQUI JOIN : Returns the resultset that matches only the condition in both the tables.

2.OUTER JOIN : Returns the resultset of all the values from both the tables even if there is condition match or not.

3.LEFT JOIN : Returns the resultset of all the values from left table and only rows that match the condition in right table.

4.RIGHT JOIN : Returns the resultset of all the values from right table and only rows that match the condition in left table.

5.FULL JOIN : Full Join and Full outer Join are same.

Answer 6

Inner Join

Retrieve the matched rows only, that is, A intersect B.

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

---

Left Outer Join

Select all records from the first table, and any records in the second table that match the joined keys.

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

---

Full Outer Join

Select all records from the second table, and any records in the first table that match the joined keys.

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

---

References

• Inner and outer joins SQL examples and the Join block

• SQL: JOINS