Search code examples
mysqlselectinverse

How do i select the inverse of this query?

How do I select the inverse of this query, and why are my attempts failing...

Query to reverse:

SELECT * FROM table_name 
where (category_id is null and product_id is null) or request_path like '%Portal%';

My attempt:

SELECT * FROM table_name 
where 
(category_id is not null and product_id is not null)
and request_path not like '%Portal%';

When I try to do count() on the results of each and add them together I do not get the total count() for every row. It is always less than the total, so I know that I'm not selecting the inverse. I also can't prove i'm not duping the selection.

Assume that category_id and product_id can be integers, and request_path is never string.empty or null.

I'm a .net programmer. I could probably do this in linq pretty easily, but i'm fixing up a mysql database. Straight SQL has always been my Achilles heel.

Solution

  • Transfering De Morgan's laws to SQL you would get the following rules:

    NOT (a AND b) = NOT a OR  NOT b
NOT (a OR  b) = NOT a AND NOT b

    That is why you need to replace and with or

    where (category_id is not null or product_id is not null)
  and request_path not like '%Portal%'

    But I would write it the following way:

    where coalesce(category_id, product_id) is not null
  and request_path not like '%Portal%'

    Update

    Trinimons comment is correct: If the request_path can contain NULLs, the correct inversion of

    request_path like '%Portal%'

    should be 

    (request_path not like '%Portal%' or request_path is null)

    because null not like 'anything' will return NULL.