How to filter SQL results when the referenced table can be empty?-CodePudding

I have a query which selects from different tables, then uses a WHERE clause to filter the items. The clause looks like this:

WHERE allItems.NumID != (SELECT RelatedNumID FROM allRelated WHERE NumID = allItems.NumID)

This works fine when allRelated table isn't empty. But when it's empty, I get 0 results from the query.

I have tried to use NOT IN instead of !=. It returns result when allRelated is empty, but doesn't filter the results when allRelated isn't empty:

WHERE allItems.NumID NOT IN (SELECT RelatedNumID FROM allRelated WHERE NumID = allItems.NumID)

I also tried to join the tables, but I get the same result as with NOT IN:

LEFT JOIN allRelated ON allItems.NumID = allRelated.NumID
WHERE allRelated.NumID IS NOT NULL

How should I write the query so that it works for both empty and non-empty allRelated table?

P.S. I'm using SQLite.

CodePudding user response：

Try with NOT EXISTS:

WHERE NOT EXISTS (
  SELECT 1 
  FROM allRelated 
  WHERE allRelated.NumID = allItems.NumID
    AND allRelated.RelatedNumID = allItems.NumID
)

or, change the condition in the WHERE clause of the LEFT join to IS NULL:

LEFT JOIN allRelated 
ON allItems.NumID = allRelated.NumID AND allRelated.RelatedNumID = allItems.NumID
WHERE allRelated.NumID IS NULL