CODEWITHSUNDEEP
sqloracle-database
user2342436
user2342436

Reputation: 482

SQL to show matching records if exists else show all records

I'm want to write a SQL query to show the matching row if it exists, else shows all the records.

Example:- Input Data:-

id

1

2

3

4

select * from table where id = 2 /* since id exits it should return id o/p 2 */


select * from table where id = 8/* since id doesn't exist it should return all the rows of table)

i.e. o/p

1

2

3

4

I want to do this strictly in SQL , no PL/SQL nor any programming block

Upvotes: 0

Views: 2742

Answers (2)

evpo
evpo

Reputation: 2531

select *
from table
where not exists (select id from table where id = 2) or id = 2

If the subquery returns no records, the first condition is true for all records in the table and the query returns all records. Otherwise it's false and we only return the record where the second condition is true, which is id = 2

Upvotes: 6

TechDo
TechDo

Reputation: 18659

Please try:

select 
    * 
from 
    table
where 
    id=nvl((select id from table where id=2), id)

Upvotes: 1

Related Questions