subqueries with bigint data type | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

subqueries with bigint data type

Problem is as follows. When I do the following query I get 0 results, even though I should get ALL results from the table (about 2000 records). Both CONTENT_ID and ID are bigint values that are guaranteed to be unique within the table. SELECT CONTENT_ID FROM TABLE_1
WHERE CONTENT_ID NOT IN (SELECT ID FROM TABLE_2)
TABLE_1 has about 2000 records, each with a unique CONTENT_ID (bigint). TABLE_2 has less than 10 records and neither of the tables shares an ID. In other words, this query should be equivalent to doing
SELECT CONTENT_ID FROM TABLE_1 In addition, I get 0 results when I do:
SELECT CONTENT_ID FROM TABLE_1 WHERE CONTENT_ID IN (SELECT ID FROM TABLE_2) How can both of these mutually exclusive queries come up with 0 results? Is there some quirky SQL Server 2000 bug for JDBC? Thanks in advance, Jake Gordon, Web Applications Programmer/DB Administrator for Ridge Tool Company
Not 30 seconds after I posted this a heroic co-op at my work place discovered the problem. There are null values in TABLE_2. Apparently when you do subqueries on a table with null values you get "undefined" results. This seems to have solved the problem. Thanks if you took the time to read this.
Check this for a great explanation of your issue:http://snipurl.com/lnd2
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
]]>