SQLにおけるデータの有無確認：比較と考察

SQLにおけるNOT EXISTS、NOT IN、LEFT JOIN WHERE IS NULLの違い

SQLにおけるNOT EXISTS、NOT IN、LEFT JOIN WHERE IS NULLは、いずれも関連するテーブル間にあるデータの有無を確認するための構文ですが、それぞれ異なる動作と特徴を持っています。

詳細

NOT EXISTS

NOT EXISTSは、サブクエリで指定した条件に一致するレコードが存在しないかどうかを確認します。サブクエリで一致するレコードが存在しない場合、外側のクエリの該当行が返されます。

SELECT *
FROM Customers
WHERE NOT EXISTS (
  SELECT *
  FROM Orders
  WHERE Customers.CustomerID = Orders.CustomerID
);

上記の例では、OrdersテーブルにCustomerIDと一致するレコードが存在しないCustomersレコードをすべて返します。

NOT IN

NOT INは、指定した値リストに列の値が含まれていないかどうかを確認します。値リストのいずれかの値と一致する場合は、該当行が返されません。

SELECT *
FROM Customers
WHERE CustomerID NOT IN (1, 2, 3);

上記の例では、CustomerIDが1、2、3のCustomersレコードをすべて除外して返します。

LEFT JOIN WHERE IS NULL

LEFT JOIN WHERE IS NULLは、左側のテーブルのすべての行を返し、右側のテーブルから一致する行があれば結合します。一致する行がない場合は、右側の列にNULL値が格納されます。

SELECT *
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.CustomerID IS NULL;

比較

NOT EXISTS、NOT IN、LEFT JOIN WHERE IS NULLはそれぞれ異なる動作と特徴を持つため、状況に応じて適切な構文を選択することが重要です。

• 複雑なクエリの場合は、どの構文が最適か判断するのが難しい場合があります。そのような場合は、データベース管理者に相談することをおすすめします。
• それぞれの構文の性能は、データベースやクエリの複雑さによって異なる場合があります。

顧客テーブル (Customers) と注文テーブル (Orders) があり、以下の条件を満たす顧客レコードをすべて抽出したい。

• 顧客が注文したことのない顧客 (つまり、Ordersテーブルに一致するレコードがない顧客)

要件

テーブル構造

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(255)
);

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  OrderDate DATETIME,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

サンプルデータ

INSERT INTO Customers (CustomerID, CustomerName)
VALUES
  (1, 'John Doe'),
  (2, 'Jane Doe'),
  (3, 'Peter Jones'),
  (4, 'Mary Smith');

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES
  (1, 1, '2023-10-01'),
  (2, 2, '2023-11-15'),
  (3, 1, '2023-12-24'),
  (4, 3, '2024-01-10');

SELECT *
FROM Customers
WHERE NOT EXISTS (
  SELECT *
  FROM Orders
  WHERE Customers.CustomerID = Orders.CustomerID
);

結果

CustomerID | CustomerName
-----------+--------------
2          | Jane Doe
4          | Mary Smith

SELECT *
FROM Customers
WHERE CustomerID NOT IN (
  SELECT CustomerID
  FROM Orders
);

CustomerID | CustomerName
-----------+--------------
2          | Jane Doe
4          | Mary Smith

SELECT *
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.CustomerID IS NULL;

CustomerID | CustomerName
-----------+--------------
2          | Jane Doe
4          | Mary Smith

説明

• LEFT JOIN WHERE IS NULL: 左側のCustomersテーブルのすべての行を返し、右側のOrdersテーブルから一致する行があれば結合します。一致する行がない場合は、OrdersテーブルのCustomerID列にNULL値が格納されるため、WHERE句でNULL値を確認することで、注文したことのない顧客のみを抽出します。
• NOT IN: 指定した値リストに顧客IDが含まれていない顧客のみを抽出します。
• NOT EXISTS: サブクエリで一致するレコードが存在しない顧客のみを抽出します。

考察

3つの方法はいずれも同じ結果を返しますが、それぞれ異なる特徴があります。

• LEFT JOIN WHERE IS NULL: 可読性が高く、結合結果をそのまま表示できます。
• NOT IN: 値リストを直接指定できるため、シンプルでわかりやすいコードになります。
• NOT EXISTS: サブクエリを使用するため、複雑な条件式を記述できます。

COALESCE関数

COALESCE関数は、引数リストの最初の非NULL値を返します。すべての引数がNULLの場合は、NULLを返します。

SELECT *
FROM Customers
WHERE COALESCE((SELECT 1 FROM Orders WHERE Customers.CustomerID = Orders.CustomerID), 0) = 0;

CASE式

CASE式は、条件に応じて異なる値を返すことができます。

SELECT *
FROM Customers
WHERE CASE WHEN EXISTS (SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID) THEN 1 ELSE 0 END = 0;

ROW_NUMBER関数

ROW_NUMBER関数は、各行に固有のシーケンス番号を割り当てます。

SELECT *
FROM Customers
WHERE ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID) = 1
AND NOT EXISTS (
  SELECT *
  FROM Orders
  WHERE Customers.CustomerID = Orders.CustomerID
);

ウィンドウ関数

ウィンドウ関数は、特定の行グループにわたって集計計算を実行できます。

SELECT *
FROM Customers
WHERE NOT EXISTS (
  SELECT *
  FROM Orders
  WHERE Customers.CustomerID = Orders.CustomerID
)
WINDOW (PARTITION BY CustomerID ORDER BY CustomerID ROWS BETWEEN PRECEDING 1 AND CURRENT ROW);