让我们从以下两个表开始:
CREATE TABLE people AS (
SELECT * FROM VALUES
('david', 10),
('george', 20)
AS tmp(name, age)
);
CREATE TABLE position AS (
SELECT * FROM VALUES
('george', 'c++'),
('george', 'frontend')
AS tmp(name, job)
);
以下两种方法是写SEMI JOIN或ANTI JOIN模式的等效方法吗？如果没有，缺少什么？
[SEMI-JOIN == WHERE EXISTS]
SELECT * FROM people SEMI JOIN position ON (people.name=position.name)
SELECT * FROM people WHERE EXISTS (SELECT * FROM position WHERE people.name=position.name)
// or like this?
SELECT * FROM people WHERE name IN (SELECT name FROM position)
[ANTI-JOIN == WHERE NOT EXISTS]
SELECT * FROM people ANTI JOIN position ON (people.name=position.name)
SELECT * FROM people WHERE NOT EXISTS (SELECT * FROM position WHERE people.name=position.name)
// or like this?
SELECT * FROM people WHERE name NOT IN (SELECT name FROM position)