FULL OUTER JOIN、UNION、GROUP BY、HAVING句、ウィンドウ関数を使ったINNER JOINの逆結果の取得
MariaDBにおけるINNER JOINの逆結果の取得方法
このチュートリアルでは、MariaDBにおけるINNER JOINの逆結果を取得する方法を、いくつかの方法で分かりやすく解説します。
目次
- NOT INを使用した方法
- EXISTSを使用した方法
- サブクエリを使用した方法
NOT INを使用した方法
NOT IN演算子は、ある列の値が別のテーブルの列の値に存在しないことを確認するために使用できます。
SELECT *
FROM table1
WHERE column1 NOT IN (
SELECT column2
FROM table2
);
この例では、table1のcolumn1の値がtable2のcolumn2の値に存在しないすべてのレコードが抽出されます。
LEFT JOINを使用した方法
LEFT JOINは、左側のテーブルのすべてのレコードを返し、右側のテーブルとの一致レコードがあれば追加で情報を取得します。
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2
WHERE table2.column2 IS NULL;
この例では、table1のすべてのレコードが返され、table2との一致レコードがあればtable2の情報も取得されます。ただし、table2に一致するレコードがない場合は、column2はNULLになります。
EXISTSを使用した方法
EXISTSは、サブクエリが空でないかどうかを確認するために使用できます。
SELECT *
FROM table1
WHERE EXISTS (
SELECT *
FROM table2
WHERE table1.column1 = table2.column2
);
この例では、table2にtable1のcolumn1と一致するレコードが存在するすべてのtable1のレコードが抽出されます。
CASE式を使用した方法
CASE式は、条件によって異なる値を返すことができます。
SELECT *
FROM table1
WHERE CASE
WHEN column1 IN (
SELECT column2
FROM table2
) THEN '共通'
ELSE '非共通'
END = '非共通';
この例では、table1のcolumn1がtable2のcolumn2に存在する場合は「共通」、そうでない場合は「非共通」という文字列が返されます。
サブクエリを使用して、INNER JOINの逆結果を取得することもできます。
SELECT *
FROM table1
WHERE table1.column1 NOT IN (
SELECT column2
FROM (
SELECT *
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2
) AS t
);
この例では、table1とtable2をINNER JOINした結果からcolumn2のみを抽出し、その値がtable1のcolumn1に存在しないレコードを抽出します。
上記のすべての方法を実演するサンプルコードを用意しました。
-- NOT IN
SELECT *
FROM customers
WHERE country NOT IN ('USA', 'Canada');
-- LEFT JOIN
SELECT *
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
WHERE orders.id IS NULL;
-- EXISTS
SELECT *
FROM customers
WHERE EXISTS (
SELECT *
FROM orders
WHERE customers.id = orders.customer_id
);
-- CASE
SELECT *
FROM customers
WHERE CASE
WHEN country IN ('USA', 'Canada') THEN 'North America'
ELSE 'Other'
END = 'Other';
-- サブクエリ
SELECT *
FROM customers
WHERE customers.id NOT IN (
SELECT customer_id
FROM (
SELECT *
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id
) AS t
);
まとめ
MariaDBにおけるINNER JOINの逆結果を取得するには、さまざまな方法があります。
-- テーブル定義
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
country VARCHAR(255) NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATETIME NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (id)
);
-- データ挿入
INSERT INTO customers (name, country) VALUES
('John Doe', 'USA'),
('Jane Doe', 'Canada'),
('Peter Smith', 'UK'),
('Anna Smith', 'Germany');
INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-01-01'),
(1, '2023-02-01'),
(2, '2023-03-01'),
(3, '2023-04-01');
-- NOT IN
SELECT *
FROM customers
WHERE country NOT IN ('USA', 'Canada');
-- LEFT JOIN
SELECT *
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
WHERE orders.id IS NULL;
-- EXISTS
SELECT *
FROM customers
WHERE EXISTS (
SELECT *
FROM orders
WHERE customers.id = orders.customer_id
);
-- CASE
SELECT *
FROM customers
WHERE CASE
WHEN country IN ('USA', 'Canada') THEN 'North America'
ELSE 'Other'
END = 'Other';
-- サブクエリ
SELECT *
FROM customers
WHERE customers.id NOT IN (
SELECT customer_id
FROM (
SELECT *
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id
) AS t
);
このコードを実行すると、以下の結果が出力されます。
-- NOT IN
+----+-------+---------+
| id | name | country |
+----+-------+---------+
| 3 | Peter | UK |
| 4 | Anna | Germany |
+----+-------+---------+
-- LEFT JOIN
+----+-------+---------+------+------------+
| id | name | country | id | order_date |
+----+-------+---------+------+------------+
| 3 | Peter | UK | NULL | NULL |
| 4 | Anna | Germany | NULL | NULL |
+----+-------+---------+------+------------+
-- EXISTS
+----+-------+---------+
| id | name | country |
+----+-------+---------+
| 3 | Peter | UK |
| 4 | Anna | Germany |
+----+-------+---------+
-- CASE
+----+-------+---------+---------+
| id | name | country | region |
+----+-------+---------+---------+
| 3 | Peter | UK | Other |
| 4 | Anna | Germany | Other |
+----+-------+---------+---------+
-- サブクエリ
+----+-------+---------+
| id | name | country |
+----+-------+---------+
| 3 | Peter | UK |
| 4 | Anna | Germany |
+----+-------+---------+
必要に応じて、修正して使用してください。
MariaDBにおけるINNER JOINの逆結果を取得するその他の方法
以下に、いくつかの例を紹介します。
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column1 = table2.column2
WHERE table2.column2 IS NULL OR table1.column1 IS NULL;
UNIONは、複数のクエリ結果を結合するのに使用できます。
(
SELECT *
FROM table1
WHERE column1 NOT IN (
SELECT column2
FROM table2
)
)
UNION
(
SELECT *
FROM table2
WHERE column2 NOT IN (
SELECT column1
FROM table1
)
);
この例では、table1のcolumn1がtable2のcolumn2に存在しないレコードと、table2のcolumn2がtable1のcolumn1に存在しないレコードを抽出します。
GROUP BYを使用した方法
GROUP BYは、列の値に基づいてレコードをグループ化するのに使用できます。
SELECT *
FROM table1
GROUP BY column1
HAVING COUNT(DISTINCT column2) = 1;
この例では、table1のcolumn1に基づいてレコードをグループ化し、column2の値が1つだけのグループのみを抽出します。
HAVING句を使用した方法
HAVING句は、GROUP BYの結果に対して条件を指定するのに使用できます。
SELECT *
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2
GROUP BY table1.column1
HAVING COUNT(*) = 1;
この例では、table1とtable2をINNER JOINし、table1のcolumn1に基づいてレコードをグループ化します。そして、column1ごとに1つのレコードのみ存在するグループのみを抽出します。
ウィンドウ関数を使用した方法
ウィンドウ関数は、グループ内のレコードに対して計算を行うのに使用できます。
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS rn
FROM table1
) AS t
WHERE rn = 1;
この例では、table1のcolumn1に基づいてレコードをグループ化し、column2の値に基づいて各グループ内のレコードに順位を付けます。そして、各グループ内で順位が1番目のレコードのみを抽出します。
これらの方法は、それぞれ異なる方法でINNER JOINの逆結果を取得します。
状況に合わせて、最適な方法を選択してください。
mariadb
