In SQL, what’s the difference between an inner and outer join?

 




Joins are used to combine the data from two tables, with the result being a new, temporary table. The temporary table is created based on column(s) that the two tables share, which represent meaningful column(s) of comparison. The goal is to extract meaningful data from the resulting temporary table. Joins are performed based on something called a predicate, which specifies the condition to use in order to perform a join. A join can be either an inner join or an outer join, depending on how one wants the resulting table to look.

It is best to illustrate the differences between inner and outer joins by use of an example. Here we have 2 tables that we will use for our example:

Employee Location
EmpID EmpName
13 Jason
8 Alex
3 Ram
17 Babu
25 Johnson
EmpID EmpLoc
13 San Jose
8 Los Angeles
3 Pune, India
17 Chennai, India
39 Bangalore, India

It’s important to note that the very last row in the Employee table does not exist in the Employee Location table. Also, the very last row in the Employee Location table does not exist in the Employee table. These facts will prove to be significant in the discussion that follows.

Outer Joins

Let’s start the explanation with outer joins. Outer joins can be be further divided into left outer joins, right outer joins, and full outer joins. Here is what the SQL for a left outer join would look like, using the tables above:

select * from employee left outer join location 
on employee.empID = location.empID;

Subscribe to our newsletter for more free interview questions.

In this SQL we are joining on the condition that the employee ID’s match in the rows tables. So, we will be essentially combining 2 tables into 1, based on the condition that the employee ID’s match. Note that we can get rid of the "outer" in left outer join, which will give us the SQL below. This is equivalent to what we have above.

select * from employee left join location 
on employee.empID = location.empID;

A left outer join retains all of the rows of the left table, regardless of whether there is a row that matches on the right table. The SQL above will give us the result set shown below.

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
25 Johnson NULL NULL

The Join Predicate – a geeky term you should know




Earlier we had mentioned something called a join predicate. In the SQL above, the join predicate is "on employee.empID = location.empID". This is the heart of any type of join, because it determines what common column between the 2 tables will be used to "join" the 2 tables. As you can see from the result set, all of the rows from the left table are returned when we do a left outer join. The last row of the Employee table (which contains the "Johson" entry) is displayed in the results even though there is no matching row in the Location table. As you can see, the non-matching columns in the last row are filled with a "NULL". So, we have "NULL" as the entry wherever there is no match.

A right outer join is pretty much the same thing as a left outer join, except that the rows that are retained are from the right table. This is what the SQL looks like:

select * from employee right outer join location 
on employee.empID = location.empID;

// taking out the "outer", this also works:

select * from employee right join location 
on employee.empID = location.empID;

Using the tables presented above, we can show what the result set of a right outer join would look like:

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
NULL NULL 39 Bangalore, India

We can see that the last row returned in the result set contains the row that was in the Location table, but not in the Employee table (the "Bangalore, India" entry). Because there is no matching row in the Employee table that has an employee ID of "39", we have NULL’s in the result set for the Employee columns.

Inner Joins

Now that we’ve gone over outer joins, we can contrast those with the inner join. The difference between an inner join and an outer join is that an inner join will return only the rows that actually match based on the join predicate. Once again, this is best illustrated via an example. Here’s what the SQL for an inner join will look like:

select * from employee inner join location on 
employee.empID = location.empID

This can also be written as:

select * from employee, location
where employee.empID = location.empID
              

Now, here is what the result of running that SQL would look like:

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India

Inner vs Outer Joins

We can see that an inner join will only return rows in which there is a match based on the join predicate. In this case, what that means is anytime the Employee and Location table share an Employee ID, a row will be generated in the results to show the match. Looking at the original tables, one can see that those Employee ID’s that are shared by those tables are displayed in the results. But, with a left or right outer join, the result set will retain all of the rows from either the left or right table.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.

