Inner Join vs. Left Outer Join vs. Right Outer Join vs. Full Outer Join Examples

I write SQL queries (and joins) every once in a while, but never had to deal with such things as “left outer joins” or “full outer joins” and so forth.  I always found this a little confusing and could not remember the differences after reading about them.

Today I had a crazy idea – why not actually TRY using the different queries and see what happens?  Consider these tables:

Table A

id common name
1 a Name 1
2 b Name 2

Table B

id common title
1 b Title 1
2 c Title 2

Here is what I found when I do various joins of table A to table B on a common column:

  • Inner Joins are the same as the regular joins you have been doing for a while.  I replaced my query’s ‘join’ statement with ‘inner join’ and got the same result (as well as reading that these were the same).  Note that this means that any rows from either table which do not intersect are not part of the result.  The remaining joins address this.

    The query: select * from table_a inner join table_b where table_a.common = table_b.common;

  • Inner Join A and B

    a.id a.common a.name b.id b.common b.title
    2 b Name 2 1 b Title 1
  • Left Outer Joins are the same as an Inner Join of A and B, PLUS all the rows from A that did not match a row from B, with all the fields from the B side set to NULL for those rows.  Think of A as being on the left side.

    The query: select * from table_a left join table_b on table_a.common = table_b.common;

  • Left Outer Join A and B

    a.id a.common a.name b.id b.common b.title
    1 a Name 1 NULL NULL NULL
    2 b Name 2 1 b Title 1
  • Right Outer Joins are the same as an Inner Join of A and B, PLUS all the rows from B that did not match a row from A, with all the fields from the A side set to NULL for those rows.  Think of B as being on the right side.

    The query: select * from table_a right join table_b on table_a.common = table_b.common;

  • Right Outer Join A and B

    a.id a.common a.name b.id b.common b.title
    2 b Name 2 1 b Title 1
    NULL NULL NULL 2 c Title 2
  • MySQL does not support Full Outer Joins natively (there are some work-arounds).  However, it is roughly like the union of Left Outer Joins and Right Outer Joins according to what I have read.  See http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/
  • Full Outer Join A and B

    a.id a.common a.name b.id b.common b.title
    1 a Name 1 NULL NULL NULL
    2 b Name 2 1 b Title 1
    NULL NULL NULL 2 c Title 2
About these ads

2 thoughts on “Inner Join vs. Left Outer Join vs. Right Outer Join vs. Full Outer Join Examples

  1. Thanks for a concise explanation outlining the differences between the different types of joins. I feel like I have a much better grasp of each one and when it would make sense to use them.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s