What is the difference between an INNER JOIN and an OUTER JOIN ?

Question

I am new to SQL and wanted to know what is the difference between those two JOIN types?

SELECT * 
FROM user u
INNER JOIN telephone t ON t.user_id = u.id

SELECT * 
FROM user u
LEFT OUTER JOIN telephone t ON t.user_id = u.id

When should I use one or the other?

Answer 1

• An inner join will only select records where the joined keys are in both specified tables.
• A left outer join will select all records from the first table, and any records in the second table that match the joined keys.
• A right outer join will select all records from the second table, and any records in the first table that match the joined keys.

In your first example, you will only return a list of users and telephone numbers if at least one telephone record exists for the user.

In your second example, you will return a list of all users, plus any telephone records if they are available (if they aren't available, you'll get NULL for the telephone values).

Answer 2

An inner join returns rows that can be combined based on the join criteria.
An outer join returns these and all rows...
   ...from the first table for a left join
   ...from the second table for a right join
   ...from both tables for a full join

Choosing when to use one or the other is a matter of determining what data you need. For your example if you need only records that have user_ids from telephone that match ids in user then use the inner join. If you also want to include rows from user that have no matching telephone entry, then the left join would be appropriate.

For more information see this question on StackOverflow.

Answer 3

If you have 2 tables like below:

Table1 :   A1    B1          Table2  :    B2     C2 
           -     -                        -      -
           1     2                        1      1
           2     4                        2      4
           3     5                        5      2

If you use Inner Join you get:

 A1     B1     B2      C2  
 -      -      -       -
 1      2      2       4
 3      5      5       2

If you use Full Outer Join you get:

 A1     B1     B2      C2  
 -      -      -       -
 1      2      2       4
 3      5      5       2
 2      4    NULL     NULL
NULL   NULL    1       1

If you use Left Outer Join you get:

 A1     B1     B2      C2  
 -      -      -       -
 1      2      2       4
 3      5      5       2
 2      4    NULL     NULL

Answer 4

Outer join is expressly designed to produce nulls in its result and should therefore be avoided, in general. Relationally speaking, it's a kind of shotgun marriage: It forces tables into a kind of union—yes, I do mean union, not join—even when the tables in question fail to conform to the usual requirements for union. It does this, in effect, by padding one or both of the tables with nulls before doing the union, thereby making them conform to those usual requirements after all. But there's no reason why that padding shouldn't be done with proper values instead of nulls, as in this example:

SELECT SNO , PNO 
FROM   SP 
UNION  
SELECT SNO , 'nil' AS PNO 
FROM   S 
WHERE  SNO NOT IN ( SELECT SNO FROM SP )

Alternatively, the same result could be obtained by using the SQL outer join operator in conjunction with COALESCE, as here:

SELECT SNO , COALESCE ( PNO , 'nil' ) AS PNO 
FROM ( S NATURAL LEFT OUTER JOIN SP ) AS TEMP

A Remark on Outer Join (4.6) in "SQL and Relational Theory: How to Write Accurate SQL Code" by C.J. Date

Answer 5

An inner join is a join where the only results displayed are results where the keys are in both tables. An outer join will display the results for all keys in one tables, a left join from the first and a right join from the second. For example:

Let's say table1 has the following primary key and data pairs: (1, a), (2, b), (3, c)

Let's also say that table2 has the following primary key and data pairs: (1, fun), (3, can), (4, happen)

So an inner join of table1 to table2 on the primary keys would yield the following resulting triplets (with the common primary key first, the first table's second item second and the second table's second item third): (1, a, fun), (3, c, can)

A left outer join of table1 to table2 on the primary keys would yield the following resulting triplets (same format as above): (1, a, fun), (2, b, NULL), (3, c, can)

A right outer join of table1 to table2 on the primary keys would yield the following resulting triplets (same format as above): (1, a, fun), (3, c, can), (4, NULL, happen)

I hope this explains the concept decently well.

Answer 6

Let me try to describe it a bit more intuitive.

The inner join shows the users, which have one or more telephone(s) together with their telephone number(s).

The left outer join additionally list those 'users' which have no telephone.

Answer 7

Since you have asked when to use what, here is a scenario with queries - select which to use depending on the requirement.

Data:

Table Users has 10 records. Table Phoneno has 6 records (with a 1:1 relation, meaning that an entry in PhoneNo will reference only one entry in Users, and only one entry in PhoneNo can reference a given entry in Users).

Requirement 1: Show all users with their phone numbers. Ignore users without phone number.

Query:

SELECT u.uid, u.name, p.phonno 
  FROM user u 
INNER JOIN phones p ON p.uid = u.uid

Result: shows 6 users who has phone number

Requirement 2: Show all users with their phone number. If user does not have a phone display 'N/A' (not available)

Query:

SELECT u.uid, u.name, ifnull(p.phonno,'N/A') 
  FROM user u 
LEFT OUTER JOIN phones p ON p.uid = u.uid

Result:

Shows all 10 records

Note: ifnull is a MySql syntax to transform the null value. I used this function to make the db engine show 'N/A' when phonno is null. Look for appropriate function if you are using some other DBMS. In SQL Server you have to use the CASE statement.

I hope this helps.