CTEs as Lookup Tables

Re:CTEs as Lookup Tables by VincentEvans on Wednesday January 11, 2023 @08:06PM (#34347660)

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 ‘’’

[ Parent | on HN ]

Re:CTEs as Lookup Tables by reactivenz on Wednesday January 11, 2023 @08:23PM (#34347799)

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)
  )

[ Parent | on HN ]

Re:CTEs as Lookup Tables by acjohnson55 on Wednesday January 11, 2023 @10:35PM (#34348862)

In the last example, it seems like it would be nice for the DB to let you omit the `SELECT * FROM` part.

[ Parent | on HN ]

Re:CTEs as Lookup Tables by TylerE on Thursday January 12, 2023 @01:41AM (#34350006)

In 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.

[ Parent | on HN ]