Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
6  
does this fit as a DBA question? This seems more a coding question to me. –  BlackICE Jan 4 '11 at 14:27
1  
@David your thoughts on Meta –  Sathya Jan 4 '11 at 15:56
    
@David then VtC if you think it's wrong for the site. We can always reopen. –  jcolebrand Jan 6 '11 at 2:32
2  
I think this is a great question, and very appropriate for the site. –  datagod Dec 16 '11 at 14:36
    
this need to be moved out of DBA uneless a noob DBA sleeved up to fine tune the DB performance :P –  AmDB Feb 14 '12 at 8:26

8 Answers 8

up vote 27 down vote accepted
  • 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).

share|improve this answer

Everytime someone ask this question, there is the answer : http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Hope it will help you understand,

share|improve this answer
2  
I've worked with joins for 20 years and those diagrams just seemed confusing to me. –  Hogan Jan 4 '11 at 18:32
3  
I'm with @Hogan -- ven diagrams aren't the best explanation for this .. a grid showing what combinations would be returned would likely be better. –  Joe Jan 5 '11 at 19:19
    
All that shows is that you don't understand a venn diagram. They do relate perfectly to the types of join but bear in mind they are a tool to help understanding for those new to joins. If you already learnt joins with a different concept in mind then it is bound to look unfamiliar to you. That is no problem, but it is also not a good indicator for the usefulness of the diagrams. –  JamesRyan Dec 13 '13 at 16:47

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.

share|improve this answer

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
share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.