SQL subquery matches hard-coded IN criteria, but not subquery

Question

I have a group of people who have taken a test. I can select their IDs with this query:

SELECT person_id
FROM tests
WHERE test_code = 1234

I'd like to pull these individuals' records from a demographics table, so I tried this subquery to do so:

SELECT *
FROM demographics d
WHERE d.person_id IN (
    SELECT t.person_id
    FROM tests t
    WHERE t.test_code = 1234
)

... but I'm not getting any results. If I take a couple of the IDs from the (functional) subquery and hard-code them into the IN criteria:

SELECT *
FROM demographics d
WHERE d.person_id IN (01123, 58132)

... the query works. I've got to be missing something really fundamental here - do you know what it is?

Possible complicating factors: t.person_id is char13, d.person_id is varchar50. This is MS SQL Server 9.0.4035; I'm working in SQL Server Management Studio.

score 2 · Accepted Answer · edited Dec 09 '21 at 09:58

2

The problem may be that TESTS.PERSON_ID is coming in with trailing blanks on it since it's declared as CHAR instead of VARCHAR. I'm not sure how to remove trailing blanks in SQL Server (it's been a while since I used it), but in Oracle I'd use the TRUNC function, as in

SELECT * 
  FROM demographics d 
  WHERE d.person_id IN ( 
    SELECT TRUNC(t.person_id )
      FROM tests t 
      WHERE t.test_code = 1234 )

Edit: I believe the equivalent function in SQL Server is RTRIM.

edited Dec 09 '21 at 09:58

Nimantha

6,405
6
28
69

answered Oct 20 '10 at 17:05

Bob Jarvis - Слава Україні

48,992
9
77
110

1

Oracle: TRUNC(t.person_id) SQL: LTRIM(RTRIM(t.person_id)) – Vidar Nordnes Oct 20 '10 at 17:10
@Vidar, @ig0774: I looked it up and you're both right; LTRIM(RTRIM(...)) is equivalent to Oracle's TRUNC, but RTRIM is really what's needed. Thanks. :-) – Bob Jarvis - Слава Україні Oct 20 '10 at 17:13
I believe that this the issue. The SQL Server trim functions are LTRIM and RTRIM. Try the hard-coded query using (' 01123', ' 58132') as the argument to the IN clause to verify. – Mark M Oct 20 '10 at 17:13
Actually, it was *leading zeroes*. The demographic IDs all have three zeroes affixed to the front... hmm. But this got me thinking about the right things. Thanks! – Matt Parker Oct 20 '10 at 17:32

score 2 · Answer 2 · answered Oct 20 '10 at 17:09

2

First off, you are not aliasing tests properly. Should be:

SELECT *
FROM demographics d
WHERE d.person_id IN (
    SELECT t.person_id
    FROM **tests t**
    WHERE t.test_code = 1234
)

Secondly, if t.person_id returns any NULLs you will not get any results.

answered Oct 20 '10 at 17:09

Larry Lustig

49,320
14
110
160

NULLs only have this affect for `NOT IN` – Martin Smith Oct 20 '10 at 17:14
Sorry, that was just a typo. Good attention to detail, though. – Matt Parker Oct 20 '10 at 17:34
@Martin — Thank you, I stand corrected. The database I most use does not implement that NULL issue according the SQL standard, something I usually appreciate. Until recently, SQLite3 did the same. – Larry Lustig Oct 20 '10 at 18:30

score 0 · Answer 3 · answered Oct 20 '10 at 17:06

0

SELECT * 
FROM demographics d 
WHERE d.person_id IN ( 
    SELECT person_id 
    FROM tests 
     WHERE test_code = 1234 
)

Have you tried it without the t. in the subquery? as looking at your original subquery your not giving the table tests an alias of t...

answered Oct 20 '10 at 17:06

Wes Price

346
6
12

Yes, I had validated that the subquery worked as written (which, ahem, was not exactly as I had it written here - Larry caught a typo in my table aliasing). – Matt Parker Oct 20 '10 at 17:36

score 0 · Answer 4 · answered Oct 20 '10 at 17:06

I've never tried a subquery in an IN clause, so I can't guarantee this works, but try this:

SELECT * 
FROM demographics d 
WHERE d.person_id IN ( 
    (SELECT t.person_id 
    FROM t.tests 
    WHERE t.test_code = 1234) 
)

I simply added parentheses around your subquery, which is common practice. Again, I've never tried a subq in an IN clause so can't guarantee this works but it's worth a shot.

SQL subquery matches hard-coded IN criteria, but not subquery

4 Answers4