Outer Joins

Most joins return rows based on pairs of records from the two joined tables according to a given relationship. The outer join is different: All the rows from one table are returned, regardless of whether the relationship condition finds a matching row in the second table. When no corresponding record is found, columns that would otherwise contain values from the second table contain NULL.

Outer joins are useful in producing reports when you do not want to exclude a record from the result if it does not have any corresponding data in a joined table.

The following example uses an outer join to produce a report of the customer orders placed in the month of February only:

MariaDB> SELECT c.name, o.order_date
 -> FROM customers c
 -> LEFT OUTER JOIN orders o
 -> ON o.customer_code = c.customer_code
 -> AND o.order_date BETWEEN '2006-02-01' AND '2006-02-28';
+-------------------------+------------+
| name | order_date |
+-------------------------+------------+
| Presidents Incorporated | NULL |
| Science Corporation | 2006-02-02 |
| Science Corporation | 2006-02-05 | 
| Musicians of America | 2006-02-01 |
| Musicians of America | 2006-02-02 |
+-------------------------+------------+
5 rows in set (0.00 sec)

Because Presidents Incorporated placed no orders in the date range specifiedyou will see in the orders table that orders were placed only in Januarythe order date returned is NULL.

LEFT JOIN

The join keyword used in the previous example was LEFT OUTER JOIN, which is often shortened to LEFT JOIN. The LEFT keyword tells MariaDB that the left tablethe one specified first, in the FROM clauseshould return every row with NULL in the right table when no match is found.

You can use RIGHT OUTER JOIN or RIGHT JOIN to reverse the behavior so that the table given after JOIN must return every row.

Exactly one row is returned for a customer record when there is no corresponding order data, whereas the other customers have multiple rows returned according to their actual order history.

Notice that the filter on order_date is given in the ON clause, not the WHERE clause. Because the query returns a NULL order_date value for rows in customers that do have any orders, putting this filter in the WHERE clause would prevent those NULL values from being returned.

Previous Page Next Page