不同的连接操作有何用途？
SQL中不同的连接操作有什么用？就像我想知道为什么我们需要不同的内部和外部连接？
What are the uses of the different join operations in SQL? Like I want to know why do we need the different inner and outer joins?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您真正需要的唯一连接类型是
LEFT OUTER JOIN。每一种其他类型的联接都可以根据一个或多个左外联接以及可能的一些过滤来重写。那么为什么我们需要所有其他的呢？难道只是为了迷惑人们吗？如果只有一种类型的连接不是更简单吗？您还可能会问：为什么同时有
a <= b和b >= a？这些不都是做同样的事情吗？难道我们就不能除掉其中一个吗？这会让事情变得简单！有时，将
<=交换为>=比交换参数更容易。类似地，左连接和右连接是相同的，只是交换了操作数。但同时拥有这两种选择也是很实用的，而不是要求人们按照特定的顺序编写查询。您可能会问的另一件事是：在逻辑上为什么我们有
AND、OR、NOT、XOR、<代码>NAND、NOR等？所有这些都可以用NAND来重写！为什么不直接使用NAND？好吧，编写OR< 是尴尬 /code> 就NAND而言，其意图并不那么明显 - 如果你写OR，人们会立即知道你的意思。如果您编写一堆 NAND，那么您想要实现的目标并不明显。同样，如果您想要执行
a FULL OUTER JOIN b，您可以进行左连接和右连接，删除重复的结果，然后合并所有结果。但这很痛苦，所以有一个简写。你什么时候使用每一个？下面是一个简化的规则：
The only type of join you really need is
LEFT OUTER JOIN. Every other type of join can be rewritten in terms of one or more left outer joins, and possibly some filtering. So why do we need all the others? Is it just to confuse people? Wouldn't it be simpler if there were only one type of join?You could also ask: Why have both
a <= bandb >= a? Don't these just do the same thing? Can't we just get rid of one of them? It would simplify things!Sometimes it's easier to swap
<=to>=instead of swapping the arguments round. Similarly, a left join and a right join are the same thing just with the operands swapped. But again it's practical to have both options instead of requiring people to write their queries in a specific order.Another thing you could ask is: In logic why do we have
AND,OR,NOT,XOR,NAND,NOR, etc? All these can be rewritten in terms ofNANDs! Why not just haveNAND? Well it's awkward to write anORin terms ofNANDs, and it's not as obvious what the intention is - if you writeOR, people know immediately what you mean. If you write a bunch ofNANDs, it is not obvious what you are trying to achieve.Similarly, if you want to do
a FULL OUTER JOIN byou could make a left join and a right join, remove duplicated results, and then union all. But that's a pain and so there's a shorthand for it.When do you use each one? Here's a simplified rule:
内连接- 根据指定的条件连接两组匹配的行。outer join- 选择一组的所有元素，以及另一组中的匹配或空（如果不匹配）元素。外连接可以是左或右，以指定完整返回哪个集合。inner join- joins rows from both sets of the match based on specified criteria.outer join- selects all of one set, along with matching or empty (if not matched) elements from the other set. Outer joins can beleftorright, to specify which set is returned in its entirety.为了使其他答案更清晰 - 例如，当您要连接的列包含空值时，根据您选择的连接，您会得到不同的结果。
因此，对于每个现实生活场景，都有一个适合它的联接（您想要没有数据的行，或者不在空值示例中）。
To make the other answers clearer - YOU GET DIFFERENT RESULTS according to the join you choose, when the columns you're joining on contain null values - for example.
So - for each Real-life scenario there is a join that suits it (either you want the lines without the data or not in the null values example).
我的答案假设两个表在一个键上连接：
INNER JOIN- 获取两个连接表中的结果（根据连接规则）FULL OUTER JOIN- 获取两个表中的所有结果（笛卡尔积）LEFT OUTER JOIN- 获取左表中的所有结果以及右表中的匹配结果您可以添加
WHERE子句以进一步限制结果。使用这些只是为了获得您想要得到的东西。
My answer assumes 2 tables joined on a single key:
INNER JOIN- get the results that are in both join tables (according to the join rule)FULL OUTER JOIN- get all results from both table (Cartesian product)LEFT OUTER JOIN- get all the results from left table and the matching results from the rightYou can add
WHEREclauses in order to further constrain the results.Use these in order to only get what you want to get.