CODEWITHSUNDEEP
mysqlsqlif-statementmariadbcase
Impromptu_Coder
Impromptu_Coder

Reputation: 435

if condition in where clause in MariaDB

These are the primary 4 columns in the EMP_TABLE that i'm using.

EMP_NAME EMP_ID EMP_DEPT JOIN_DATE

The general requirement is to fetch EMP_DEPT = 'HR' records only. But if EMP_DEPT = 'HR' records are NOT available in the table, then the requirement is to fetch EMP_DEPT = 'DEV' records from EMP_TABLE. The priority is to be given to HR records.

I know it can be achieved using inner queries or union and count. But I would like to know if it is possible using IF or WHEN. I've tried the following query and it doesn't seem to work.

select * from EMP_TABLE where if(EMP_DEPT='HR',EMP_DEPT='HR',EMP_DEPT='DEV');

Any help on this is appreciated.

Upvotes: 2

Views: 2985

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You want to do this as:

select e.*
from EMP_TABLE e
where (emp_dept = 'HR') or
      (emp_dept = 'DEV' and 
       not exists (select 1 from emp_table e2 where e2.emp_dept = 'HR')
      );

The first condition returns all rows with 'HR'. The second then returns 'DEV', but only when there are no 'HR' records.

You really do not want to use (or even learn) if(). It is a MySQL extension. The standard for conditional expressions is case.

Then, you don't want to include conditional expressions in where clauses. First, it impedes optimization. Second, most logic can be expressed using and and or, so it introduces additional "clutter" in the conditions.

Upvotes: 2

Nick
Nick

Reputation: 147186

You could do this with a case based on the count of rows with EMP_DEPT = 'HR'; if the count is non-zero then return rows with EMP_DEPT = 'HR' otherwise return rows with EMP_DEPT = 'DEV'.

SELECT *
FROM EMP_TABLE
WHERE EMP_DEPT = CASE WHEN (SELECT COUNT(*) FROM EMP_TABLE WHERE EMP_DEPT = 'HR') > 0 THEN 'HR' ELSE 'DEV' END

Demo on dbfiddle

Upvotes: 1

Related Questions