SELECT CONCAT("https://en.wikipedia.org/wiki/User", CASE WHEN p.page_namespace = 3 THEN '_talk:' ELSE ':', p.page_title)

FROM enwiki_p.page p

LEFT JOIN enwiki_p.user u ON REPLACE(SUBSTRING_INDEX(p.page_title, '/', 1), '_', ' ') = u.user_name

WHERE p.page_namespace IN (2, 3)

AND u.user_id IS NULL

AND p.page_is_redirect = 0

AND p.page_title NOT LIKE '%.%.%.%'

AND p.page_title NOT LIKE '%:%:%:%:%:%:%:%'

AND p.page_title NOT LIKE '%.css'

AND p.page_title NOT LIKE '%.cs'

AND p.page_title NOT LIKE '%.js'

AND p.page_title NOT LIKE '%.charset'

AND p.page_id NOT IN (

  SELECT cl.cl_from FROM

  enwiki_p.categorylinks cl

  WHERE cl.cl_to IN ('User_soft_redirects', 'Wikipedia_doppelganger_accounts')

)