SQL SQL从两个表中获取公共行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31541227/
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
SQL to get the common rows from two tables
提问by Ram
I have two tables T1 and T2.
我有两张桌子 T1 和 T2。
Can any one please help with a SQL query which will fetch the common rows from these two tables? (Assume T1 and T2 has 100 columns each)
任何人都可以帮助进行 SQL 查询，该查询将从这两个表中获取公共行吗？（假设 T1 和 T2 各有 100 列）
P.S : I guess INNER JOIN on each of the columns will not be a good idea.
PS：我猜每个列上的 INNER JOIN 都不是一个好主意。
Thanks
谢谢
回答by Himanshu Upadhyay
If you are using SQL Server 2005, then you can use Intersect Key word, which gives you common records.
如果您使用的是 SQL Server 2005，那么您可以使用 Intersect Key word，它为您提供常见的记录。
SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2
If you want in the output both column1 and column2 from table1 which has common columns1 in both tables.
如果您希望在两个表中具有公共列 1 的 table1 中的 column1 和 column2 输出。
SELECT column1, column2
FROM table1
WHERE column1 IN
(
SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2
)
回答by Hitesh Jain
SELECT NAME FROM Sample1
UNION
SELECT NAME FROM Sample2;
EX: Table Sample1
ID NAME
-------
1 A
-------
2 B
-------
Table Sample 2
ID NAME
--------
1 C
--------
2 B
------
Output
NAME
----
A
---
B
---
C
---
回答by Thisizraaz
Yes, INNER JOIN will work.
是的，INNER JOIN 会起作用。
eg. SELECT (column_1, column_2, ... column_n) FROM T1 JOIN T2 ON (condition) WHERE (condition)
例如。SELECT (column_1, column_2, ... column_n) FROM T1 JOIN T2 ON (condition) WHERE (condition)
This query will fetch the common records (intersection of) in both the tables according to ON condition.
此查询将根据 ON 条件获取两个表中的公共记录（交集）。
回答by Rohit Khatana
By using INTERSECT:
通过使用INTERSECT：
SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary
回答by René Nyffenegger
select
t1.*
from
t1, t2
where
(
(t1.col1 is null and t2.col1 is null) or (
(t1.col1 = t2.col1 )
) and
(
(t1.col2 is null and t2.col2 is null) or (
(t1.col2 = t2.col2 )
) and
(
(t1.col3 is null and t2.col3 is null) or (
(t1.col3 = t2.col3 )
) and
....
(
(t1.col100 is null and t2.col100 is null) or (
(t1.col100 = t2.col100 )
)
