INSERT INTO clients (client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers s
WHERE NOT EXISTS (
SELECT 1 FROM clients c WHERE c.client_id = s.supplier_id
);
原理: 對每一行 `suppliers`,檢查 `clients` 是否已存在相同 `client_id`。不存在則插入。原子性由單條 SQL 保證 (無競態)。
CREATE TABLE books (
bookid SMALLINT NOT NULL PRIMARY KEY,
booktitle VARCHAR(60) NOT NULL,
copyright YEAR NOT NULL
) ENGINE=InnoDB;
INSERT INTO books VALUES
(12786, 'Java', 1934), (13331, 'MySQL', 1919), (14356, 'PHP教程', 1966),
(15729, 'Perl', 1932), (16284, 'Oracle', 1996), (17695, 'PL/SQL', 1980),
(19264, 'JavaScript', 1992), (19354, 'www.java2s.com', 1993);
CREATE TABLE authors (
authid SMALLINT NOT NULL PRIMARY KEY,
authfn VARCHAR(20), authmn VARCHAR(20), authln VARCHAR(20)
) ENGINE=InnoDB;
INSERT INTO authors VALUES
(1006, 'H', 'S.', 'T'), (1007, 'J', 'C', 'O'), (1008, 'B', NULL, 'E'),
(1009, 'R', 'M', 'R'), (1010, 'J', 'K', 'T'), (1011, 'J', 'G.', 'N'),
(1012, 'A', NULL, 'P'), (1013, 'A', NULL, 'W'), (1014, 'N', NULL, 'A');
CREATE TABLE authorbook (
authid SMALLINT NOT NULL,
bookid SMALLINT NOT NULL,
PRIMARY KEY (authid, bookid),
FOREIGN KEY (authid) REFERENCES authors(authid),
FOREIGN KEY (bookid) REFERENCES books(bookid)
) ENGINE=InnoDB;
INSERT INTO authorbook VALUES
(1006, 14356), (1008, 15729), (1009, 12786), (1010, 17695),
(1011, 15729), (1012, 19264), (1012, 19354), (1014, 16284);
SELECT bookid, booktitle
FROM books b
WHERE NOT EXISTS (
SELECT 1 FROM authorbook ab WHERE ab.bookid = b.bookid
)
ORDER BY booktitle;
-- 結果:只有 bookid=13331 'MySQL' (無作者關聯)
SELECT b.bookid, b.booktitle
FROM books b
WHERE EXISTS (
SELECT 1 FROM authorbook ab WHERE ab.bookid = b.bookid
)
ORDER BY b.booktitle;
SELECT a.authid, a.authfn, a.authln
FROM authors a
WHERE NOT EXISTS (
SELECT 1 FROM authorbook ab WHERE ab.authid = a.authid
);
-- 結果:authid 1007, 1013 (J.C.O 和 A.W)
| 操作 | EXISTS | IN | JOIN |
|---|---|---|---|
| 語義 | 存在性檢查 (半連接) | 集合成員檢查 | 連接後過濾 |
| NULL 處理 | 安全 (忽略 NULL) | **陷阱**:`NOT IN (含 NULL)` 永遠為空 | 需額外條件 |
| 子查詢優化 | 優化器常轉為 Semi-Join | 同 EXISTS | 可能產生重複行需 DISTINCT |
| 適用場景 | **防重複插入、檢查存在性、差集** | 值列表較小、靜態 | 需要獲取關聯表列數據 |
-- 危險!若 subquery 返回含 NULL,整個 NOT IN 結果為空集
SELECT * FROM books WHERE bookid NOT IN (SELECT bookid FROM authorbook WHERE authid = 1008);
-- 如果 authorbook 有 NULL bookid (雖然 PK 不允許),NOT IN 失效
-- 安全寫法:NOT EXISTS 或 NOT IN 加非空條件
SELECT * FROM books WHERE bookid NOT IN (SELECT bookid FROM authorbook WHERE authid = 1008 AND bookid IS NOT NULL);
-- 或
SELECT * FROM books b WHERE NOT EXISTS (SELECT 1 FROM authorbook ab WHERE ab.bookid = b.bookid AND ab.authid = 1008);
-- MySQL 8.0+ 推薦 ON DUPLICATE KEY UPDATE 或 INSERT ... ON CONFLICT (PostgreSQL)
-- 僅用標準 SQL (如較老版本) 時:
INSERT INTO stats (date, cnt)
SELECT '2023-10-01', 1
WHERE NOT EXISTS (SELECT 1 FROM stats WHERE date = '2023-10-01');
-- 或配合觸發器/存儲過程實現更複雜邏輯