CODEWITHSUNDEEP
mysqlsqlcase
Adam
Adam

Reputation: 10026

Efficient CASE Statements, SQL

Here is my query:

select col1, col2, (<a fairly complex query> as col3) from <a table>

<a fairly complex query> may return NULL, in which case I want to set col3 to col2. I know I could do this with a CASE statement:

select col1, col2, 
CASE WHEN (<a fairly complex query>) is NULL col2
ELSE (<a fairly complex query>) END AS col3 
from <a table>

However, that approach executes <a fairly complex query> twice. What are some options if I only want to execute <a fairly complex query> once?

Upvotes: 1

Views: 71

Answers (2)

tysonwright
tysonwright

Reputation: 1525

Similar to the other answer, but eliminates the need for a subquery:

select col1, col2, COALESCE(<a fairly complex query>, col2) AS col3 from <a table>

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

You could use subquery and COALESCE:

SELECT col1, col2, COALESCE(col3, col2) AS col3
FROM (select col1, col2, (<a fairly complex query>) as col3 
      from <a table>) AS sub;

Without subquery:

SELECT col1, col2, COALESCE(<a fairly complex query>, col2) AS col3
FROM <a table>;

Upvotes: 4

Related Questions