Namespace names w/less boilerplate

A way to get namespace names in query results with less boilerplate, or at least less wasted vertical space in the query, than the CASE expression I've been using e.g. in query 77254.

A significant drawback is that if you use the simpler versions - that is, the ones without COALESCE - then you get either a NULL (if you left join ns) or no row at all (if you just join it) in the vanishingly-rare cases when there's a page in an undefined namespace.  On the other hand, "SELECT CONCAT(ns_s, page_title)" or even just "SELECT ns_s, page_title" feels really natural, and the latter even only hides the namespace, not title, for the hypothetical screwy page.  (Looks the same as the main namespace on Quarry, though, which represents NULLs as blank.  That'd be pretty misleading.)

Alternatively, eliminating the whitespace from the CASE version also gets it to seven lines.  You'd still need to repeat it for each pagename column in the resultset, though, which is an occasional issue.

And of course you can always just stuff either into a single line if you don't mind the query box scrolling horizontally.

SQL

WITH ns(ns_n, ns_s) AS (VALUES (-2, 'Media:'), (-1, 'Special:'), (0, ''), (1, 'Talk:'), (2, 'User:'), (3, 'User talk:'), (4, 'Wikipedia:'),
  (5, 'Wikipedia talk:'), (6, ':File:'), (7, 'File talk:'), (8, 'MediaWiki:'), (9, 'MediaWiki talk:'), (10, 'Template:'), (11, 'Template talk:'),
  (12, 'Help:'), (13, 'Help talk:'), (14, ':Category:'), (15, 'Category talk:'), (100, 'Portal:'), (101, 'Portal talk:'), (118, 'Draft:'),
  (119, 'Draft talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'), (828, 'Module:'), (829, 'Module talk:'), (2300, 'Gadget:'),
  (2301, 'Gadget talk:'), (2302, 'Gadget definition:'), (2303, 'Gadget definition talk:'))
SELECT CONCAT(COALESCE(ns1.ns_s, CONCAT('{{ns:', page_namespace, '}}:')), page_title) AS pagename,
       CONCAT(COALESCE(ns_plus_4.ns_s, CONCAT('{{ns:', page_namespace + 4, '}}:')), page_title) AS 'pagename in ns+4'
FROM page
LEFT JOIN ns AS ns1 ON ns1.ns_n = page_namespace
LEFT JOIN ns AS ns_plus_4 ON ns_plus_4.ns_n = page_namespace + 4
WHERE page_random BETWEEN 0.3916 AND 0.391601;

SQL

Checking query status...