CODEWITHSUNDEEP
oracle-databasesubquerywhere-clause
hbaltuntel
hbaltuntel

Reputation: 97

How Can I Get All Rows Using IN Subquery if the Subquery Returns No Rows

I have a query like this;

SELECT * FROM A
WHERE ID IN(
        SELECT ID FROM B WHERE STATUS=1
    )

I want to get all rows if sub query is null

How can i do this?

Upvotes: 0

Views: 530

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21075

If you mean I want to get all rows if sub query returns no rows use a three step approach

1 Check Subquery

select count(*) FROM B WHERE STATUS=1

If you get result > 0 the follow with step 2 otherwise with three

2 Get Subquery Result

-- your original query
SELECT * FROM A
WHERE ID IN(
        SELECT ID FROM B WHERE STATUS=1
    )

3 Get All Data

SELECT * FROM A

Alternative One Query Approach

Using OR you writes the query as you say: if the query returns no rows get all data.

SELECT * FROM A
WHERE ID IN(
        SELECT ID FROM B WHERE STATUS=1
    )
OR (SELECT count(*) FROM B WHERE STATUS=1) = 0

Upvotes: 0

Popeye
Popeye

Reputation: 35910

You can use LEFT JOIN and DISTINCT as the following:

SELECT DISTINCT A.* FROM A LEFT JOIN B
ON (A.ID = B.ID AND B.STATUS= 1)

Cheers!!

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

You Should use LEFT JOIN

SELECT A.* FROM A LEFT JOIN B ON A.ID = B.ID AND B.Status = 1

Upvotes: 1

Related Questions