$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

Keeping unmatched rows with LEFT JOIN #

INNER JOIN only keeps rows that have a match in both tables. That's a problem if you want to know about rows in one table that have no match in the other, for example, customers who have never placed an order.

In our sample database, every customer except one has placed at least one order. If we INNER JOIN customers with orders, that one customer disappears from the result entirely, because there's no matching orders row to pair them with.

LEFT JOIN fixes this. It keeps every row from the left table (the one listed first), whether or not it has a match on the right. When there's no match, the columns from the right table are filled in with NULL.

SELECT
    columns
FROM
    table_1
LEFT JOIN
    table_2
ON
    table_1.column = table_2.column;
  • table_1 is the "left" table: all of its rows appear in the result, matched or not.
  • table_2 is the "right" table: its columns show up as NULL for any table_1 row that has no match.

Example #

SELECT
    c.first_name,
    o.status
FROM
    customers c
LEFT JOIN
    orders o
ON
    c.id = o.customer_id;

Result:

+------------+-----------+
| first_name |  status   |
+------------+-----------+
|   Megan    |    NULL   |
|  Jonathan  |  Shipped  |
|  Jonathan  | Delivered |
|    .....   |   .....   |

Megan (customer id 1) has never placed an order, so she still shows up once in the result, with status as NULL. Every other customer shows up once per order they've placed, just like with INNER JOIN.

Exercise #

Write a query using LEFT JOIN that returns every customer's first_name, along with the id of their order (aliased order_id), so that customers with no orders still appear in the result, with order_id as NULL.

  • The result has a first_name column and an order_id column.
  • The customer with no orders appears exactly once, with order_id equal to NULL.
  • The total number of rows matches a LEFT JOIN of customers and orders.
Solution 
SELECT
    c.first_name,
    o.id AS order_id
FROM
    customers c
LEFT JOIN
    orders o
ON
    c.id = o.customer_id;
Output will be displayed here