Search code examples
mysqlsqljoininner-join

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

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

How do I include rows that have no corresponding order_row entries (which would be an order that has no items in it yet)?

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. I don't want to double query time just to include the 1-3 orders that have no items.

Solution

  • 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
 AND order_rows.quant <> order_rows.quant_fulfilled 
GROUP BY orders.id 
ORDER BY orders.id DESC