2022.12.08

【SQL】JOIN, IN と EXISTSの使い方

minn

Contents

はじめに
IN演算子
EXISTS演算子
JOIN句
まとめ

はじめに

こんにちは、ミンです。
今日はSQLの JOIN, IN と EXISTS 文の使い方について書いてみようと思います。
＊この記事のサンプルでは MySQL を使用しています。

それでは実際に試してみたいので2つのテーブルを準備します。
テーブル作成のSQL文は次のようになります。

CREATE DATABASE test_db;
USE test_db;

CREATE TABLE customers ( customer_id int, customer_name varchar(255) NOT NULL, city varchar(255) );
CREATE TABLE orders ( order_id int, customer_id int, order_date varchar(255) );

INSERT INTO customers (customer_id, customer_name, City) VALUES 
(1, 'John',  'Tokyo'),
(2, 'William',  'Osaka'),
(3, 'David',  'Osaka') ;

INSERT INTO orders (order_id, customer_id, order_date) VALUES 
(1, 1,  '2022-11-01'),
(2, 2,  '2022-12-01') ;

準備できていることを確認。

SELECT * FROM customers;
SELECT * FROM orders;

IN演算子

WHERE句の条件に複数の値を指定したい場合に主に「IN」を使います。
複数の「OR 」条件の代わりに「IN」を使えるので便利です。
「IN」演算子の否定形は「NOT IN」になります

構文：

SELECT col_name(s)
FROM Table_name
WHERE col_name IN(val1, val2, val3, …..)

例：
city='Tokyo'レコードのcustomer_nameを取得。

SELECT customer_name
FROM customers
WHERE city IN('Tokyo');

city='Tokyo'のレコードが抽出されます。

EXISTS演算子

ネストされたサブクエリに条件にマッチするレコードが存在するか判定したい時に使います。
条件にマッチするレコードが1つ以上存在する場合は Boolean TRUE、存在しない場合は FALSEを返します。
「IN」演算子の否定形と同じように、「EXISTS」演算子の否定形は「NOT EXISTS」になります。

構文：

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE ’条件’);

例：
ordersテーブルにcustomer_idが存在するレコードに対してcustomersテーブルからcustomer_id, customer_name, cityを取得。

SELECT customer_id, customer_name, city
FROM customers
WHERE EXISTS
(SELECT * FROM orders WHERE orders.customer_id = customers.customer_id);

ここでcustomer_id = 3のDavidさんのレコードはordersテーブルに存在しないため抽出されてないことがわかります。

JOIN句

2つ以上のテーブルに関連づいているカラムを軸に結合し、それぞれのテーブルからタプルまたはレコードを連結してデータ抽出する時に使います。
両方のテーブルにレコードが存在しない場合は NULL 値を返します。
SQL には、基本的に INNER JOIN, OUTER JOIN, CROSS JOIN と SELF JOIN の4 種類の JOINS があります。

1. INNER JOIN:

結合する両方のテーブルにONで指定した条件にマッチする値またはレコードのみ返します。
構文：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

例：
customersテーブルとordersテーブルからcustomer_idが一致するレコードを取得。

SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

2. OUTER JOIN

LEFT JOIN / LEFT OUTER JOIN

左側のテーブルの全レコードと右側のテーブルからONで指定した条件にマッチするレコードのみ返します。
構文：

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

例：
左側customersテーブルの全レコードと右側ordersテーブルから関連するカラムcustomer_idが一致するレコードのみを組み合わせて取得。

SELECT *
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

RIGHT JOIN / RIGHT OUTER JOIN

左側のテーブルからONで指定した条件にマッチするレコードのみと右側のテーブルの全レコード返します。
構文：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

例：
左側customersテーブルから関連するカラムcustomer_idが一致するレコードのみと右側ordersテーブルの全レコードとを組み合わせて取得。

SELECT *
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

FULL JOIN / FULL OUTER JOIN

JOINするテーブル両方から条件に一致するレコードのみ返します。
WHERE条件を付けていない場合は例のように両方のテーブルから関連しているレコードの組み合わせが抽出されます。
構文：

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE ’条件’;

例：
WHERE条件なしでcustomersテーブルとordersテーブルから全レコードを取得。

SELECT customers.customer_name, orders.order_id
FROM customers
FULL OUTER JOIN orders ON Customers.CustomerID=orders.CustomerID
ORDER BY customers.customer_name;

MySQLではFULL OUTER JOIN がサポートされていません。
この場合は JOIN, UNION とLEFT JOINを合わせてデータを取得する必要があります。ただし、重複しているレコードも取得されてしまうので注意が必要です。
WHERE条件を付けていないため以下のように関連するレコードの組み合わせが抽出されます。
例（MySQL構文）：

SELECT * FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT * FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

3. CROSS JOIN

JOINする2つのテーブルから各行を全て返します。
関連していない2つのテーブル1つのテーブルにまとめてデータ抽出したい時は便利です。
注意：CROSS JOINは左右のテーブルから片方だけにレコードが存在することに限らず全てのレコードを返すのでデータの量が多すぎてパフォーマンスに影響してしまうケースがあります。
構文：

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

例：
customersテーブルとordersテーブルからレコード各行を全て取得。

SELECT customer_name, city, order_id, order_date 
FROM customers
CROSS JOIN orders;

4. SELF JOIN

同一のテーブルをJOINして条件にマッチするレコードのみ返します。
構文：

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE ’条件’;

例：
customersテーブルから同じcityでcustomer_nameが違うレコードのみ取得。

SELECT T1.customer_name AS customer_name1, T2.customer_name AS customer_name2, T1.city
FROM customers T1, customers T2
WHERE T1.customer_name <> T2.customer_name
AND T1.city = T2.city;

まとめ

「JOIN」は2つ以上のテーブルを結合して1つのテーブルとしてデータを抽出できます。
「IN」は複数の「OR」演算子と同様にWHERE句の条件に複数の値を指定してマッチする全レコードを抽出できます。
「EXISTS」はサブクエリで別のテーブルに条件にマッチするレコードの存在有無のチェック結果を基に必要なデータを抽出できます。

このようにSQLの JOIN, IN と EXISTS の使い方と主な違いを識別しておくと有効なSQL 文の作成に役に立つと思います。

#サーバサイド

AUTHOR