sql中join类型_SQLJoin类型概述和教程
sql中join类型
This article will provide an overview of the SQL Join and cover all of the SQL join types including inner, self, cross and outer. For inner joins we’ll be discussing Equi and Theta joins.
本⽂将概述SQL连接，并涵盖所有SQL连接类型，包括内部，⾃⾝，交叉和外部。 对于内部联接，我们将讨论Equi和Theta联接。
The ability to combine results from related rows from multiple tables is an important part of relational database system design. In SQL Server, this is accomplished with the SQL join clause. It’s the nature of traditional relational database systems where some table contains information related to other tables with a common key value. Using a SQL join, you can easily perform queries on related data-sets from multiple tables with these shared keys.
合并来⾃多个表的相关⾏的结果的能⼒是关系数据库系统设计的重要组成部分。 在SQL Server中，这是通过SQL join⼦句完成的。 这是传统关系数据库系统的本质，其中某些表包含具有其他键值的其他表的相关信息。 使⽤SQL连接，您可以使⽤这些共享键轻松地从多个表中对相关数据集执⾏查询。
多表left joinThe aim of this article is to provide you with the basic knowledge and examples that you will need to use the SQL join effectively in any database environment.
本⽂的⽬的是为您提供在任何数据库环境中有效使⽤SQL连接所需的基本知识和⽰例。
什么是SQL连接？ (What is a SQL join?)
A SQL Join is a special form of generating a meaningful data by combining multiple tables relate to each other using a “Key”. Typically, relational tables must be designed with a unique column and this column is used to create relationships with one or more other tables. When you need a result-set that includes related rows from multiple tables, you’ll need to use SQL join on this column
SQL联接是⼀种特殊形式，可以通过使⽤“键”组合彼此相关的多个表来⽣成有意义的数据。 通常，关系表必须设计为具有唯⼀列，并且该列⽤于创建与⼀个或多个其他表的关系。 如果需要⼀个包含多个表中相关⾏的结果集，则需要在此列上使⽤SQL连接
The various SQL join types are as follows
各种SQL连接类型如下
1.
1. Equi join
平等加⼊
2. Non-equi join (Theta join)
⾮等额联接（Theta联接）
2.
1. SQL left join or left outer join
SQL左联接或左外部联接
2. SQL right join or right outer join
SQL右连接或右外连接
3. SQL full join or full outer join
SQL完全连接或完全外部连接
3. SQL cross join
SQL交叉连接
4. SQL self join
SQL⾃连接
Note: The keyword outer is optional. It means you can specify the keyword “outer” or not makes no difference to the query execution.
注意：关键字outer是可选的。这意味着您可以指定关键字“ outer”，或者对查询执⾏没有影响。
For example,
例如，
SQL连接类型 (SQL join types)
SQL内部联接 (SQL inner join)
The simplest and most common form of a join is the SQL inner join the default of the SQL join types used in most database management systems. It’s the default SQL join you get when you use the join keyword by itself.
连接的最简单和最常见的形式是SQL内部连接，这是⼤多数数据库管理系统中使⽤SQL连接类型的默认值。 它是单独使⽤join关键字时获得的默认SQL连接。
The result of the SQL inner join includes rows from both the tables where the join conditions are met.
SQL内部联接的结果包括两个表中满⾜联接条件的⾏。
Syntax:
句法：
SELECT ColumnList from LeftTable L
INNER join  RightTable R
ON L.Column=R.Column
Note: It is very easy to visualize a join query as a Venn diagram, where each of the tables is represented by intersecting shapes. The intersection of the shapes, where the tables overlap, are the rows where a condition is met. Unique columns (ID) are often used for this purpose, where the condition to be met is matching the ids of rows.
注意：将联接查询可视化为维恩图⾮常容易，其中每个表都由相交的形状表⽰。表格相交的形状的交点是满⾜条件的⾏。唯⼀列（ID）通常⽤于此⽬的，其中要满⾜的条件与⾏的ID匹配。
平等加⼊： (Equi join:)
An equi join is the most common form of SQL inner join used in practice. If the join contains an equality =, then it’s an equi-join.
等联接是实践中使⽤的最常见SQL内部联接形式。 如果联接包含等号运算符，例如=， 则为等联接。
The following example returns all matching state names and stateProvinceIDs.
下⾯的⽰例返回所有匹配的状态名称和stateProvinceID。
SELECT DISTINCT A.StateProvinceID,S.Name
FROM Person.Address A
inner join Person.StateProvince S
On A.StateProvinceID=S.StateProvinceID
Theta连接（⾮设备连接）： (Theta join (Non-equi join):)
In general, this a Theta join used to specify operators or conditions (the ON clause in SQL). In practice, this is a rarely used SQL join types. In most cases, the join will use a non-equality >
通常，此Theta联接⽤于指定运算符或条件（SQL中的ON⼦句）。 实际上，这是⼀种很少使⽤SQL连接类型。 在⼤多数情况下，联接将使⽤⾮相等条件，例如>
SELECT p1.FirstName, p2. FirstName
FROM PErson.Person p1
INNER join PErson.Person p2
ON len(p1.FirstName) > len(p2.FirstName);
SQL⾃连接 (SQL self join)
A SQL Self join is a mechanism of joining a table to itself. You would use a self join when you wanted to create a result set joining records in the table with some other records from the same table.
SQL Self连接是将表连接到⾃⾝的机制。 当您想要在表中创建记录集和同⼀表中的其他记录时，可以使⽤⾃连接。
For a SQL self join example, consider an Employee table where managers are listed because they are also employees, and we would like to take a look at a result set that returns all of the employees and indicating who their managers are
对于⼀个SQL⾃连接⽰例，考虑⼀个列出了经理的雇员表，因为他们也是雇员，我们想看看⼀个返回所有雇员并指出他们的经理是谁的结果集。
ame, e.empno, m.ename as manager, e.mgr
FROM
emp e, emp m
= m.empno
SQL交叉连接 (SQL cross join)
A CROSS join returns all rows for all possible combinations of two tables. It generates all the rows from the left table which is then combined with all the rows from the right table. This type of join is als
o known as a Cartesian product(A*B).
CROSS连接返回两个表的所有可能组合的所有⾏。 它从左侧表⽣成所有⾏，然后将其与右侧表中的所有⾏合并。 这种连接也称为笛卡尔乘积（A * B）。
For example, if the left table has 100 rows and the right table has 100 then the cross join result will yield 10,000 rows.
例如，如果左边的表有100⾏，右边的表有100⾏，那么交叉联接结果将产⽣10,000⾏。
SELECT e.BusinessEntityID, d.Name AS Department
FROM HumanResources.Employee AS e
CROSS join HumanResources.Department AS d
SQL外部联接 (SQL outer join)
On joining tables with a SQL inner join, the output returns only matching rows from both the tables. When using a SQL outer join, not only it will list the matching rows, it will also list the unmatched rows from the other tables.
使⽤SQL内部联接联接表时，输出仅返回两个表中匹配的⾏。 使⽤SQL外连接时，它不仅会列出匹配的⾏，还会列出其他表中不匹配的⾏。
A SQL left outer join will return all the records from the left table in the join clause, regardless of matching records in the right table. The left SQL outer join includes rows where the condition is met plus all the rows from the table on the left where the condition is not met. Fields from the right table with no match will be displayed as null values.
SQL左外部联接将返回join⼦句中左表中的所有记录，⽽不管右表中的记录是否匹配。 左SQL外连接包括满⾜条件的⾏以及左侧表中不满⾜条件的所有⾏。 右侧表中没有匹配项的字段将显⽰为空值。
Syntax:
句法：
SELECT ColumnList from LeftTable L
LEFT join  RightTable R
ON L.Column=R.Column
Where R.Column is NULL
The following example joins two tablesProduct and SalesOrderDetail on ProductID and preserves the unmatched rows from the left table. The Product table is matched with the SalesOrderDetail table on the ProductID columns in each table. All products, ordered and not ordered, appear in the result set.
以下⽰例在ProductID上连接两个表Product和SalesOrderDetail，并保留左侧表中不匹配的⾏。 产品表与每个表中产品ID列上的SalesOrderDetail表匹配。 所有产品（已订购和未订购）都出现在结果集中。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。