CODEWITHSUNDEEP
sqlplsql
user544079
user544079

Reputation: 16639

SQL query with NOT IN returning no rows

I have a sql statement as

select t.name, t.company from company t inner join employee e
on t.id = e.emp_id
where t.name not in(select t.name from table1 where t.id='x')

This above query returns no rows.

However, when I remove the sub query, and just use

select t.name, t.company from company t inner join employee e
    on t.id = e.emp_id

I get the required rows.

Also, the sub query 

select t.name from table1 where t.id='x'

gives rows of data when executed by itself. Is my syntax for the NOT IN incorrect?

Upvotes: 1

Views: 3008

Answers (4)

Guest
Guest

Reputation: 11

Not sure why the poster accepted the wrong answer. Yes there is an error in the query, the following should be changed from:

where t.name not in(select t.name from table1

to:

where t.name not in(select t1.name from table1 t1

However once that is done, the user's dilemma is still the same. Using NOT EXISTS is a workaround but avoids the real issue. The real solution is the one that was posted by Taryn, namely to add a filter in the subquery to exclude null values.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270763

A common cause is NULL values in the subquery. But you have a different problem. Here is your query:

select t.name, t.company
from company t inner join employee e
     on t.id = e.emp_id
where t.name not in(select t.name from table1 where t.id='x')

The t.name in the subquery refers to the "t" for company in the outer query. That is, the query is checking for t.name not in (t.name) -- which is always false. The subquery needs the name from table1. Using no alias fixes this:

select t.name, t.company
from company t inner join employee e
     on t.id = e.emp_id
where t.name not in(select name from table1 where id='x')

Better yet, use meaningful aliases (i.e. abbreviations of the table name) everywhere:

select c.name, c.company
from company c inner join employee e
     on c.id = e.emp_id
where c.name not in (select t1.name from table1 t1 where t1.id = 'x')

Upvotes: 1

Taryn
Taryn

Reputation: 247860

I am assuming that you might have null values in table1. If you have null values in table1, then you will want to explicitly exclude null in your WHERE NOT IN query:

select t.name, t.company 
from company t 
inner join employee e
  on t.id = e.emp_id
where t.name not in(select name 
                    from table1 
                    where id='x'
                       and name is not null);

You will also see that I removed the t. alias from the WHERE clause query. You are using an alias that is associated with the outer table company. You will either need to change the alias name or remove it.

Upvotes: 0

Sebas
Sebas

Reputation: 21542

this is because NOT IN (NULL) is always false

select t.name, t.company from company t inner join employee e
on t.id = e.emp_id
where t.name not in(select null from dual)

would be the same.

Use NOT EXISTS instead:

select t.name, t.company 
from company t 
    join employee e on t.id = e.emp_id
where 
    not exists(select 1 from table1 t2 where t.name = t2.name)
and t.id='x'

follow up: What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

Upvotes: 3

Related Questions