user2924127 user2924127 - 5 months ago 8
SQL Question

Why does this query return values in the column

I have the following query:

SELECT
a.id,
c.c_date
FROM table_a a ,
table_c c
WHERE
a.id = c.id AND
a.id IN (SELECT id from table_c where c_date is null);


I have two tables,
table_a
and
table_c
.

I join these two tables, but then get use an
IN
statement to only show the id's for in which are in
table_c
AND have the c_date column set to null`.

This query though returns
id, and c_date values, and some of the c_date values are not null
, how is this possible?

I thought in my sub query I am only selecting id which have null c_dates?

Answer

This should work without the subquery assuming you don't want to return null dates. Please note the use of the join:

SELECT a.id, 
     c.c_date 
FROM table_a a 
    JOIN table_c c ON a.id = c.id  
WHERE c_date is null;

It's difficult to answer your specific question though without sample data and expected results. You probably have multiple records in table_c that match the id field in table_a.