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:


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