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

Question

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.

That is invalid SQL unless you do have a column named `Yes`. The derived table (it's not a sub-query) does not any processing overhead, it's only syntactic sugar, so don't worry. — , Nov 27 '12 at 13:54

score 3 · Accepted Answer · answered Nov 27 '12 at 13:55

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.

Several good answers here, but I checked yours off as you went on to talk about performance. — James Young, Nov 27 '12 at 22:20

score 2 · Answer 2 · edited Nov 27 '12 at 13:47

2

How about

SELECT 
        lla.id as A,
        max(decode(lla.attrid, 2, lla.valstr, null)) as B, 
        'Yes' 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 can't use an alias that you created in the same query. You have to write it again

edited Nov 27 '12 at 13:47

ypercubeᵀᴹ

113,259
19
174
235

answered Nov 27 '12 at 13:42

Marc

16,170
20
76
119

I like the "'Yes' as C". I didn't think of that. – James Young Nov 27 '12 at 22:21

score 2 · Answer 3 · edited May 23 '17 at 11:45

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"

Please read this : Why can't I use alias in a count(*) "column" and reference it in a having clause?

Vincent Malgrat · Answer 4 · 2012-11-27T13:48:40.713

Use the HAVING clause:

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'

This clause is evaluated after the GROUP BY and is used to filter on the grouped results.

Unfortunately you can't use aliases in the having clause. In some cases the subquery may be clearer in intent than the having clause.

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

4 Answers4