### ON Clause

Before moving on to other join types, we need to look at the ON clause. This is useful for putting the JOIN conditions in a separate clause.

Now we can distinguish the JOIN condition from the WHERE clause conditions. But there is also a slight difference in functionality. We will see that in the LEFT JOIN examples.

### USING Clause

USING clause is similar to the ON clause, but it's shorter. If a column is the same name on both tables, we can specify it here.

In fact, this is much like the NATURAL JOIN, so the join column (customer_id) is not repeated twice in the results.

### Left (Outer) Join

A LEFT JOIN is a type of Outer Join. In these queries, if there is no match found from the second table, the record from the first table is still displayed.

Even though Andy has no orders, his record is still being displayed. The values under the columns of the second table are set to NULL.

This is also useful for finding records that do not have relationships. For example, we can search for customers who have not placed any orders.

All we did was to look for NULL values for the order_id.

Also note that the OUTER keyword is optional. You can just use LEFT JOIN instead of LEFT OUTER JOIN.

#### Conditionals

Now let's look at a query with a condition.

So what happened to Andy and Sandy? LEFT JOIN was supposed to return customers with no matching orders. The problem is that the WHERE clause is blocking those results. To get them we can try to include the NULL condition as well.

We got Andy but no Sandy. Still this does not look right. To get what we want, we need to use the ON clause.

Now we got everyone, and all orders above \$15. As I said earlier, the ON clause sometimes has slightly different functionality than the WHERE clause. In an Outer Join like this one, rows are included even if they do not match the ON clause conditions.

### Right (Outer) Join

A RIGHT OUTER JOIN works exactly the same, but the order of the tables are reversed.

This time we have no NULL results because every order has a matching customer record. We can change the order of the tables and get the same results as we did from the LEFT OUTER JOIN.

Now we have those NULL values because the customers table is on the right side of the join.