Syntax error with aliases in PostgreSQL subqueries

Question

I'm new to SQL and trying to do a certain query in which I need two subqueries. I have to give them aliases, but when I do it says:

ERROR:  syntax error at or near "as"

But I don't see anything wrong in the syntax. I'm having a lot of trouble with this kind of queries and I don't find much information or examples. Would you help me?

Select
    ...
from
    turno,
    (select * from
        (
            select 
                ...
             from 
                accion
            where
                accion.tipo = 4 or accion.tipo = 5
        ) as part1
        union
        (
            select 
                ...
             from 
                accion
            where
                accion.tipo <> 4 and accion.tipo <> 5
        ) as part2
    ) as accion
where 
    ...
;

Thanks a lot.

Erwin Brandstetter · Accepted Answer · 2011-12-08 20:41:30Z

2

You have more query levels than necessary and mix them up. Try:

SELECT ...
FROM   turno,
      ( 
    SELECT ...
    FROM   accion
    WHERE  accion.tipo = 4 OR accion.tipo = 5

    UNION
    SELECT ...
    FROM   accion
    WHERE  accion.tipo <> 4 AND accion.tipo <> 5
   ) AS accion
WHERE ...

Better:

SELECT ...
FROM   turno
JOIN   (    
    SELECT ...
    FROM   accion
    WHERE  accion.tipo = 4 OR accion.tipo = 5

    UNION
    SELECT ...
    FROM   accion
    WHERE  accion.tipo <> 4 AND accion.tipo <> 5
   ) AS accion ON <join condition>
WHERE ...

Better yet, simplify to:

SELECT ...
FROM   turno
JOIN   (    
    SELECT ...
    FROM   accion
    WHERE  accion.tipo  = 4
       OR  accion.tipo  = 5
       OR (accion.tipo <> 4 AND accion.tipo <> 5)
   ) AS accion ON <join condition>
WHERE ...

Yields the same result, only faster & simpler.

The conditions qualify all rows (except a.tipo IS NULL) in accion. So in this particular case you can further simplify:

SELECT ...
FROM   turno
JOIN   accion a ON a.tipo IS NOT NULL AND <join condition>
WHERE  ...

But that's probably due to over-simplification of the problem, right?

This last example also demonstrates how the keyword AS is just noise in this context.

edited Dec 8, 2011 at 20:41

answered Dec 8, 2011 at 18:02

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jk041 Over a year ago

I can't use the last options since I need to make diferent things on the select for each one of the two subqueries. Thanks anyway.

Erwin Brandstetter Over a year ago

@jesusiniesta: Depending on what you actually need, you might get it cheaper with a CASE statement.

Jk041 Over a year ago

Brandstetter: Thanks, I didn't know that. It's great for those two subqueries (there is another third one I cannot put there, but it's a great help).

user359040 · Accepted Answer · 2011-12-08 17:59:35Z

1

Try:

Select
    ...
from
    turno,
    (select * from
        (
            select 
                ...
             from 
                accion
            where
                accion.tipo = 4 or accion.tipo = 5
         union
            select 
                ...
             from 
                accion
            where
                accion.tipo <> 4 and accion.tipo <> 5
        ) as part1_2
    ) as accion
where 
    ...
;

answered Dec 8, 2011 at 17:59

user359040

1 Comment

Jk041 Over a year ago

Obviously, it works now. I have tried all posible ways to put it that came to my mind, I guess I haven't got SQL yet. Thanks.

Collectives™ on Stack Overflow

Syntax error with aliases in PostgreSQL subqueries

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related