FOLLOW Varoon Sahgal, Author of ProgrammerInterviewon
  • prakash chandra Sahoo

    i believe you can get your hands on tutorials point

  • Athira Sajeev

    Super site

  • thanks thats great … Really appreciate this share

  • Tribeni

    This is one awesome piece of work !
    The way each concept is explained is very concise and concrete. This is the site I strictly refer to before appearing for any interview.

  • hongyegong

    Really clear explanation.

  • Justin Lew

    Does anyone know someone with practice questions for SQL for interviews? Somewhere you can actually practice typing in the code and push a button to check if your answer is correct?

  • Rajendra Kawade

    Very good explanation with proper example.
    This is the best site I have ever visited.
    Thanks for the information.

  • Daniel Edwards

    I just got off of a phone interview for a position as an implementation consultant. This site helped TREMENDOUSLY. Concepts that I was a bit shaky on or forgot were made concrete after reading the descriptions here! I killed the interview, they were really happy with the results and will get a decision from them in a few days. Thank you so much!!

  • swati

    good question… expecting answer about this question from anyone

  • Vaibhav Singh

    SQL FULL OUTER JOIN Keyword. The FULLOUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

  • David Wright

    good explanation, but you didn’t explain the “full” outer join

  • ARUN NARAYANAN

    Simple and best explanation.. Thanks a lot.

  • Ahsan

    This stuff is good very good as compared to my teacher slides.Good work guyz very helpful :) thanks (Y)

  • Peter Moh

    It explains out jion and inner join very clearly

  • Love To Learn

    Can anyone tell me which is the recommended way to write the SQL statements?

    Using:
    select * from employee, location
    where employee.empID = location.empID

    OR

    select * from employee inner join location on
    employee.empID = location.empID

    I am now learning SQL and find that using the WHERE clause makes more sense to me but I am not sure if that is the recommended method.

  • softwaretestingpro.com

    Nice stuff !!

  • Bipin

    Very helpful

  • G.

    Very helpful. Thanks alot

  • Ketan Dharmik

    How join two table have different field in mysql????

  • Suresh

    very helpful thaks

  • mahesh

    very nice buddy.

  • hh

    DUDU? xD

  • Gavin Shao

    The explanation is really clear

  • Bibhuti Anand

    Excellent Explanation of almost all the topic i have searched in this site…
    Great work. thanks.

  • Kevin

    Best explanation ever….thanks so much

  • Sam

    Worked in IT for over 12 years and I really never understood joins that well UNTIL Today. Thanks!

  • Md Farooq

    wow awesome explanation

  • Stella

    best explanation ever !!! new grad here preparing for interview here.

  • kim

    how about joining table with one id only?

  • speakhearseenoevil

    You might find this diagram useful. I have it saved on my desktop for quick reference.

  • sumitperi

    amazing explanation.. my concepts are becoming a lot clear

  • Guest

    Superb…. Thank you

  • sunil

    Again a very good explanation

  • Chetu

    Really good explanation….:)

  • ramesh

    Super Buddy!!. Fantastic explanation. Kudos to the author

  • Pawan Pareek

    Thanks for the post. I got the point very well and cleared my confusion between these.

  • javed

    thankzzzz…Its Really Helpful…

  • PI

    performance point of view, inner is better

  • Priya Chetwani

    i’m new to the mysql joins. thank you so much for explanation

  • sum15

    Very well explained!! Thanks

  • ram

    simple & best explanation

  • Kannan Ramasamy

    very nice for ur explanation

  • mmm

    mn km

  • i would like to know the exact differnce between inner join ,outerjoin and full outer join,can some guide me?
    Thanks..

  • Adi Narayana

    Excellent explanation.

  • ankit

    very helpful

  • NITHIY ANANTH

    THNX BRO…..XELLENT EXPLANATION

  • sruthibenjamin

    very good explanation, thank you.

  • HK

    what happens if I interchange tables during left outer and right outer joins? In that case we can just have one type of outer join.

  • Md Tauqueer Alam

    Saved my lots of time… explained extremely well in an organized and understanding way. Thanx

  • sam

    Good

  • andy

    Thank you so very much. Yes i agree the best explanation i have read thus far

  • cromateu

    thanku for this explanation :)

  • Simon Mohoalali

    Awesome! simple, sweet and straight to the point!

  • Nachi

    Really Good Explanation! Thanks a lot!

  • Mayank Kumar

    Hi I want to know what is the difference between Inner join and Natural join?Please explain with examples.

  • Abhaya

    Does it means like the UNION?
    which means it’ll return all values from both the table.

  • Abhaya

    Yes, we don’t have this concept. we have left outer and right outer join.
    Inner join is only the simplest one and it returns the value based on the common field’s rows only.

  • Rahul Sharma

    best

  • ihateyou

    r8…

  • shamim

    Nice Examples

  • Chandra Prakash

    Best Ever explanation for a LAYMAN

  • So far so good. I hope you will also add the explanation of a full outer join.

  • gcoxusa

    The explanation is excellent, but if I get this question (what
    is the difference between an inner and outer join?) in a phone interview they would not appreciate me launching into this long-winded explanation.

  • sanjay

    it means we don’t have the concept of inner left and right join ???

  • Labeeba

    Simple explanation.Thank you

  • Sanjay

    very nice !!!!

  • Ranjith Babu

    Thnk you so much…

  • Meera

    good

  • Sgt13Echo

    Whats better to use the “inner join” or commas and a where statement

  • Boo

    ya… nice tutorial …… i like it

  • Muhammad Usman

    Good explanation and awesome work….

  • Keshav

    Great !!

  • Reddy

    explanation made very simple manner. Liked this.

  • Edwin Bajwahuka

    yes, it is amazing

  • Zelinskey

    Really amazing explanation. Better than all other sites (almost > 20) which I referred. Read all the comments below and found none that doesn't appreciate you.

  • Nishant

    Cleared concepts in lucid way

  • Prashant

    Really very good and useful explanation….Thanks

  • Bharat Reddy

    really am struggling with joins Concept..
    now, i will roar cause i clearly understood .

  • Ravivarman

    Very nice explanation.!! Thanks very much..!!

  • neethu

    awsome explanation

  • anas

    full outer join gives both common and uncommon result from the tables…

  • Anwesh

    I have gone through many refernce material to understand Outer join, but i never understood…This is by far the best and the simplest explanation possible….Thanks a ton DUDU

  • Vaibhav Tikoo

    The way the explanation is given is just awesome…Thanks for sharing

  • GLKC

    Finally, clear and simple conceptual explanations that are easy to understand! Thank You!

  • Richa

    Thorough Explanation :) Thank u so much for other concepts as well!

  • Fida Husain

    really very good Explanation about join

  • Aamir Sohail

    you are awesome bddy.. post more about hints, execution plan table's data(most confusing) also write some topics for performance tuning…. plzz

  • Gixx

    On the DOT… Dude

  • Hegel

    This is good. Thank you.

  • vicky

    what about full outer join ???

  • Anirudh

    Thanks

  • Vuyani Billy Nyathikazi

    WOW…I LIKE IT

  • apurva

    osum work :)

  • anonymus

    really ur site s so awesome…as u give a simple and neat explanation..i wish u post more in networking and operating systems..

  • rd

    simple and precise explanation

  • Akash

    I love this explanation,,It seems the author got the crux of sql..

  • Shivam Bansal

    This site is awesome…

  • dixitsingla

    Best explanation

Your Email (required)
Your Country