using field selected in outer part of query as parameter for sub query

Question

How can I use a field selected in the outer part of a query as a parameter for the subquery? I thought this was more or less it but obviously not as is should return a couple of rows

SELECT f1, f2, f3
FROM table1 t1
WHERE status = 1
AND (
      SELECT ref from table2 where altref = SUBSTR(t1.f2, 1,4)
    ) != NULL

UPDATE Thanks for all the answers so far. I should say though, using EXIST would return true all the time, as the row in the sub query will exist, just not necessarily with a value in 'altref', so I amended the EXISTS to include the is not null on the alt ref.

Answer 1

The problem here is that you're checking against null with the [in]eqaulity operator. Null isn't a value - it's the lack thereof - and thus, any value comparison against it (e.g., =, !=, >, etc) would return "unknown" (which isn't true, so any row returning evaluating to it won't be returned).

Instead, you should use the IS operator:

SELECT f1, f2, f3
FROM table1 t1
WHERE status = 1
AND (
      SELECT ref from table2 where altref = SUBSTR(t1.f2, 1,4)
    ) IS NOT NULL
-- Here^

It's worth noting, BTW, that the exists operator may be a tad more elegant:

SELECT f1, f2, f3
FROM   table1 t1
WHERE  status = 1 AND
       EXISTS (SELECT *
               FROM   table2
               WHERE  altref = SUBSTR(t1.f2, 1, 4))