Sql 0 Results for 'Not In' and 'In' When Row Does Exist

SQL 0 results for 'Not In' and 'In' when row does exist

Don't use NOT IN with a subquery. Use NOT EXISTS instead:

delete from tableA 
    where not exists (select 1 from tableB where tableA.orderno = tableB.orderno);

What is the difference? If any orderno in TableB is NULL, then NOT IN returns NULL. This is correct behavior based on how NULL is defined in SQL, but it is counterintuitive. NOT EXISTS does what you want.

Why does this PostgreSQL query result in 0 results even though I know there are

I strongly, strongly discourage you from using not in with subqueries. The reason is simple: NULL values are not handled intuitively.

As you have observed, NOT IN filters out all rows if any of the rows returned by the subquery are NULL.

There is a simple alternative: not exists:

select count(*)
from journal j
where not exists (select 1
                  from journal_contribution jc
                  where jc.journal_uuid = j.uuid
                 );

In addition, this query can make use of an index on journal_contribution(journal_uuid). I am guessing that with such an index, this query will be rather speedy.

Query with NOT IN subquery returning 0 rows

Based on the provided SQLFiddle in the edited question, this works.

SELECT p.*, 'BBCode' AS Format,
FROM_UNIXTIME(TIME) AS DateInserted,
FROM_UNIXTIME(editTime) AS DateUpdated
FROM et_post p
INNER JOIN et_conversation c 
ON c.conversationId = p.conversationId
and c.private = 0
join (
select conversationId,min(postId) as m
from et_post
group by conversationId
) r
on r.conversationId = c.conversationId
where p.postId<>r.m

12,15,18 disappear as requested in your edit ... so too does NOT IN madness

SQL "select where not in subquery" returns no results

Update:

These articles in my blog describe the differences between the methods in more detail:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

---

There are three ways to do such a query:

• LEFT JOIN / IS NULL:

  SELECT  *
  FROM    common
  LEFT JOIN
          table1 t1
  ON      t1.common_id = common.common_id
  WHERE   t1.common_id IS NULL
  

• NOT EXISTS:

  SELECT  *
  FROM    common
  WHERE   NOT EXISTS
          (
          SELECT  NULL
          FROM    table1 t1
          WHERE   t1.common_id = common.common_id
          )
  

• NOT IN:

  SELECT  *
  FROM    common
  WHERE   common_id NOT IN
          (
          SELECT  common_id
          FROM    table1 t1
          )
  

When table1.common_id is not nullable, all these queries are semantically the same.

When it is nullable, NOT IN is different, since IN (and, therefore, NOT IN) return NULL when a value does not match anything in a list containing a NULL.

This may be confusing but may become more obvious if we recall the alternate syntax for this:

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

The result of this condition is a boolean product of all comparisons within the list. Of course, a single NULL value yields the NULL result which renders the whole result NULL too.

We never cannot say definitely that common_id is not equal to anything from this list, since at least one of the values is NULL.

Suppose we have these data:

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN / IS NULL and NOT EXISTS will return 3, NOT IN will return nothing (since it will always evaluate to either FALSE or NULL).

In MySQL, in case on non-nullable column, LEFT JOIN / IS NULL and NOT IN are a little bit (several percent) more efficient than NOT EXISTS. If the column is nullable, NOT EXISTS is the most efficient (again, not much).

In Oracle, all three queries yield same plans (an ANTI JOIN).

In SQL Server, NOT IN / NOT EXISTS are more efficient, since LEFT JOIN / IS NULL cannot be optimized to an ANTI JOIN by its optimizer.

In PostgreSQL, LEFT JOIN / IS NULL and NOT EXISTS are more efficient than NOT IN, sine they are optimized to an Anti Join, while NOT IN uses hashed subplan (or even a plain subplan if the subquery is too large to hash)

Return false if no rows for select

You could just selects an EXISTS expression here:

SELECT EXISTS (SELECT 1 FROM user_test_date
               WHERE test_date < NOW() AND new_date IS NULL AND login_id = 1) AS isTest;

This would always return just a single record, containing a boolean value of true or false.

How to select rows where multiple values do not exist

Use NOT IN:

select distinct surveyDataId
FROM surveyData
WHERE surveyDataId NOT IN (
    SELECT surveyDataId
    FROM surveyData
    WHERE chosenInterests in (2,3)
);

BTW your query with EXISTS would work too:

select distinct surveyDataId
FROM surveyData sd1
WHERE NOT EXISTS (
    SELECT *
    FROM surveyData sd2
    WHERE sd1.surveyDataId = sd2.surveyDataId and chosenInterests in (2,3)
);

Why is my SQL 'NOT IN' clause producing different results from 'NOT EXISTS'

You have a NULL in the subquery SELECT l.event_id FROM locations AS l so NOT IN will always evaluate to unknown and return 0 results

SELECT COUNT(DISTINCT e.event_id) 
FROM   events AS e
WHERE  e.event_id NOT IN (SELECT  l.event_id FROM locations AS l)

The reason for this behaviour can be seen from the below example.

'x' NOT IN (NULL,'a','b')

≡ 'x' <> NULL and 'x' <> 'a' and 'x'
<> 'b'

≡ Unknown and True and True

≡ Unknown

---

Related Topics