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))

Answer 2

Your problem is the != NULL. This will always fail the filtering in the WHERE clause (the result is going to be NULL which is never true).

I think you want:

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

If t2.ref could be NULL, then the appropriate version is:

SELECT f1, f2, f3
FROM table1 t1
WHERE status = 1 AND
      EXISTS (SELECT 1 
              FROM table2 t2 
              WHERE t2.altref = SUBSTR(t1.f2, 1, 4) AND t2.ref IS NOT NULL
             );

Answer 3

Use an EXISTS

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

Answer 4

Try this:

SELECT f1, f2, f3
FROM table1 t1
INNER JOIN (
      SELECT ref from table2 where altref = SUBSTR(t1.f2, 1,4)
    ) as table2 on table2.altref = SUBSTR(t1.f2, 1,4)
WHERE status = 1