MySQL – master detail query with no child records

[adsense id=”0514458240″ width=”468″ height=”60″]

I have seen this requested quite a bit on various forums where there is a master table (customers) and a child table (orders). We need to identify several pieces of information:

• Customer orders
• Customer and their orders even if they do not have orders
• Customer that do not have orders

Firstly lets have a look at the tables:

CREATE TABLE customers (
  customer_id int auto_increment primary key,
  name varchar(100)
);

CREATE TABLE orders (
  order_id int auto_increment primary key,
  customer_id int,
  transdate datetime,
  product varchar(10),
  quantity int
);

With these tables we now want to list off all orders for each customers:

SELECT name, transdate, product, quantity FROM customers JOIN orders USING (customer_id);

This query will display all customers and order details where records exist in both the customer and orders table. In some cases we may want to have each customer despite no record existing in the orders table. For this we will use a LEFT JOIN as follows:

SELECT name, transdate, product, quantity FROM customers LEFT JOIN orders USING (customer_id);

So far so good. But what happens if we want to display all customers that do not have entries in the orders table. There are several ways in which this can be resolved.

The first method is to use what we already have above and modify it. If we LEFT JOIN the orders table, then any values for the order fields will be returned as NULL’s. However, we need to be careful in selecting the right field to perform our check on and the primary key field must always have a NON NULL value.

SELECT name FROM customers LEFT JOIN orders USING (customer_id) WHERE order_id IS NULL;

Another way of writing the same query is using the EXISTS syntax.

SELECT name FROM customers c WHERE NOT EXISTS (
  SELECT order_id FROM orders o WHERE c.customer_id = o.customer_id
);

[googleplus]