Join on Substring

Join on a value as a substring, e.g.

WITH

 with_table0 AS (

 SELECT

   0 AS value

 UNION ALL

 SELECT

   1 AS value

 UNION ALL

 SELECT

   2 AS value ),

 with_table1 AS (

 SELECT

   '1' AS value

 UNION ALL

 SELECT

   '2' AS value

 UNION ALL

 SELECT

   '1, 2' AS value )

SELECT

 with_table0.value AS table_0,

 with_table1.value AS table_1

FROM

 with_table0

LEFT JOIN

 with_table1

ON

 INSTR(with_table1.value,CAST(with_table0.value AS string)) > 0

Join Secondary on Any

If the first join is NULL, a second JOIN can create a match for those with NULL.

Join on a value, join rest on any value, e.g.

WITH

with_inputs AS (

 SELECT

   4 AS warehouse,

   'complete' AS status,

   11 qty

 UNION ALL

 SELECT

   4 AS warehouse,

   'started' AS status,

   12 qty

 UNION ALL

 SELECT

   5 AS warehouse,

   'complete' AS status,

   13 qty

 UNION ALL

 SELECT

   5 AS warehouse,

   'started' AS status,

   14 qty

 UNION ALL

 SELECT

   6 AS warehouse,

   'complete' AS status,

   15 qty

 UNION ALL

 SELECT

   6 AS warehouse,

   'started' AS status,

   16 qty ),

 with_maps AS (

 SELECT

   4 AS warehouse,

   'complete' AS status,

   '4_complete' output

 UNION ALL

 SELECT

   4 AS warehouse,

   'any' AS status,

   '4_any' output

 UNION ALL

 SELECT

   5 AS warehouse,

   'any' AS status,

   '5_any' output)

SELECT

 with_inputs.*,

 true_maps.status AS true_map_status,

 true_maps.output AS true_map_output,

 any_maps.status AS any_map_status,

 any_maps.output AS any_map_output,

 COALESCE(true_maps.output,any_maps.output,'n/a') AS final_output

FROM

 with_inputs

LEFT JOIN

 with_maps AS true_maps

ON

 true_maps.warehouse = with_inputs.warehouse

 AND true_maps.status = with_inputs.status

LEFT JOIN

 with_maps AS any_maps

ON

 true_maps.output IS NULL

 AND any_maps.status = 'any'

 AND any_maps.warehouse = with_inputs.warehouse;

Full Join

WITH

 aaa AS (

 SELECT

   'a' AS type,

   11 AS value

 UNION ALL

 SELECT

   'b' AS type,

   12 AS value ),

 bbb AS (

 SELECT

   'a' AS type,

   21 AS value

 UNION ALL

 SELECT

   'c' AS type,

   23 AS value )

SELECT

 IFNULL(aaa.type,bbb.type) AS type,

 aaa.value AS value_aaa,

 bbb.value AS value_bbb

FROM

 aaa

FULL JOIN

 bbb

ON

 bbb.type = aaa.type;

Cross Join

WITH

  aaa AS (

  SELECT

    'a' AS type_aaa,

    11 AS value_aaa

  UNION ALL

  SELECT

    'b' AS type_aaa,

    12 AS value_aaa ),

  bbb AS (

  SELECT

    'a' AS type_bbb,

    21 AS value_bbb

  UNION ALL

  SELECT

    'c' AS type_bbb,

    23 AS value_bbb )

SELECT

  aaa.*,

  bbb.*

FROM

  aaa

CROSS JOIN

  bbb;