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

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?

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

StackExchange Database Administrators Q#153, answer score: 32