SQL Join, include rows from table a with no match in table b

Question

SELECT orders.* FROM orders JOIN order_rows 
  ON orders.id = order_rows.order_id 
  WHERE order_rows.quant <> order_rows.quant_fulfilled 
  GROUP BY orders.id 
  ORDER BY orders.id DESC

I need this to include rows that have no corresponding order_row entries (which would be an order that has no items in it yet). It seems like there must be a way to do this by adding to the ON or WHERE clause?

There will only be a couple empty orders at a given time so I would use a separate query if the best answer to this is going to significantly decrease performance. But I was hoping to include them in this query so they are sorted by orders.id along with the rest. Just don't want to double query time just to include the 1-3 orders that have no items.

I am using MySQL. Thanks in advance for any advice.

Answer 1

Simply use LEFT JOIN instead of JOIN. You'll obtain all rows of orders.

SELECT orders.* FROM orders LEFT JOIN order_rows 
  ON orders.id = order_rows.order_id 
  WHERE order_rows.quant IS NULL OR order_rows.quant <> order_rows.quant_fulfilled 
  GROUP BY orders.id 
  ORDER BY orders.id DESC