4 ms·
Seems like an interesting idea, but could use a better example, at least for those who aren't yet intermediate level in SQL. In what world is this WITH coun
by charlie0 4y ago
Seems like an interesting idea, but could use a better example, at least for those who aren't yet intermediate level in SQL.
In what world is this
WITH countries (code, name) AS (
...> SELECT \* FROM (VALUES
...> ('us', 'United States'), ('fr', 'France'), ('in', 'India')
...> ) AS codes
...> )
...> SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code;
easier to read than this
SELECT code,
...> CASE code
...> WHEN 'us' THEN 'United States'
...> WHEN 'fr' THEN 'France'
...> WHEN 'in' THEN 'India'
...> END AS country
...> FROM data;
- data-ottawa 4y agoWhen you want more than one column
- rco8786 4y agoYea this is a common problem with contrived code samples like these. In my experience the author is right in that real world examples of using CASE can often get out of hand - especially if you need it in more than one place. But the contrived, simple example is clearly easier than the CTE. It is perhaps not surprising that we end up with this sort of stuff in production code, because the original author only needed one simple CASE statement and then it organically grew from there.
- pjot 4y agoTo me the first example makes me assume that there isn’t yet a `country` table to select from. Hence the `from values()` clause. If you were to run the first query on a fresh db, it’d return data. Running the second would fail.
- santialbo 4y agoTo me the advantanges comes when you use some sort of query builder. You can pass your list in code rather than having to write the SQL.