James Young James Young - 3 months ago 5
SQL Question

Is there a better way to write this SQL SELECT statement than using a subquery?

I have this queury:

SELECT A, B, C
FROM ( SELECT
lla.id as A,
max(decode(lla.attrid, 2, lla.valstr, null)) as B,
max(decode(lla.attrid, 3, lla.valstr, null)) as C
FROM
llattrdata lla,
llattrdata lla2
WHERE
lla.id = lla2.id
GROUP BY lla.id)
WHERE C = "Yes"


Is there a better way to do this? I tried to use a HAVING clause, but couldn't get it to work against column C="Yes". Thanks in advance.

Answer

I will let you into a secret. there's nothing wrong with what you have. so don't feed the need to rewrite it.

SELECT A, B, C
FROM (  SELECT 
        lla.id as A,
        max(decode(lla.attrid, 2, lla.valstr, null)) as B, 
        max(decode(lla.attrid, 3, lla.valstr, null)) as C
    FROM 
        llattrdata lla, 
        llattrdata lla2
    WHERE 
        lla.id = lla2.id 
    GROUP BY lla.id)
WHERE C = 'Yes'

is the same performance as :

SELECT 
    lla.id as A,
    max(decode(lla.attrid, 2, lla.valstr, null)) as B, 
    max(decode(lla.attrid, 3, lla.valstr, null)) as C
FROM 
    llattrdata lla, 
    llattrdata lla2
WHERE 
    lla.id = lla2.id 
GROUP BY lla.id
HAVING max(decode(lla.attrid, 3, lla.valstr, null)) = 'Yes';

you will gain no performance by avoiding the inline view here. whichever is more readable to you, go with that.