How can I remove the subquery from the select statement?-CodePudding

I need help in removing the subquery out of the original SELECT statement. Is this even possible? I'm needing this to ultimately move queries like this to Denodo/VQL, which doesn't allow subqueries in SELECT statements (but does allow CTE/WITH and subqueries in FROM/WHERE).

select case when material in (
    select material
    from schema.material_table
    where old_material like 'UAD%'
  ) then 'Found'
  else 'Not Found'
end
from schema.material_table;

CodePudding user response：

I can see a couple of options. The most direct translation seems to be:

SELECT CASE
         WHEN m2.MATERIAL IS NOT NULL THEN 'Found'
         ELSE 'Not Found'
       END AS IZZIT_THERE
  FROM SCHEMA.MATERIAL_TABLE m2
  RIGHT OUTER JOIN SCHEMA.MATERIAL_TABLE m1
    ON m1.MATERIAL = m2.MATERIAL
  WHERE m1.OLD_MATERIAL LIKE 'UAD%'

but the use of a RIGHT OUTER JOIN may be unfamiliar. To switch to the more familiar LEFT OUTER JOIN we need to reverse the position of the tables in the query and alter how the conditions are presented:

SELECT CASE
         WHEN m1.MATERIAL IS NOT NULL THEN 'Found'
         ELSE 'Not Found'
       END AS IZZIT_THERE
  FROM SCHEMA.MATERIAL_TABLE m1
  LEFT OUTER JOIN SCHEMA.MATERIAL_TABLE m2
    ON m2.MATERIAL = m1.MATERIAL
  WHERE m1.OLD_MATERIAL LIKE 'UAD%'

I kept the aliases the same so you can see how they moved around in the query. In both queries m1 is the primary table, that is, it's the one that must provide data, while m2 is the secondary or "optional" table - it may or may not have data which matches the primary.

Personally, I prefer joins over subqueries as I find them easier to understand, but YMMV.