SQL OUTER JOIN

In the lesson about SQL join we matched clients with salespersons. There are some cases where there is no matching:

  • a salesperson is not assigned to any client.
  • a client has no salesperson assigned to him.


With the inner join, the query result was not showing such clients and salesperson. To show them you must use one of the following joins:

  • left outer join.
  • right outer join.
  • full outer join.

Before explaining these 3 joins, note that:

  • we will call the first table appearing in the query (just after the keyword from) the left table.
  • we will call the second appearing in the query (just after the keyword join) the right table.

Remember that you right in English from left to right: so the first table is on the left, the second on the right.

Left outer join:

With the left outer join:

  • you show all clients (left table). Even those not assigned to any salespersons.
  • you do not show salesperson without any client.

Syntax:

select cl.first_name as clientfirstname,
cl.last_name as clientlastname,
sa.first_name as salesfirstname,
sa.last_name as saleslastname
from clients as cl
left outer join salespersons as sa
on cl.salesperson_id = sa.id

 

right outer join:

With the right outer join:

  • you show all salesperson (right table). Even those without any client.
  • you do not show clients not assigned to any salesperson.

Syntax:

select cl.first_name as clientfirstname,
cl.last_name as clientlastname,
sa.first_name as salesfirstname,
sa.last_name as saleslastname
from clients as cl
right outer join salespersons as sa
on cl.salesperson_id = sa.id

 

Full outer join:

With the full outer join:

  • you show all clients (left table). Even those not assigned to any client.
  • you show all salesperson (right table). Even those without any client.

Important note: full outer join is not supported on mySQL database. If you are using such a database, use the union keyword as explained in the next chapter “Full outer join is not recognized in mySQL, what can I do?”

Syntax:

select cl.first_name as clientfirstname,
cl.last_name as clientlastname,
sa.first_name as salesfirstname,
sa.last_name as saleslastname
from clients as cl
full outer join salespersons as sa
on cl.salesperson_id = sa.id

 

Full outer join is not recognized in mySQL, what can I do?

mySQL databases do not support full outer joins. In order to get the same results as with an outer join query, use the union keyword.

When using the union keyword between two queries, you show the rows resulting from the second query just under the rows resulting from the first query. If your first query is a right outer join and the second query is a left outer join, the result of the union will be the same as a full outer join.

The syntax to replace the full outer join would be:

select cl.first_name as clientfirstname,
cl.last_name as clientlastname,
sa.first_name as salesfirstname,
sa.last_name as saleslastname
from clients as cl
right outer join join salespersons as sa
on cl.salesperson_id = sa.id
union
select cl.first_name as clientfirstname,
cl.last_name as clientlastname,
sa.first_name as salesfirstname,
sa.last_name as saleslastname
from clients as cl
left outer join join salespersons as sa
on cl.salesperson_id = sa.id

 

To test your understanding of this lesson, take the SQL Outer Join Quiz. You can also go to the next chapter about SQL group by
 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

5 questions on “SQL OUTER JOIN

  1. Very nice content.

    Can you please let me know the reason why the Aamir Rao came in as the first record for the result of right outer join and full outer join?

    • Excellent question Harsha ! For left outer join, the order of rows is the same as if you were executing “select * from clients” (left table).
      For right outer join, the order of rows is the same as if you were executing “select * from clients” (right table). For full outer join, I need to reinstall MS SQL server on my PC to double check, but notice that both left hand side and right hand side of the results are in the same order as the left and right table respectively. Aamir Rao is the first row on the top because it is over the row Aarif Verma which corresponds to the row appearing on top of the left table (Barun Rao).


Author:
Last udpated: September 10, 2017
Created: August 13, 2014