Why is nothing selected in this select query?

Question

If I run

Select distinct CompanyID from Device

I get 6 rows. If I run

Select CompanyID from Company

I get 8441 rows. If I run the following:

If I run

Select CompanyID
from Company where CompanyID NOT IN 
( 
   Select distinct CompanyID from Device
)

I should get the 8435 companies that aren't in the original select statement, correct?

However, when I run this I get 0 rows. What is the issue here? One of the 6 results from above is null but that shouldn't matter.

Thanks!

edit: With Marc-s's help I was able to get the following statement to be what I needed, I still feel like there was an extra step in this query, anyone care to add to this?

DELETE from Company where CompanyID NOT IN
(
Select C.CompanyID 
from Company C where C.CompanyID IN 
( 
  Select distinct CompanyID from Device
)
OR CompanyID IN
(
  Select distinct CustomerID from Device
)
OR CompanyID IN
(
  Select distinct CompanyID from AssignedCompanies
 )
 )

marc_s · Answer 1 · 2012-10-31T21:49:07.693

No, the output is correct.

You have 8441 rows in Company - their CompanyID values are just one of those 6 that you got from your first query (let's say A, B, C, D, E, F).

ALL the rows have a CompanyID value from those 6 distinct values.

So when you select in your last query, you want to get all rows that have a value that's NOT any of those six values you got in your first query - but ALL the rows do have one of those 6 values ( A through F) for their CompanyId !

So you get back nothing - since all of their CompanyId values do exist in that subselect and are thus excluded from the SELECT statement

score 2 · Accepted Answer · edited May 23 '17 at 12:27

2

Assuming there are CompanyIDs in Company that are not in Device, the problem is the NULL value in the 6 from Device. Your NOT IN statement evaluates to UNKNOWN even if the CompanyID is not one of the 6. See this Q/A for more details: NOT IN clause and NULL values

Edit: To avoid this try altering your subquery to:

SELECT DISTINCT CompanyID FROM  Device WHERE CompanyID IS NOT NULL

edited May 23 '17 at 12:27

Community

1
1

answered Oct 31 '12 at 21:50

mjoshawa

115
8

I believe this is the correct answer. Good to know, I've run into this a few times. – Dave Oct 31 '12 at 22:51
I just added a potential fix. Sorry, I got distracted and forgot to earlier. – mjoshawa Oct 31 '12 at 23:37

Taryn · Answer 3 · 2012-10-31T21:59:07.433

The result is correct.

If your device table contains all of the same values in your company table then your query to select all the DISTINCT CompanyID is including all of the CompanyID's in your other table:

Example:

CREATE TABLE device ([companyid] int);

INSERT INTO device  ([companyid])
VALUES
    (1),
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (7)
;

CREATE TABLE company([companyid] int, [name] varchar(5));

INSERT INTO company ([companyid], [name])
VALUES
    (1, 'Comp1'),
    (2, 'Comp2'),
    (3, 'Comp3'),
    (4, 'Comp4'),
    (5, 'Comp5'),
    (6, 'Comp6'),
    (7, 'Comp7')
;

Your query will return the following DISTINCT values - 1, 2, 3, 4, 5, 6, 7. Your company table holds those same values so the NOT IN will return zero records:

See SQL Fiddle with demo

Now let's say that your device table did not hold all of the values of the company table, then you would return any of the missing values

CREATE TABLE device([companyid] int);

INSERT INTO device([companyid])
VALUES
    (1),
    (1),
    (2),
    (3),
    (4)
;

CREATE TABLE company([companyid] int, [name] varchar(5));

INSERT INTO company([companyid], [name])
VALUES
    (1, 'Comp1'),
    (2, 'Comp2'),
    (3, 'Comp3'),
    (4, 'Comp4'),
    (5, 'Comp5'),
    (6, 'Comp6'),
    (7, 'Comp7')
;

Then this query will return values 5, 6, 7 because while they exist in the company table they are not in the device table:

Select CompanyID
from Company 
where CompanyID NOT IN (Select distinct CompanyID 
                        from Device)

See SQL Fiddle with Demo

score 0 · Answer 4 · answered Oct 31 '12 at 21:42

0

There are all distinct CompanyIDs in the nested select. Therefore you won't find any rows with CompanyID that is not present in that set.

answered Oct 31 '12 at 21:42

Michal Klouda

14,263
7
53
77

score 0 · Answer 5 · answered Oct 31 '12 at 21:44

The query is probably correct, but you are making an assumption that that there is no overlap in the data.

For example, if the Device table has CompanyID values 1,2,3,4,5, and the Company table has values 1,2,3,4,5,6,7, then your query will result in 0 rows.

If you invert your query from NOT IN to IN, it should show you the intersecting values.

Why is nothing selected in this select query?

5 Answers5

Related