MySQL List Set theory, relational database

Question

SELECT business.name FROM business WHERE (3) NOT IN (1,2,3)

The above is a valid MySQL statement. But I need a statement which allows the NOT IN condition to work with 2 lists i.e. none of the elements in the first list appear in the second.

E.g.

SELECT business.name FROM business WHERE (4, 5) NOT IN (1,2,3)

Note: Each list has only integer values, and each list is generated from different tables using nested queries.

Thank You in advance

Answer 1

If values of both lists are retrieved by nested queries,
then you may join resultsets of these two nested queries and check if they have common values.
If yes - that means that some value from list 1 exists in list 2

The final query might look like this:

select *
from business
where not exists(
  SELECT 1 FROM 
  ( 
      -- nested query that retrieves values of list 1
      SELECT val FROM list1
  ) x
  JOIN
  (
    -- nested query that retrieves values of list 2
      SELECT val FROM list2
  ) y
  ON x.val = y.val
);

SQLFiddle demo --> http://www.sqlfiddle.com/#!2/5e47b/2

Answer 2

Unfortunately, you'll have to use AND conditions for every element of the 1-st set:

SELECT
    business.name
FROM
    business
WHERE
    4 NOT IN (1,2,3)
AND
    5 NOT IN (1,2,3)