SQL 什么是数据库中的半连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42249690/
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 semi-join in database?
提问by DarkShadow
I am having trouble while trying to understand the concept of semi-join and how it is different from conventional join. I have tried some article already but not satisfied with the explanation, could someone please help me to understand it?
我在尝试理解半连接的概念以及它与传统连接的不同之处时遇到了麻烦。我已经尝试了一些文章但对解释不满意，有人可以帮助我理解吗？
回答by Iurii Ant
Simple example. Let's select students with grades using left outer join:
简单的例子。让我们使用左外连接选择有成绩的学生：
SELECT DISTINCT s.id
FROM students s
LEFT JOIN grades g ON g.student_id = s.id
WHERE g.student_id IS NOT NULL
Now the same with left semi-join:
现在与左半连接相同：
SELECT s.id
FROM students s
WHERE EXISTS (SELECT 1 FROM grades g
WHERE g.student_id = s.id)
The latter is much more efficient.
后者效率更高。
回答by Lukasz Szozda
As far as I know SQL dialects that support SEMIJOIN/ANTISEMIare U-SQL/Cloudera Impala.
据我所知，支持的 SQL 方言SEMIJOIN/ANTISEMI是 U-SQL/Cloudera Impala。
Semijoins are U-SQL's way filter a rowset based on the inclusion of its rows in another rowset.Other SQL dialects express this with the SELECT * FROM A WHERE A.key IN (SELECT B.key FROM B) pattern.
半连接：
半连接是 U-SQL 根据行集包含在另一个行集中的行集来过滤行集的方式。其他 SQL 方言用 SELECT * FROM A WHERE A.key IN (SELECT B.key FROM B) 模式表达了这一点。
More info Semi Join and Anti Join Should Have Their Own Syntax in SQL:
更多信息Semi Join 和 Anti Join 在 SQL 中应该有自己的语法：
“Semi” means that we don't really join the right hand side, we only check if a join would yield results for any given tuple.
“半”意味着我们并没有真正连接右侧，我们只检查连接是否会为任何给定的元组产生结果。
-- IN
SELECT *
FROM Employee
WHERE DeptName IN (
SELECT DeptName
FROM Dept
)
-- EXISTS
SELECT *
FROM Employee
WHERE EXISTS (
SELECT 1
FROM Dept
WHERE Employee.DeptName = Dept.DeptName
)
EDIT:
编辑：
Another dialect that supports SEMI/ANTISEMI join is KQL:
另一种支持 SEMI/ANTISEMI 连接的方言是KQL：
kind=leftsemi(or kind=rightsemi)
Returns all the records from the left side that have matches from the right. The result table contains columns from the left side only.
kind=leftsemi（或 kind=rightsemi）
从左侧返回与右侧匹配的所有记录。结果表仅包含左侧的列。
let t1 = datatable(key:long, value:string)
[1, "a",
2, "b",
3, "c"];
let t2 = datatable(key:long)
[1,3];
t1 | join kind=leftsemi (t2) on key
Output:
输出：
key value
1 a
3 c
回答by Simon VE
As I understand, a semi join is a left join or right join:
据我了解，半连接是左连接或右连接：
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?
INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么区别？
So the difference between a left (semi) join and a "conventional" join is that you only retrieve the data of the left table (where you have a match on your join condition). Whereas with a full (outer) join (I think thats what you mean by conventional join), you retrieve the data of both tables where your condition matches.
因此，左（半）连接和“常规”连接之间的区别在于您只检索左表的数据（您的连接条件匹配）。而使用完整（外部）连接（我认为这就是传统连接的意思），您可以检索条件匹配的两个表的数据。
