PHP レッスン — レッスン 3.6 — MySQL DB の操作。JOIN 演算子の種類。

MySQL では、JOIN による抽出をさまざまな方法で行えます。これらすべての種類のクエリを取り上げていきます。JOIN を伴うすべてのクエリのリストです:

1. INNER JOIN
2. LEFT JOIN
3. 右のテーブルとの交差なしの LEFT JOIN
4. RIGHT JOIN
5. 左のテーブルとの交差なしの RIGHT JOIN
6. FULL OUTER
7. 左または右のテーブルが空の FULL OUTER

これらの JOIN の種類の図解です:

記事に私たちのサイトのファイルを添付します。その中には join.php があり、そこでさまざまな JOIN 演算子を使ってすべてのレコードを出力します。

INNER JOIN

この INNER JOIN 演算子から始めます。クエリで単に JOIN と書くと、この演算子がデフォルトで働くからです。この演算子は、ON 演算子の後に続く条件が満たされる、2 つのテーブルからのすべてのレコードを選びます。私たちには Files と Messages という 2 つのテーブルがあります:

Messages テーブル:

 Files テーブル:

JOIN を使ったクエリは次のとおりです:

SELECT * FROM Messages INNER JOIN Files ON Messages.fid=Files.fid

その結果、次のレコードが出力されます

つまり、fid が一致するところで、mysql はそれらの行を出力します。

LEFT JOIN

LEFT JOIN では、左のテーブル(私たちの場合は Messages)にあるすべてのレコードを、それらの fid 値が Files テーブルにあるレコードも含めて出力します。

Messages テーブル:

 Files テーブル:

LEFT JOIN を使ったクエリは次のとおりです:

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid

その結果、次のレコードが出力されます

LEFT JOIN は、すべてのメッセージのレコードを出力し、添付ファイルがあるかどうかは PHP 側で確認する、という場合に必要になります。

右のテーブルとの交差なしの LEFT JOIN

この LEFT JOIN は、右のテーブルで fid が一致するものを除く、左のテーブルからのすべてのレコードを出力します。

Messages テーブル:

 Files テーブル:

交差なしの LEFT JOIN を使ったクエリは次のとおりです:

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid WHERE Files.fid IS NULL

その結果、次のような抽出が得られます:

交差なしの LEFT JOIN は、添付ファイルのないすべてのレコードを出力する場合に必要になります。

RIGHT JOIN

RIGHT JOIN は、右のテーブルからすべてのレコードを出力し、交差があれば左のテーブルのデータも出力します。

Messages テーブル:

 Files テーブル:

RIGHT JOIN を使ったクエリは次のとおりです:

SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid

その結果、次のような抽出が得られます:

RIGHT JOIN は、使われているかどうかに関係なく、すべての添付ファイル、つまり単にすべてのファイルを出力する場合に必要になります。

交差なしの RIGHT JOIN

交差なしの RIGHT JOIN は、左のテーブルとの交差があるものを除く、右のテーブルからのすべてのレコードを出力します。

Messages テーブル:

Files テーブル:

交差なしの RIGHT JOIN を使ったクエリは次のとおりです:

SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid WHERE Messages.fid IS NULL

こうして、次のデータが得られます:

この RIGHT JOIN は、どのメッセージにも添付されていないすべての添付ファイルを出力する場合に必要になります。例えば、使われていないファイルを出力したい場合です。

FULL OUTER JOIN

SQL 言語には FULL OUTER JOIN がありますが、MySQL にはこの演算子はありません。というのも、このような演算子はサーバーに大きな負荷をかけるからです。今、私たちには 3 つのファイルと 3 つのメッセージがあり、クエリの実行結果として 4 行ができます。LEFT JOIN と RIGHT JOIN の 2 つのクエリを合わせたものを与えるクエリを書くのが良い考えかどうか、私には確信が持てません。それでも、FULL OUTER JOIN クエリをエミュレートすることは可能です。

Messages テーブル:

Files テーブル:

FULL OUTER JOIN のクエリのエミュレーションは次のとおりです:

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid

このクエリでは、LEFT JOIN と RIGHT JOIN の 2 つのクエリを結合するために UNION 演算子を使います。

その結果、次のレコードが得られます:

そして、ここでは FULL OUTER JOIN が何のために必要かを言うのは難しいです。しかし SQL にある以上、後で必要になるのでしょう。

交差なしの FULL OUTER JOIN

単なる FULL OUTER JOIN よりさらに突飛な、もう 1 つの JOIN の種類、すなわち交差なしの FULL OUTER JOIN があります。この JOIN の種類をどこで使えるか、私は提案すらできません。というのも、結果として、使われていないファイルと、ファイルのないメッセージが得られるからです。そして、おそらくもうお察しのとおり、この演算子も MySQL にはありません。交差なしの LEFT JOIN と交差なしの RIGHT JOIN の 2 つの演算子でエミュレートするしかありません。

交差なしの FULL OUTER JOIN クエリのエミュレーション:

$sql = 'SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid WHERE Files.fid IS NULL 
UNION 
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid WHERE Messages.fid IS NULL';

その結果(元のテーブルは FULL OUTER JOIN の例と同じ)、次が得られます:

ひとまずこれで全部です。次のレッスンでは、複数のテーブルに対して同時に、さらに複雑なクエリを書き始めます。