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:'), (126, 'MOS:'), (127, 'MOS talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'),

  (828, 'Module:'), (829, 'Module talk:')),

prefixes AS (SELECT CONCAT('Editnotices/Page/', REPLACE(REPLACE(ns_s, ':', ''), ' ', '_'))

             FROM ns

             WHERE ns_n <> 0)

SELECT CONCAT('Template:', p1.page_title), p2.page_is_redirect AS 'exists as redirect'

FROM page p1

LEFT JOIN page AS p2 ON p2.page_namespace = 0 AND p2.page_title = SUBSTRING(p1.page_title, LENGTH('Editnotices/Page/') + 1)

WHERE p1.page_namespace = 10  -- template

  AND p1.page_title LIKE 'Editnotices/Page/%'

  AND (p2.page_is_redirect IS NULL -- doesn't exist

       OR (p2.page_is_redirect = 1 -- exists as redirect

           AND p1.page_is_redirect = 0)) -- but the editnotice isn't also a redirect

  -- exclude editnotices for non-mainspace pages

  -- not just pages without colons, as that misses Template:Editnotices/Page/Index_of_underwater_diving:_N–Z

  AND SUBSTRING_INDEX(p1.page_title, ':', 1) NOT IN (SELECT * FROM prefixes)

ORDER BY p1.page_title ASC;