Outer Joins

Depending on the amount of experience you have with databases in general it's likely that as an Oracle user you would use the Oracle sytle join syntax. From version 9i Oracle has supported the ANSI join syntax but many users and many examples of Oracle SQL on the web still use the Oracle syntax.

Essentially the Oracle syntax uses the where clause to specifiy the join condition simply by comparing a column in one table with the other.

SQL> select e.emp_id, d.description from emp e, dept d
  2  where e.dept_id = d.dept_id;

    EMP_ID DESCRIPTION
---------- ------------------------------
         1 HR
         2 HR
         3 IT
         4 ADMIN

The good news is that this also works in MySQL. MySQL fully supports ANSI join conditions but it also allows (in part) the joining of tables via the where clause.

mysql> select e.emp_name, d.description from emps e, dept d
    -> where e.dept_id = d.dept_id;
+----------+-------------+
| emp_name | description |
+----------+-------------+
| Paul     | IT          |
| John     | HR          |
| Alan     | IT          |
+----------+-------------+
3 rows in set (0.02 sec)

The bad news is that you can't use the Oracle outer join syntax. In a normal join, know as an inner join only rows where the join matches exactly are returned. This means that in our example above if an employee record does not have a dept_id (or at least one which isn't in the dept table) the record will not be displayed. If we want to display all the records in a table regardless of the fact there may not be a match we can use an outer join.

In Oracle to use an outer join we can simply use (+) against the table we are joining against, so for example.

SQL> select e.emp_id, d.description from emp e, dept d
  2  where e.dept_id = d.dept_id(+);

    EMP_ID DESCRIPTION
---------- ------------------------------
         1 HR
         2 HR
         3 IT
         4 ADMIN
         5

We can now see a new record that wasn't shown in the first select. In Oracle we can simply move the (+) to either column the join is used against to specify which table to perform the outer join on.

SQL> select e.emp_id, d.description from emp e, dept d
  2   where e.dept_id(+) = d.dept_id;

    EMP_ID DESCRIPTION
---------- ------------------------------
         1 HR
         2 HR
         3 IT
         4 ADMIN
           PR

As mentioned MySQL doesn't support (+) for outer joins. You therefore need to use the ANSI join syntax. We won't go into the full syntax here, but to use an outer join you need to use the left outer join keywords between the table definitions. The biggest difference is that the join condition becomes part of the from clause.

mysql> select e.emp_name, d.description 
       from emps e left outer join dept d 
       on e.dept_id = d.dept_id;

+----------+-------------+
| emp_name | description |
+----------+-------------+
| Barry    | NULL        |
| Paul     | IT          |
| John     | HR          |
| Alan     | IT          |
+----------+-------------+
4 rows in set (0.11 sec)

Using the ANSI standard you can change which table is treated as the outer table by changing the LEFT keyword to RIGHT.

select e.emp_name, d.description 
       from emps e right outer join dept d 
       on e.dept_id = d.dept_id;

+----------+-------------+
| emp_name | description |
+----------+-------------+
| Paul     | IT          |
| Alan     | IT          |
| John     | HR          |
+----------+-------------+
3 rows in set (0.00 sec)