4 ms·
those UNION's should be UNION ALL otherwise they are deduplicated. Thus you code is worse, also the VALUES express is nicer when done in longer form WITH my_
by reactivenz 4y ago
those UNION's should be UNION ALL otherwise they are deduplicated. Thus you code is worse, also the VALUES express is nicer when done in longer form
WITH my_cte AS (
SELECT \* FROM VALUES
(1, 'column 2 value', 3.0),
(2, 'column 2 value', 3.0),
(3, 'column 2 value', 3.0),
(4, 'column 2 value', 3.0)
)
you can often alias the VALUES values like:
WITH my_cte AS (
SELECT \* FROM VALUES
(1, 'column 2 value', 3.0),
(2, 'column 2 value', 3.0),
(3, 'column 2 value', 3.0),
(4, 'column 2 value', 3.0)
as t(col1_name, col2_name, col3_name)
)
and some DB's allow you to alias via the cte name:
WITH my_cte(col1_name, col2_name, col3_name) AS (
SELECT \* FROM VALUES
(1, 'column 2 value', 3.0),
(2, 'column 2 value', 3.0),
(3, 'column 2 value', 3.0),
(4, 'column 2 value', 3.0)
)
- acjohnson55 4y agoIn the last example, it seems like it would be nice for the DB to let you omit the `SELECT * FROM` part.
- TylerE 4y agoIn any non trivial code select * is a code smell anyway. Thus parsers don’t spend cycles looking for forms that are unlikely to be useful in real code.