Finding orphaned records in MySQL using LEFT JOIN

Question

I'm trying to find distinct orphaned menu_item records using LEFT JOINS then checking for NULL values.

The query below is returning results that aren't actually orphans. mitem_id is a primary key for the menu_item table and a foreign key for the price_info table. pinfo_id is the primary key for the price_info table and a foreign key orderdetails table.

SELECT DISTINCT mi.mitem_id, descr 
FROM menu_item mi 
    LEFT JOIN price_info pi ON pi.menu_item = mi.mitem_id 
    LEFT JOIN orderdetails od ON od.price_info = pi.pinfo_id 
WHERE od.detail_id IS NULL;

One of the records returned looks like this:

+----------+------------------+
| mitem_id | descr            |
+----------+------------------+
|      926 | Alameda Pale Ale |
+----------+------------------+

How I know it's not an orphan is because when I run the query:

SELECT od.detail_id 
FROM orderdetails od 
   INNER JOIN price_info pi ON pi.pinfo_id = od.price_info 
    INNER JOIN menu_item mi ON mi.mitem_id = pi.menu_item 
WHERE mi.mitem_id = 926;

It returns a set of records:

+-----------+
| detail_id |
+-----------+
|    142830 |
|    142876 |
|    143394 |
|    143610 |

Why is it returning results that aren't orphaned?

Answer 1

Kane was correct in that records were being returned that were not null. The many to many relationship between menu_item and price_info prevented me from using LEFT JOIN WHERE NULL to find orphaned records. I ended up having to change my query to:

SELECT DISTINCT mi.mitem_id, descr FROM menu_item mi
WHERE mi.mitem_id NOT IN 
(SELECT mi.mitem_id FROM menu_item mi 
INNER JOIN price_info pi ON pi.menu_item = mi.mitem_id 
INNER JOIN orderdetails od ON od.price_info = pi.pinfo_id)