SQL:同じテーブルに格納されている値をサブクエリ(副問合せ)で結合して取得する

同じテーブルに値が2レコードとして保存されていて、クエリで1レコードとして取得する方法をすぐ忘れてしまうのでメモです。

具体的なクエリは以下のとおりです。

SELECT table1.id AS table1_id, tmp.tmp_id
FROM table1
LEFT OUTER JOIN (
  SELECT id AS tmp_id, column2 AS tmp_column2, column4 AS tmp_column4 FROM table1 
  WHERE table1.column1 = 1) AS tmp
ON table1.column2 = tmp.tmp_column2
WHERE table1.column3 = 'ABC' AND tmp.tmp_column4 = 'DEF'; 

JOINするのは、別々のテーブルのレコードを1レコードで取得するのと同じです。
JOINするテーブルに、サブクエリ(副問合せ)の結果を用いるのが特徴です。
サブクエリ(副問合せ)で、結合する結果だけを取得して、別々のテーブルのレコードを結合するのと同じ動作にしています。

photo credit: Секул Камберов Sekul Kamberov Microsoft sql server management studio via photopin (license)

同一のテーブルなので、テーブル名にASで別名をつけています。
同一のテーブルなので当然カラム名も同一なので、ASで別名をつけるか取得するカラムを指定しないと、同一カラムが2カラムになってしまいます。
この点を忘れていると、クエリの結果を利用するプログラム側で、想定外の値が取得できて悩むことになります。

WHEREでの絞り込みは、結合の基準となるテーブルのテーブル名(table1)か、JOINするテーブルのサブクエリ(副問合せ)の結果の別名(tmp1)を指定すればできます。
同一のテーブルなので、どのように指定するのか一瞬迷いそうです。
サブクエリ(副問合せ)で絞り込んだ方がわかりやすいかもしれません。