Check existence of a result from sub query in SELECT

Question

How do I get not-null results from a sub query in SELECT statement?

SELECT a, b, c,
       (SELECT d 
        FROM table2
        WHERE ...) as d
FROM table 1
WHERE ...

I want to get results only when all values (a, b, c , d) not Null. It won't be kind of weird/non-efficient to use the same sub-query in main WHERE clause as well but with EXISTS?

Easiest way is to put all that in another query: `SELECT * FROM (SELECT a, b, c, (SELECT d ...) FROM ...) AS x WHERE x IS NOT NULL` — 404, Oct 31 '18 at 13:43
Can you be more specific? Please provide some example of what you need to do.. — codeLover, Oct 31 '18 at 13:43
I don't really understand the question. Can you show some data an the expected result? — The Impaler, Oct 31 '18 at 13:53
Possible duplicate of [SQL use column from subselect in where clause](https://stackoverflow.com/questions/18029786/sql-use-column-from-subselect-in-where-clause) — SABER, Oct 31 '18 at 14:00
@eurotrash I like your solution - it works and elegant. Unfortunately, I can't mark it as an answer — Agenobarb, Oct 31 '18 at 14:20

score 3 · Accepted Answer · answered Oct 31 '18 at 14:23

The easiest way to do this is to put your original query in a subquery, then you can check whether the whole row that the subquery returns is NULL:

SELECT *
FROM (
    SELECT a, b, c,
           (SELECT d 
            FROM table2
            WHERE ...)
    FROM table 1
    WHERE ...
) AS sub
WHERE sub IS NOT NULL

sub being the row of (a,b,c,d) returned by the subquery.

Vaso Miruashvili · Answer 2 · 2018-10-31T13:57:12.240

0

SELECT 
 t1.a,
 t1.b,
 t1.c, 
 t2.d
FROM table1 t1
left join table2 as t2 on t2.ID = t1.ID
WHERE t1.a is not null and t1.b is not null and t1.c is not null and t2.d is not null

edited Oct 31 '18 at 13:57

answered Oct 31 '18 at 13:53

Vaso Miruashvili

101
1
11

While this code may answer the question, it is better to explain how to solve the problem and provide the code as an example or reference. Code-only answers can be confusing and lack context. – Robert Columbia Oct 31 '18 at 15:51

score 0 · Answer 3 · answered Oct 31 '18 at 13:54

You can use a subquery:

select a, b, c, d
from (SELECT a, b, c,
             (SELECT d 
              FROM table2
              WHERE ...) as d
      FROM table 1
      WHERE ... and
            a is not null and b is not null and c is not null
     ) x
where d is not null;

In all likelihood, though, you can use JOIN:

SELECT a, b, c, x.d 
FROM table 1 JOIN
      (SELECT d 
        FROM table2
        WHERE ...
     ) x
WHERE ... and
      a is not null and b is not null and c is not null and d is not null;

Check existence of a result from sub query in SELECT

3 Answers3