3 ms·
For the sake of completeness, you can accomplish the same using UNION instead of VALUES and subquery instead of CTE. ‘’’ WITH CTE AS ( SELECT ‘NY’ AS code, ‘N
by VincentEvans 4y ago
For the sake of completeness, you can accomplish the same using UNION instead of VALUES and subquery instead of CTE.
‘’’
WITH CTE AS (
SELECT ‘NY’ AS code, ‘New York’ AS state
UNION
SELECT ‘NJ’, ‘New Jersey’
)
SELECT * FROM CTE
‘’’
… likewise you can also do that in a subquery that you can immediately join:
‘’’
SELECT * FROM (
SELECT ‘NY’ AS code, ‘New York’ AS state
UNION
SELECT ‘NJ’, ‘New Jersey’
) SUB
INNER JOIN AnotherTable AT
ON SUB.code = AT.code
‘’’
- reactivenz 4y agothose 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.