CODEWITHSUNDEEP
sqloracle-database
Skywalker
Skywalker

Reputation: 77

NOT EXISTS together with SELECT NULL

I'm trying to figure out what this SQL query is doing, more specifically in the part that starts after NOT EXISTS:

SELECT
    order_num,
    MIN(order_date)
FROM
    orders
WHERE
    order_date >= '01.01.2019'
    AND
    NOT EXISTS
    (
        SELECT
            NULL
        FROM
            result
        WHERE
            unique_id = '201895'
            AND
            result = order_num
    )
GROUP BY
    order_num

Upvotes: 4

Views: 15144

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

EXISTS/NOT EXISTS checks to see if rows are returned. It does not care what is in the result -- even NULL. I am definitely not a fan of using it. I tend to use 1 -- easier to type and clearer.

That said, the query should be using table aliases and qualified column names. This can be very important with correlated subqueries:

SELECT o.order_num, MIN(o.order_date)
FROM orders o
WHERE o.order_date >= DATE '2019-01-01' AND
      NOT EXISTS (SELECT 1
                  FROM result r
                  WHERE r.unique_id = '201895' and
                        r.result = o.order_num
                 )
GROUP BY o.order_num;

I also fixed the date constant to use the DATE keyword. This is safer than relying on database settings.

Upvotes: 2

Ortiga
Ortiga

Reputation: 8814

SELECT NULL still returns rows from the query. It does the same as if it was:

[...] EXISTS( SELECT 1 FROM [...]

or

[...] EXISTS( SELECT Id FROM [...]

It's just one way to make it clear that the value is not used.

Upvotes: 8

Related Questions