SQL JOIN和不同类型的JOIN

本文介绍了SQL JOIN和不同类型的JOIN的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我在 SO 和其他一些论坛上经历过许多主题。所以我想我会总结什么是SQL JOIN？和什么是不同类型的SQL JOIN？。解决方案 什么是 SQL JOIN ？ SQL JOIN 是从两个或多个数据库表中检索数据的方法。 有什么不同 SQL JOIN s？ 共有五个 JOIN 它们是： 1. JOIN或INNER JOIN 2. OUTER JOIN LEFT OUTER JOIN或LEFT JOIN 2.2右外部加入或右加入 2.3全部加入或完全加入 3.自然加入 4. CROSS JOIN 5. SELF JOIN 1。 JOIN或INNER JOIN： 在这种 JOIN 中，我们获得所有符合条件的记录 换句话说， INNER JOIN code>基于以下单一事实：只有在BOTH表中匹配的条目应该被列出。 注意 JOIN 没有任何其他 JOIN 关键字（如 INNER ， OUTER ， LEFT 等）是 INNER JOIN 。换句话说， INNER JOIN 是a JOIN的语法糖（参见： JOIN和INNER JOIN之间的差异）。 2。 OUTER JOIN： OUTER JOIN 检索 ，一个表中的匹配行和另一个表中的所有行或者，所有表中的所有行（无论是否存在匹配） p> 有三种外部加入： 2.1 LEFT OUTER JOIN或LEFT JOIN 此连接将返回左表中的所有行以及右表中匹配的行。如果右表中没有匹配的列，则返回 NULL 值。 JOIN或RIGHT JOIN 此 JOIN 返回右表中的所有行，匹配左表中的行。如果左表中没有匹配的列，则返回 NULL 值。 2.3 FULL OUTER JOIN或FULL JOIN 此 JOIN 结合 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 。当满足条件时，它从任一表返回行，并且在没有匹配时返回 NULL 。 换句话说， OUTER JOIN 是基于以下事实：只有一个表中的匹配条目（RIGHT或LEFT）或BOTH 请注意，OUTER JOIN是一个松散的形式的INNER JOIN。 3。 NATURAL JOIN： 它基于以下两个条件： 从结果中删除重复的列。 这似乎更多的是理论性的，结果（可能）大多数DBMS 甚至不打扰支持这个。 > 4。 CROSS JOIN： 这是两个表的笛卡尔乘积。在大多数情况下， CROSS JOIN 的结果将没有意义。此外，我们根本不需要这个（或需要最少，更精确）。 5。 SELF JOIN： 它不是 JOIN 的不同形式，而是 JOIN （ INNER ， OUTER 等） > 基于运算符的JOIN 根据 JOIN 子句，可以有两种类型 JOIN s。它们是 Equi JOIN Theta JOIN 1。 Equi JOIN： 对于 JOIN 类型（ INNER ， OUTER 等），如果我们只使用相等运算符（=），那么我们说 JOIN 是 EQUI JOIN 。 2。 Theta JOIN： 这与 EQUI JOIN 相同，但它允许所有其他运算符，如>，< = etc。 许多人认为 EQUI JOIN 和Theta JOIN 类似 INNER ， OUTER etc JOIN 。但我坚信，它的一个错误，使得的想法模糊。因为 INNER JOIN ， OUTER JOIN 等都与连接表和其数据，其中 EQUI JOIN 和 THETA JOIN 只有与我们在前者使用的运算符连接。 同样，有很多人认为 NATURAL JOIN 是某种特殊的 EQUI JOIN 。事实上，这是真的，因为第一个条件我提到 NATURAL JOIN 。然而，我们不必限制只是到 NATURAL JOIN 单独。 INNER JOIN s， OUTER JOIN s 等可以是 EQUI JOIN 。 I have been going through many threads on SO and some other forums. So I thought I would summarize "What is SQL JOIN?" and "What are different types of SQL JOINs?". 解决方案 What is SQL JOIN ?SQL JOIN is a method to retrieve data from two or more database tables.What are the different SQL JOINs ?There are a total of five JOINs. They are : 1. JOIN or INNER JOIN 2. OUTER JOIN 2.1 LEFT OUTER JOIN or LEFT JOIN 2.2 RIGHT OUTER JOIN or RIGHT JOIN 2.3 FULL OUTER JOIN or FULL JOIN 3. NATURAL JOIN 4. CROSS JOIN 5. SELF JOIN1. JOIN or INNER JOIN :In this kind of a JOIN, we get all records that match the condition in both the tables,and records in both the tables that do not match are not reported.In other words, INNER JOIN is based on the single fact that : ONLY the matching entries in BOTH the tables SHOULD be listed.Note that a JOIN without any other JOIN keywords (like INNER, OUTER, LEFT, etc) is an INNER JOIN. In other words, INNER JOIN isa Syntactic sugar for JOIN (see : Difference between JOIN and INNER JOIN).2. OUTER JOIN :OUTER JOIN retrievesEither, the matched rows from one table and all rows in the other table Or, all rows in all tables (it doesn't matter whether or not there is a match).There are three kinds of Outer Join :2.1 LEFT OUTER JOIN or LEFT JOINThis join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.2.2 RIGHT OUTER JOIN or RIGHT JOINThis JOIN returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.2.3 FULL OUTER JOIN or FULL JOINThis JOIN combines LEFT OUTER JOIN and RIGHT OUTER JOIN. It returns row from either table when the conditions are met and returns NULL value when there is no match.In other words, OUTER JOIN is based on the fact that : ONLY the matching entries in ONE OF the tables (RIGHT or LEFT)or BOTH of the tables(FULL) SHOULD be listed.Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.3. NATURAL JOIN :It is based on the two conditions :the JOIN is made on all the columns with the same name for equality.Removes duplicate columns from the result.This seems to be more of theoretical in nature and as a result (probably) most DBMSdon't even bother supporting this.4. CROSS JOIN :It is the Cartesian product of the two tables involved. The result of a CROSS JOIN will not make sensein most of the situations. Moreover, we wont need this at all (or needs the least, to be precise).5. SELF JOIN :It is not a different form of JOIN, rather it is a JOIN (INNER, OUTER, etc) of a table to itself.JOINs based on OperatorsDepending on the operator used for a JOIN clause, there can be two types of JOINs. They areEqui JOINTheta JOIN1. Equi JOIN :For whatever JOIN type (INNER, OUTER, etc), if we use ONLY the equality operator (=), then we say thatthe JOIN is an EQUI JOIN.2. Theta JOIN :This is same as EQUI JOIN but it allows all other operators like >, <, >= etc. Many consider both EQUI JOIN and Theta JOIN similar to INNER, OUTER etc JOINs. But I strongly believe that its a mistake and makes the ideas vague. Because INNER JOIN, OUTER JOIN etc are all connected with the tables and their data where as EQUI JOIN and THETA JOIN are only connected with the operators we use in the former. Again, there are many who consider NATURAL JOIN as some sort of "peculiar" EQUI JOIN. In fact, it is true, because of the first condition I mentioned for NATURAL JOIN. However, we dont have to restrict that simply to NATURAL JOINs alone. INNER JOINs, OUTER JOINs etc could be an EQUI JOIN too. 这篇关于SQL JOIN和不同类型的JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！