UNION works with CTE, but not without?

Question

I'm using PostgreSQL 11.12.

I have this query:

select first_name, length(first_name)
from db_employee
where length(first_name) = 
(select max(length(first_name)) from db_employee)
order by 1 
limit 1
union
select first_name, length(first_name)
from db_employee
where length(first_name) = 
(select min(length(first_name)) from db_employee)
order by 1 
limit 1

and when I run it I get this error:

syntax error at or near "union"

If I use CTE it works:

with cte1 as
(select first_name, length(first_name)
from db_employee
where length(first_name) = 
(select max(length(first_name)) from db_employee)
order by 1 
limit 1),

cte2 as
(select first_name, length(first_name)
from db_employee
where length(first_name) = 
(select min(length(first_name)) from db_employee)
order by 1 
limit 1)

select * from cte1
union
select * from cte2;

Why does the first query result in syntax error? Is it possible to get it to work without using CTE?

Does this work for you? It is for MySQL but worth a try. https://stackoverflow.com/questions/1415328/combining-union-and-limit-operations-in-mysql-query — , Feb 07 '22 at 01:28
Sure does. Feel free to comment below so you can get credit. — user219820, Feb 07 '22 at 01:38
Is `UNION` intentional? I.e., do you want to merge both rows if both are the same? Typically, that shouldn't be a thing, and you really want `UNION ALL` ... — Erwin Brandstetter, Feb 07 '22 at 01:42

Erwin Brandstetter · Accepted Answer · 2022-02-07T03:16:54.437

4

To include a LIMIT clause per SELECT in a UNION query, you must add parentheses. Like:

(  -- !
SELECT first_name, length(first_name)
FROM   db_employee
WHERE  length(first_name) = (SELECT max(length(first_name)) FROM db_employee)
ORDER  BY 1
LIMIT  1
) -- !
UNION
( -- !
SELECT first_name, length(first_name)
FROM   db_employee
WHERE  length(first_name) = (SELECT min(length(first_name)) FROM db_employee)
ORDER  BY 1
LIMIT  1
) -- !

Calculate difference of multiple highest and lowest column values

That said, your query can be optimized. Use instead:

(
SELECT first_name, length(first_name)
FROM   db_employee
ORDER  BY length(first_name) DESC NULLS LAST, first_name
LIMIT  1
)
UNION ALL  -- my guess
(
SELECT first_name, length(first_name)
FROM   db_employee
ORDER  BY length(first_name), first_name
LIMIT  1
);

Subtle difference: this does not completely exclude rows with first_name IS NULL, but that only shows if all rows have first_name IS NULL.

About NULLS LAST:

Sort by column ASC, but NULL values first?

edited Feb 07 '22 at 03:16

answered Feb 07 '22 at 01:39

Erwin Brandstetter

605,456
145
1,078
1,228

Using parenthesis makes sense. I thought of using UNION (and not UNION ALL) because if the names are all the same length, then min/max length is same (granted this is corner case). So you want to remove duplicates. Did I miss something ? – user219820 Feb 07 '22 at 02:08
1

All names with the same length is an exotic corner case. And nothing bad happens, you just get the same row twice. I would take the cheaper `UNION ALL`. Unimportant for the query at hand, just a common anti-pattern. Stripping the additional `SELECT` actually matters for performance. – Erwin Brandstetter Feb 07 '22 at 03:14
Sounds good, I will do UNION ALL. Last one. I notice you're sorting by length. Are you assuming there is an index on name and length? Otherwise sorting is expensive ie O(n log n) – user219820 Feb 07 '22 at 03:17
1

`ORDER BY length(first_name) LIMIT 1` is no more expensive than `(select max(length(first_name)) from db_employee)` - typically slightly cheaper, even. But my query only needs a single scan. Compare performance to see the proof. Each can profit from an expression index on `(length(first_name))`, or even a multicolumn index on `(length(first_name), first_name)`, but always weigh cost and benefit of a tailored index. – Erwin Brandstetter Feb 07 '22 at 03:21

score 0 · Answer 2 · answered Feb 07 '22 at 02:55

just put both the sql parts in separate brackets and keep union outside.

    (select first_name, length(first_name)
    from db_employee
    where length(first_name) = 
    (select max(length(first_name)) from db_employee)
    order by 1
    limit 1)
    union
    (select first_name, length(first_name)
    from db_employee
    where length(first_name) = 
    (select min(length(first_name)) from db_employee)
    order by 1
    limit 1);

another way of doing the same thing:

    select * from (
    select first_name, l_fname
    ,row_number() over (order by l_fname) rn_min
    ,row_number() over (order by l_fname desc) rn_max
    from(
    select first_name,length(first_name) l_fname from db_employee
    ) A
    ) b where (rn_min = 1 or rn_max = 1);

UNION works with CTE, but not without?

2 Answers2