检索表交集的标准 SQL 查询是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/148795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the standard SQL Query to retrieve the intersection of tables?
提问by Peter Turner
Selecting the union:
选择工会：
select * from table1
union
select * from table1_backup
What is the query to select the intersection?
选择交点的查询是什么？
回答by Tom Ritter
回答by workmad3
SELECT *
FROM table1
WHERE EXISTS
(SELECT *
FROM table1_backup
WHERE table1.pk = table1_backup.pk)
works
作品
回答by Jeremy Wadhams
For questions like this, I tend to go back to this visual resource:
对于这样的问题，我倾向于回到这个视觉资源：
回答by cesar
here is a solution for mySQL:
这是 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;
Basically you have an table of mathematical subsets (ie. 1={1, 2 ,3}, 2={3, 4, 2}, ... , n={1, 4, 7}) with an attribute id, which is the set number, and fk_ id, which references a PRIMARY KEY of a table of elements, the superset (meaning possible values for the numbers in the curly braces). For those not mathematically inclined, let's pretend you have a table, 'other_ table', which is a list of items, and another table, 'another_ table', which is a list of transaction numbers, and both tables form a many-to-many relationship, thus producing 'table1'. now let's pretend you wanted to know the id's in 'another_ table' which had items 1, 2, and 3. that's the query to do it.
基本上你有一个带有属性 id 的数学子集表（即 1={1, 2 ,3}, 2={3, 4, 2}, ... , n={1, 4, 7}），这是集合编号，以及 fk_id，它引用元素表的 PRIMARY KEY，即超集（意思是花括号中数字的可能值）。对于那些不喜欢数学的人，让我们假设你有一个表，“other_table”，它是一个项目列表，另一个表，“another_table”，它是一个交易编号列表，两个表形成一个多对-许多关系，从而产生'table1'。现在让我们假设您想知道具有项目 1、2 和 3 的“another_ table”中的 id。这就是执行此操作的查询。
回答by stefano m
inner join i think: suppose T1 and T2 have the same structure:
内连接我认为：假设 T1 和 T2 具有相同的结构：
select T1.* from T1 inner join T2 on T1.pkField = T2.pkField
在 T1.pkField = T2.pkField 上从 T1 内连接 T2 中选择 T1.*
回答by Walter Mitty
"intersect" is also part of standard SQL.
“intersect”也是标准 SQL 的一部分。
Inner join gives a different answer.
内连接给出了不同的答案。
回答by ovais.tariq
An intersect on two identical tables a and b can be done in this manner:
两个相同的表 a 和 b 上的相交可以通过这种方式完成：
SELECT a.id, a.name
FROM a INNER JOIN b
USING (id, name)
回答by Stephen Wuebker
subqueries?! really?
子查询？！真的吗？
to get the intersection of table1 and table2:
获取 table1 和 table2 的交集：
SELECT * FROM table1, table2 WHERE table1.pk=table2.pk;
回答by James Curran
select distinct * from (select * from table1 union select * from table1_backup)
