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 |

...