Geek Answers Handbook

How to override another value in sql "WHERE" section?

I have a case where I need to retrieve the entries for the field1 = 'value1' column, if there are no values ​​for 'value1', then I have to retrieve the entry for 'default'.

In the above scenario, I used two queries:

Select * from table_name where field1="value1"

If the above query does not return any record, I run the following query:

Select * from table_name where field1="default"

Now I want to do the above in a single request. Can someone please help me with the same. I believe the answer lies somewhere in using the CASE WHEN clause.

Also the above queries should work for oracle, postgres, as well as mysql.

+6
source share
7 answers

Use CASE and Exists as shown below

 Select * from table_name where field1= case when exists(select 1 from table_name where field1='value1') then 'value1' else 'default 'end
+3
source

The basic ANSI SQL answer expected to run on all different platforms:

 select * from table_name where field1 = 'value1' or (field1 = 'default' and NOT EXISTS (select 1 from table_name where field1 = 'value1'))
+6
source

Optimal solution using coalesce() :

 Select * from table_name where field1 = coalesce ( (select field1 from table_name where field1='value1' limit 1) , 'default' );

Note limit 1 in the subquery: in this case, it is imperative that the subquery does not return more than one row. But even using the case when exists (...) approach, it is good practice to add it otherwise, the database engine may be forced to scan all rows matching the subquery.

Of course, most modern databases are smart enougth to quietly optimize it. But some old ones could not. And in general, these may be cases when they cannot.

That is, for example, in PostgreSQL, if the subquery uses not (or not declared as) stable functions, the scheduler will be forced to perform a full scan to obtain consistent results if this function has any effect s | ide.

+1
source

For mysql:

 SET @a = Select Count(*) from table_name where field1="value1" IF @a > 0 Then Select * from table_name where field1="value1" ELSE Select * from table_name where field1="default" END IF
0
source

Ok, I tried it, it seems to work.

 SELECT * FROM table_name WHERE CASE WHEN EXISTS(SELECT * FROM table_name WHERE field1='value1') THEN field1= 'value1' ELSE field1='default' END
0
source

You can try this ...

 select * table_name where field1="default" and not exists (select * from table_name where field1="value1") union all select * from table_name where field1="value1"
0
source

You can use the case with the count tag.

 select * from table_name where coulmn_1 = (case when (select count(1) from dc_date_card where coulmn_1 = value_1) > 0 then value_1 else Default end)
0
source

More articles:


All Articles