Home sql What are the differences between INNER JOIN and OUTER JOIN?

# What are the differences between INNER JOIN and OUTER JOIN?

Author

Date

Category

• What’s the difference between `INNER JOIN `and `OUTER JOIN `?

• What do `LEFT JOIN `, `RIGHT JOIN `and `FULL JOIN `mean?

• Translation of the question “Difference between INNER and OUTER joins @ cdv .

You can also use this diagram to understand table joins: Translation of the answer “Difference between INNER and OUTER joins” @ Teoman Shipahi

Suppose you want to do a column join without duplicates, which is quite common:

• Inner join of A and B: A intersects B, i.e. the interior of the intersection Venn diagrams .

• Outer join A and B: A connects to B, i.e. the outer part of a connection in a Venn diagram.

Examples

Suppose you have two tables. Each consists of one column, with the following values:

``````A B
- -
13
2 4
3 5
4 6
``````

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

Internal join

An inner join using one of the equivalent queries gives the intersection of two tables, that is, two rows common to each of them.

``````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

The result of the left outer join is all rows of table A plus all rows of table B that match the rows of table A.

``````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

The result of the right outer join is all rows of table B plus all rows of table A that match the rows of table B.

``````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

The result of a full outer join is a join between tables A and B, i.e. all rows of A and all rows of B. If any element of table A does not have a match in table B, that part of B is empty, 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
``````

Translation of the answer “Difference between INNER and OUTER joins @ Mark Harrison .

INNER-inner join (intersection of sets) -selects common from sets

LEFT JOIN-left outer join-takes everything on the left and everything on the right, for which there is a match in the left set

What do the differences mean, and also why such names, for all types of table joins, can be clearly seen in the following graph: • `[INNER] JOIN `– internal
• `(LEFT | RIGHT) [OUTER] JOIN `– outer, or right, or left
• `FULL [OUTER] JOIN `– external, right and left, ie full

INNER JOIN Returns records that match in both tables
OUTER LEFT JOIN Returns all records from the left table + records that match from the right
OUTER RIGHT JOIN Returns all records from the right table + records that match from the left
OUTER FULL JOIN Returns all records from both tables

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.