https://twitter.com/mamy1326/status/1444988083426889728

left_join_on_where.php

<?php

function t2_in_1_3($buff)
{
  if ($buff["t2"] == 1 || $buff["t2"] == 3)
    return true;
  return false;
}

$t1= array(1, 2, 3);
$t2= array(1, 2, 3);

// -----

/*
-- INNER JOIN で WHERE
SELECT * FROM t1 JOIN t1 AS t2
ON t1.num = t2.num
WHERE t2.num IN (1, 3);
*/

$join_buffer= [];
foreach ($t1 as $t1_row)
{
  foreach ($t2 as $t2_row)
  {
    // ON句
    if ($t1_row == $t2_row)
    {
      array_push($join_buffer, array("t1" => $t1_row, "t2" => $t2_row));
    }
  }
}

// WHERE句
$result_buffer= array_filter($join_buffer, "t2_in_1_3");

var_dump($result_buffer);

// ----


/*
-- INNER JOIN で ON
SELECT * FROM t1 JOIN t1 AS t2
ON t1.num = t2.num AND t2.num IN (1, 3);
*/

$join_buffer= [];

foreach ($t1 as $t1_row)
{
  foreach ($t2 as $t2_row)
  {
    // ON句
    if ($t1_row == $t2_row && ($t1_row == 1 || $t1_row == 3))
    {
      array_push($join_buffer, array("t1" => $t1_row, "t2" => $t2_row));
    }
  }
}
$result_buffer= $join_buffer;

var_dump($result_buffer);

// ----

/*
-- LEFT JOIN の右側で WHERE
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON t1.num = t2.num
WHERE t2.num IN (1, 3);
*/

$join_buffer= [];
foreach ($t1 as $t1_row)
{
  $t2_not_exist= 1;

  foreach ($t2 as $t2_row)
  {
    // ON句
    if ($t1_row == $t2_row)
    {
      array_push($join_buffer, array("t1" => $t1_row, "t2" => $t2_row));
    }
    else
    {
      // OUTER JOINのNULLを詰め込む
      array_push($join_buffer, array("t1" => $t1_row, "t2" => "NULL"));
    }
  }

  if ($t2_not_exist)
  {
    // OUTER JOINのNULLを詰め込む
    array_push($join_buffer, array("t1" => $t1_row, "t2" => "NULL"));
  }
}
// WHERE句
$result_buffer= array_filter($join_buffer, "t2_in_1_3");

var_dump($result_buffer);

// ----

/*
-- LEFT JOIN の右側で ON
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON t1.num = t2.num AND t2.num IN (1, 3);
*/

$join_buffer= [];
foreach ($t1 as $t1_row)
{
  $t2_not_exist= 1;

  foreach ($t2 as $t2_row)
  {
    // ON句
    if ($t1_row == $t2_row && ($t1_row == 1 || $t1_row == 3))
    {
      array_push($join_buffer, array("t1" => $t1_row, "t2" => $t2_row));
      $t2_not_exist= 0;
    }
  }
  
  if ($t2_not_exist)
  {
    // OUTER JOINのNULLを詰め込む
    array_push($join_buffer, array("t1" => $t1_row, "t2" => "NULL"));
  }
}
$result_buffer= $join_buffer;

var_dump($result_buffer);