Left Joins

How many leads do I have?

Now, let’s go to the opposite question from the previous lesson. How many customers do we have that haven’t placed an order? How many leads do we have? This is more tricky and can’t be carried out only by the use of the orders table. This is because we have only those customers in the orders table that have placed an order. Some other customers inside the customers table haven’t placed an order, so their customers.id doesn’t exist as a value inside the orders.customer_id column.

Let’s try the following query:

mysql> select * from customers left join orders on customers.id = orders.customer_id;
+----+-------------+----------+------+--------------+---------------------+-------------+
| id | name        | identity | id   | order_number | ordered_at          | customer_id |
+----+-------------+----------+------+--------------+---------------------+-------------+
|  4 | John Woo    | JW001    |    2 | ABC001       | 2016-09-12 21:36:56 |           4 |
|  5 | Maria Foo   | MF001    |    3 | ABC002       | 2016-09-12 21:40:20 |           5 |
|  5 | Maria Foo   | MF001    |    4 | ABC003       | 2016-09-12 21:44:34 |           5 |
|  6 | Jim Papas   | JP001    |    5 | ABC004       | 2016-09-12 21:46:38 |           6 |
|  6 | Jim Papas   | JP001    |    6 | ABC005       | 2016-09-12 21:47:41 |           6 |
|  6 | Jim Papas   | JP001    |    7 | ABC006       | 2016-09-12 21:49:31 |           6 |
|  7 | Jessy Romeo | JR001    | NULL | NULL         | NULL                |        NULL |
|  8 | Arya Stark  | AS001    | NULL | NULL         | NULL                |        NULL |
+----+-------------+----------+------+--------------+---------------------+-------------+
8 rows in set (0.00 sec)

This is almost the same query that we issued earlier to get the customers. The only difference is that the join with customers isn’t a simple join, but is instead a left join. The left join means to bring all the customers even if they don’t have corresponding entries inside the orders table. That’s why we see the extra two rows for Jessy Romeo and Arya Stark. We see one row for each of these customers, which only has data for the customers part of the result set. It has NULL values for all the columns that belong to the orders part of the result set.