1

I have the following code:

$sql=
    'select *,'.
    'case when fisicas_cpf is not null then null else razao_social end as razao_social,'.
    'case when fisicas_cpf is not null then null else nome_fant end as nome_fant '.
    'from pessoas p join fisicas f join juridicas j '.
    'on p.fisicas_cpf=f.cpf or p.juridicas_cnpj=j.cnpj '.
    'group by p.id'
;

I want to unify the case statements because they verify the same thing.

I want something like this:

'case when fisicas_cpf is not null then null else razao_social,nome_fant end as razao_social,nome_fant '.

Here, @AaronB says that the case just return one value. This applicate to my situation, or there is a way of doing it?

  • 1
    What is your motivation? Optimization? Evaluation of the case expressions is going to account for a tiny part of the execution time of this query... which, incidentally, isn't entirely valid from a relational perspective -- you are using GROUP BY with no aggregate functions, making the values of the other columns not deterministic. – Michael - sqlbot Sep 25 '13 at 22:38
  • I would say just "to not repeat", but the Sebastian gives the formal name: DRY principle. BTW, i don't understood what's the matter of the group by and the "not deterministic values". – Rafael Barros Sep 25 '13 at 22:51
  • 1
    What you appear have here is a query that may be written in a logically incorrect way, even though it appears to actually work, and may be performing very badly and returning correct results almost by chance. I would suggest that from pessoas p left join fisicas f on p.fisicas_cpf=f.cpf left join juridicas j on p.juridicas_cnpj=j.cnpj would be more logically correct and substantially faster and also that a group by should not be needed. It is impossible to say for certain without understanding your schema but that is my impression. – Michael - sqlbot Sep 26 '13 at 1:25
  • Oh, it hurts! but i undestand it's for my good. I will make some tries and give feedback. Thanks! – Rafael Barros Sep 26 '13 at 3:11
  • Wow! It worked flawless, without the cases and the group by. Thank you, @Michael-sqlbot. – Rafael Barros Sep 26 '13 at 15:16
1

CASE always returns a single scalar value. You could get to a single-test solution by left joining to a subquery returning a single row with the values only if the condition is met.

However, that would be fairly messy SQL Code. In this simple case you are better off just repeating the case statement. Even though this violates the DRY principle, it is in the end easier to read and maintain.


Correction: MySQL currently does not support correlated subqueries in a JOIN. Therefore the messy solution I mentioned above is not even possible in MySQL.

Just for completeness, here is the SQL Server solution:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.test(id INT, show_ind INT, col1 VARCHAR(10),col2 VARCHAR(10));

INSERT dbo.test VALUES(1,1,'c11','c21');
INSERT dbo.test VALUES(2,0,'c12','c22');
INSERT dbo.test VALUES(3,1,'c13','c23');
INSERT dbo.test VALUES(4,0,'c14','c24');

Query 1:

SELECT T.id,T.show_ind, XX.col1, XX.col2
  FROM dbo.test T
  CROSS APPLY(
    SELECT TOP(1) X.col1,X.col2
    FROM(
      SELECT 1 x,T.col1,T.col2 WHERE T.show_ind = 1
      UNION ALL
      SELECT 2 x,'',''
    )X
    ORDER BY X.x
  )XX

Results:

| ID | SHOW_IND | COL1 | COL2 |
|----|----------|------|------|
|  1 |        1 |  c11 |  c21 |
|  2 |        0 |      |      |
|  3 |        1 |  c13 |  c23 |
|  4 |        0 |      |      |

You probably agree that it is a lot harder to read than the CASE solution:

SELECT T.id,T.show_ind,
  CASE WHEN T.show_ind = 1 THEN T.col1 ELSE '' END AS col1,
  CASE WHEN T.show_ind = 1 THEN T.col2 ELSE '' END AS col2
FROM dbo.test T;

And these two examples do not even contain the JOINs that are part of your query.

  • What do you mean with "MySQL does not support correlated subqueries in a JOIN"? Do you mean the APPLY syntax? – ypercubeᵀᴹ Sep 25 '13 at 23:43
  • @ypercube, yes - the APPLY syntax or anything functionally comparable. – Sebastian Meine Sep 26 '13 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.