-- Oracle 12c: Oracle Query using LATERAL Join syntax 
--
-- SELECT 
--   ta.idx, some_desc
-- FROM table_a ta
--   LATERAL (SELECT tb.some_desc
--            FROM  table_b tb
--            WHERE ta.idx = tb.idx);
--
-- Reference Query: City table (q_cty) with country table (q_tld)

SELECT
 continent,
 q_cty.city_desc,
 q_cty.cc_tld
FROM
(SELECT 'New York'     city_desc, 'US' cc_tld FROM DUAL UNION
 SELECT 'Ushuaia'      city_desc, 'AR' cc_tld FROM DUAL UNION
 SELECT 'Toronto'      city_desc, 'CA' cc_tld FROM DUAL UNION
 SELECT 'Los Angeles'  city_desc, 'US' cc_tld FROM DUAL
) q_cty,
 LATERAL(
    SELECT 
     q_tld.continent
    FROM
      (SELECT 'AR' cc_tld, 'South America' continent FROM DUAL UNION
       SELECT 'CA' cc_tld, 'North America' continent FROM DUAL UNION
       SELECT 'IN' cc_tld, 'Asia'          continent FROM DUAL UNION 
       SELECT 'US' cc_tld, 'North America' continent FROM DUAL
      ) q_tld
    WHERE  q_tld.cc_tld = q_cty.cc_tld
    AND    INSTR(q_tld.continent,'America') >0)
;

--> Query Output



-- Same query using WITH clause

WITH q_cty AS
 (SELECT 'New York'     city_desc, 'US' cc_tld FROM DUAL UNION
  SELECT 'Ushuaia'      city_desc, 'AR' cc_tld FROM DUAL UNION
  SELECT 'Toronto'      city_desc, 'CA' cc_tld FROM DUAL UNION
  SELECT 'Los Angeles'  city_desc, 'US' cc_tld FROM DUAL
 ),
 q_tld AS (
       SELECT 'AR' cc_tld, 'South America' continent FROM DUAL UNION
       SELECT 'CA' cc_tld, 'North America' continent FROM DUAL UNION
       SELECT 'IN' cc_tld, 'Asia'          continent FROM DUAL UNION 
       SELECT 'US' cc_tld, 'North America' continent FROM DUAL
      )
SELECT
 continent,
 q_cty.city_desc,
 q_cty.cc_tld
FROM q_cty,
 LATERAL (SELECT continent
          FROM  q_tld
          WHERE  q_tld.cc_tld = q_cty.cc_tld
          AND    INSTR(q_tld.continent,'America') >0
         )
;