检索表的交集的标准SQL查询是什么?

intersect

select * from table1 
intersect
select * from table1_backup

SELECT *
FROM table1
WHERE EXISTS
(SELECT *
FROM table1_backup
WHERE table1.pk = table1_backup.pk)

作品

对于这样的问题,我倾向于回到这个可视化资源:

A Visual Explanation of SQL Joins

我认为内在的联结: 假设T1和T2具有相同的结构:

从中选择T1.* T1.pkField=T2.pkField上的T1内部连接T2

以下是mySQL的解决方案:

CREATE TABLE table1(
id INT(10),
fk_id INT(10),
PRIMARY KEY (id, fk_id),
FOREIGN KEY table1(id) REFERENCES another_table(id),
FOREIGN KEY table1(fk_id) REFERENCES other_table(id)
);

SELECT table1.* FROM table1 as t0
INNER JOIN table1 as a ON (t0.id = a.id and fk_id=1)
INNER JOIN table1 as b ON (t0.id = b.id and fk_id=2)
INNER JOIN table1 as c ON (t0.id = c.id and fk_id=3)
ORDER BY table1.id;

在两个相同的表a和表b上的交集可以这样做:

SELECT a.id, a.name
FROM a INNER JOIN b
USING (id, name)

子查询?!真的?

要获得表1和表2的交集:

SELECT * FROM table1, table2 WHERE table1.pk=table2.pk;

select distinct * from (select * from table1 union select * from table1_backup)