CODEWITHSUNDEEP
oracle-database
Raghul Raman
Raghul Raman

Reputation: 199

query to check if record exists and column null

I have a table and i have to check a particular column in null or has value.
Lets say the column name is order_price.

If i just check where order_price is null then this also includes records that are not in the table too.
For example i have order_id = 1 whose order_price is null and i have order_id = 2 which does not exist in order table. So instead of a where condition to check whether order_price is null, i want to know whether the column is null or the record does not exist.

I'm doing outer join on this table so i cannot map the primary keys of another table. I am using Oracle.

Thanks

Upvotes: 0

Views: 11991

Answers (2)

user507484
user507484

Reputation:

Instead of a Join , you can use the "EXISTS / NOT EXISTS" keyword and a subquery.

e.g.

SELECT parent.* 
FROM parent 
WHERE EXISTS (SELECT 1 
              FROM child 
              WHERE child.id_parent = parent.id AND child.somecolumn IS NULL)

You can play with "exists / not exists" depending on whether you want or don't want the predicate to match.

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425753

SELECT  o.*,
        od.order_price,
        CASE
        WHEN od.order_id IS NULL THEN
                'Not exists'
        WHEN od.order_price IS NULL THEN
                'Exists, IS NULL'
        ELSE
                'Exists, IS NOT NULL'
        END AS nullness
FROM    orders o
LEFT JOIN
        order_data od
ON      od.order_id = o.id

Upvotes: 3

Related Questions