SQL EXISTS

EXISTS 經常搭配子查詢，用來判斷是否存在至少一筆符合條件的紀錄。它很適合做「有沒有下單」、「有沒有明細」這類判斷。

SELECT CustomerName
FROM Customers c
WHERE EXISTS (
  SELECT 1 FROM Orders o
  WHERE o.CustomerID = c.CustomerID
);

SELECT CustomerName
FROM Customers c
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o
  WHERE o.CustomerID = c.CustomerID
);

SELECT CustomerName
FROM Customers c
WHERE EXISTS (
  SELECT 1 FROM Orders o
  WHERE o.CustomerID = c.CustomerID
    AND o.Freight > 100
);

SELECT CategoryName
FROM Categories c
WHERE EXISTS (
  SELECT 1 FROM Products p
  WHERE p.CategoryID = c.CategoryID
);