sql各种Join用法详解

本文详细介绍了SQL中的各种连接类型,如内连接、外连接、交叉连接等,并探讨了它们在实际应用中的性能表现。此外,还提供了一些SQL查询优化技巧,帮助读者提升数据库操作效率。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

在关系代数中,连接运算是由一个笛卡尔积运算和一个选取运算构成的。首先用笛卡尔积完成对两个数据集合的乘运算,然后对生成的结果集合进行选取运算,确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起。连接的全部意义在于在水平方向上合并两个数据集合(通常是表),并产生一个新的结果集合,其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。SQL提供了多种类型的连接方式,它们之间的区别在于:从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。

不同的 SQL JOIN

连接类型

定义

 

Inner Join

内连接是最常见的一种连接,它也被称为普通连接,只连接匹配的行(仅对满足连接条件的CROSS中的列)。它又分为等值连接(连接条件运算符为"=")和不等值连接(连接条件运算符不为"=",例如between...and)。

 

Outer Join

Full Outer Join

FULL JOIN 会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行,那么对面的数据用NULL代替

Left Outer Join

LEFT JOIN返回左表的全部行和右表满足ON条件的行,如果左表的行在右表中没有匹配,那么这一行右表中对应数据用NULL代替。

Right Outer Join

RIGHT JOIN返回右表的全部行和左表满足ON条件的行,如果右表的行在左表中没有匹配,那么这一行左表中对应数据用NULL代替。

 Cross Join

CROSS对两个表执行笛卡尔乘积。它为左表行和右表行的每种可能的组合返回一行。返回(左表行数*右表行数)行的表。

 

 

Appendix      

Natural Join

自然连接是一种特殊的等值连接,在连接条件中使用等于(=)运算符比较被连接列的列值,但它使用选择列表指出查询结果集合中所包括的列,并删除连接表中的重复列。

自连接

某个表和其自身连接,常用在同一表内不同数据间对同一列的比较


self join 常用在同一表内不同数据间对同一列的比较 

select a.emp_no,a.emp_name,b.emp_no,b.emp_name,a.date_hired from

employee ajoin employee b 

on (a.emp_no!=b.emp_no and a.date_hired=b.date_hired)     

order by a.date_hired 

这样会重复数据,只要加上一句 and a.emp_name>b.emp_name 

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。

同时,每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

SQL UNION ALL 语法

UNION ALL 命令和 UNION 命令几乎是等效的,不过 UNION ALL 命令会列出所有的值。


SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

UNION 实例:

列出所有在中国和美国的不同的雇员名:

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

UNION ALL实例:

列出在中国和美国的所有的雇员:

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA



下面转自:http://blog.sina.com.cn/s/blog_7e5f32ff0102wbwc.html

性能优化

1.显示(explicit) inner join VS 隐式(implicit) inner join

如:

select * from
table a inner join table b
on a.id = b.id;

VS

select a.*, b.*
from table a, table b
where a.id = b.id;

我在数据库中比较(10w数据)得之,它们用时几乎相同,第一个是显示的inner join,后一个是隐式的inner join。

2.left join/right join VS inner join

尽量用inner join.避免 LEFT JOIN 和 NULL.

在使用left join(或right join)时,应该清楚的知道以下几点:

(1). on与 where的执行顺序

ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。如:

PASS

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;

Great

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1

从上面例子可以看出,尽可能满足ON的条件,而少用Where的条件。从执行性能来看第二个显然更加省时。

(2).注意ON 子句和 WHERE 子句的不同

如作者举了一个列子:

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |  2 |     22 |     0 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)

从上可知,第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN,然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

(3).尽量避免子查询,而用join

往往性能这玩意儿,更多时候体现在数据量比较大的时候,此时,我们应该避免复杂的子查询。如下:

PASS

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id); 

Great

insert into t1(a1)  
select b1 from t2  
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id   
where t1.id is null;  

mysql的exists与inner join 和 not exists与 left join 性能差别惊人

由于客户数据量越来越大,在实践中让我发现mysql的exists与inner join 和 not exists与 left join 性能差别惊人。

我们一般在做数据插入时,想插入不重复的数据,或者盘点数据在一个表,另一个表否有存在相同的数据会用not exists和exists,例如:

Sql代码  
  1. insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);  

 

如果t1的数据量很大时,性能会非常慢。经过实践,用以下方法能提高很多。

Sql代码
  1. insert into t1(a1)  
  2. select b1 from t2  
  3. left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id   
  4. where t1.id is null;  

 

Sql代码
  1. select * from t1 where exists(select 1 from t2 where t1.id=t2.r_id);  

 替换为:

Sql代码
  1. select t1.* from t1   
  2. inner join (select distinct r_id from t2) t2 on t1.id= t2.r_id   

 这是实践的得出的结果。不知否有其他更好的方法,或则这个只是特例而已。 


补充:MySQL STRAIGHT_JOIN 与 NATURAL JOIN的使用

长话短说:straight_join实现强制多表的载入顺序,从左到右,如:

...A straight_join B on A.name = B.name 

straight_join完全等同于inner join 只不过,join语法是根据“哪个表的结果集小,就以哪个表为驱动表”来决定谁先载入的,而straight_join 会强制选择其左边的表先载入。

往往我们在分析mysql处理性能时,如(Explain),如果发现mysql在载入顺序不合理的情况下,可以使用这个语句,但往往mysql能够自动的分析并处理好。

更多内容参考:

MySQL STRAIGHT_JOIN 与 NATURAL JOIN

MySQL优化的奇技淫巧之STRAIGHT_JOIN

八.参考:

五种提高 SQL 性能的方法

关于 MySQL LEFT JOIN 你可能需要了解的三点


<think>好的,我现在需要详细解答用户关于SQL Server JOIN语句的问题。首先,我要确保自己全面理解各种JOIN类型,并能够用清晰的中文解释出来。用户可能对JOIN的概念不太熟悉,所以需要从基础开始,逐步深入。 首先,回顾一下SQL中的JOIN类型,包括INNER JOIN、LEFT JOIN(或LEFT OUTER JOIN)、RIGHT JOIN(RIGHT OUTER JOIN)、FULL JOIN(FULL OUTER JOIN),还有交叉连接CROSS JOIN。可能还需要提到自连接,虽然它本质上是同一表的JOIN操作。 接下来,每个JOIN类型的具体作用和使用场景需要详细说明。例如,INNER JOIN用于获取两个表的交集,LEFT JOIN保留左表所有记录,右表不匹配则为NULL等。要确保每个例子都准确,最好给出示例SQL语句和结果,帮助用户理解。 同时,用户可能对ON和WHERE子句在JOIN中的区别有疑问,需要解释清楚条件放置的位置对结果的影响。例如,在LEFT JOIN中,ON后面的条件会影响连接过程,而WHERE子句则是在连接后过滤结果。 另外,需要注意SQL Server中JOIN语法是否正确,例如是否支持USING子句,或者是否需要使用ON来指定连接条件。记得提醒用户在使用RIGHT JOIN或FULL JOIN时的注意事项,比如可能产生的NULL值。 还要考虑到用户可能会遇到的常见问题,比如多表JOIN的顺序,性能优化建议,以及JOIN与子查询的对比等。是否需要简要提及这些内容,取决于用户的接受程度,但主要先集中在JOIN语句本身的详解上。 然后,结构安排要合理。先定义JOIN的作用,然后分点介绍每种JOIN类型,每个类型都给出语法、说明、示例和结果。最后可以总结不同JOIN的区别,使用场景,以及常见问题解答。 在编写示例时,需要创建简单易懂的表数据,比如员工表和部门表,这样用户容易理解。例如,员工表有员工ID、姓名、部门ID,部门表有部门ID和部门名称。通过这些表展示不同JOIN的效果。 需要检查是否有语法错误,特别是LaTeX格式是否正确。根据系统级指令,行内数学表达式用$...$,但这里可能用不到数学公式,但如果有示例中的代码或表达式,要确保符合要求。不过在这个问题中,可能主要是SQL代码,所以需要用代码块标记,而不是LaTeX。但用户提供的系统指令中可能要求的是数学表达式使用LaTeX,但SQL代码应该用代码块。可能需要确认用户的要求,但根据用户给出的示例,他们可能希望代码用适当的标记,比如反引号。 不过用户给的示例中,系统级指令提到数学表达式的格式,但当前问题涉及的是SQL语句,可能不需要LaTeX,但需要确保回答中的代码部分正确。可能用户希望回答中的代码用代码块展示,所以需要用Markdown的代码块语法,比如三个反引号加sql。 另外,用户要求回答结构清晰,分步骤解决问题。所以应该按照逻辑顺序,先介绍JOIN的概念,然后分类型详细讲解,每个类型都有示例,最后总结对比,可能给出使用建议。 最后,要确保语言简洁明了,避免专业术语过多,必要时进行解释。例如,在解释外连接时,可以说明“外连接会保留主表的所有行,即使从表没有匹配的行”,这样用户更容易理解。 现在检查是否有遗漏的JOIN类型,比如CROSS JOIN,以及自然连接(虽然SQL Server不直接支持自然连接,但可以提及)。同时,自连接需要作为特殊情况说明,但可能不属于基本JOIN类型,可以放在最后作为补充。 确认所有示例在SQL Server中有效,例如使用正确的语法,比如LEFT OUTER JOIN和LEFT JOIN是相同的,SQL Server都支持。同时,别名使用是否正确,如FROM Employees e这样的别名用法。 总结下来,回答结构大致如下: 1. JOIN的作用和分类 2. 每种JOIN的详细说明(INNER, LEFT, RIGHT, FULL, CROSS),包括语法、示例、结果 3. 自连接的简要说明 4. 使用JOIN的注意事项和常见问题 现在需要组织语言,确保每个部分清晰,示例准确,并且符合用户的要求。可能还需要指出不同JOIN之间的区别,比如LEFT和RIGHT JOIN的方向性,FULL JOIN是两者的结合等。</think>以下为SQL Server中`JOIN`语句的详解,按使用场景和语法分类逐步说明: --- ### 一、JOIN的作用 `JOIN`用于根据关联条件合并两个或多个表的行,是关系型数据库的核心操作之一。通过`JOIN`可实现: 1. 横向合并数据(增加列) 2. 根据条件筛选关联行 --- ### 二、JOIN的分类及语法 #### 1. INNER JOIN(内连接) - **作用**:返回两个表**同时满足条件**的行。 - **语法**: ```sql SELECT 列名 FROM 表1 INNER JOIN 表2 ON 表1.列 = 表2.列; ``` - **示例**: ```sql -- 查询员工及其部门信息(仅显示有部门的员工) SELECT e.Name, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID; ``` **结果**:仅包含`Employees`和`Departments`中`DepartmentID`匹配的行。 --- #### 2. LEFT JOIN(左外连接) - **作用**:返回左表**所有行** + 右表匹配的行(不匹配的右表字段为`NULL`)。 - **语法**: ```sql SELECT 列名 FROM 表1 LEFT JOIN 表2 ON 表1.列 = 表2.列; ``` - **示例**: ```sql -- 查询所有员工及其部门(包括未分配部门的员工) SELECT e.Name, d.DepartmentName FROM Employees e LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID; ``` **结果**:包含所有`Employees`行,`Departments`无匹配时显示`NULL`。 --- #### 3. RIGHT JOIN(右外连接) - **作用**:返回右表**所有行** + 左表匹配的行(不匹配的左表字段为`NULL`)。 - **语法**: ```sql SELECT 列名 FROM 表1 RIGHT JOIN 表2 ON 表1.列 = 表2.列; ``` - **示例**: ```sql -- 查询所有部门及其员工(包括无员工的部门) SELECT e.Name, d.DepartmentName FROM Employees e RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID; ``` **结果**:包含所有`Departments`行,`Employees`无匹配时显示`NULL`。 --- #### 4. FULL JOIN(全外连接) - **作用**:返回左右表**所有行**,不匹配的字段填充`NULL`。 - **语法**: ```sql SELECT 列名 FROM 表1 FULL JOIN 表2 ON 表1.列 = 表2.列; ``` - **示例**: ```sql -- 显示所有员工和部门(包括未分配部门的员工和空部门) SELECT e.Name, d.DepartmentName FROM Employees e FULL JOIN Departments d ON e.DepartmentID = d.DepartmentID; ``` **结果**:`Employees`和`Departments`的所有行均保留,无匹配则对应字段为`NULL`。 --- #### 5. CROSS JOIN(交叉连接) - **作用**:返回两表的笛卡尔积(所有可能的行组合)。 - **语法**: ```sql SELECT 列名 FROM 表1 CROSS JOIN 表2; ``` - **示例**: ```sql -- 生成员工和部门的所有组合 SELECT e.Name, d.DepartmentName FROM Employees e CROSS JOIN Departments d; ``` **结果**:每个员工与每个部门组合成一行。 --- ### 三、特殊场景 #### 1. 自连接(Self-Join) - 同一表通过别名关联,常用于层级数据(如上下级关系): ```sql SELECT a.Name AS Employee, b.Name AS Manager FROM Employees a LEFT JOIN Employees b ON a.ManagerID = b.EmployeeID; ``` #### 2. 多表JOIN - 按需链式添加多个`JOIN`: ```sql SELECT e.Name, d.DepartmentName, p.ProjectName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID LEFT JOIN Projects p ON e.ProjectID = p.ProjectID; ``` --- ### 四、注意事项 1. **性能优化**: - 在`JOIN`条件涉及的列上建立索引。 - 避免对大型表使用`CROSS JOIN`。 2. **别名使用**: - 推荐为表定义简短别名(如`e`、`d`),提高可读性。 3. **NULL处理**: - 外连接中未匹配的字段为`NULL`,需用`COALESCE()`或`IS NULL`处理。 --- ### 五、总结对比 | JOIN类型 | 方向性 | 保留未匹配行 | 典型场景 | |------------------|--------|-----------------------|------------------------| | `INNER JOIN` | 双向 | 不保留 | 精确匹配查询 | | `LEFT JOIN` | 左→右 | 保留左表 | 主表数据全显示 | | `RIGHT JOIN` | 右→左 | 保留右表 | 从表数据全显示 | | `FULL JOIN` | 双向 | 保留左右表 | 合并全量数据 | | `CROSS JOIN` | 无 | 生成所有组合 | 生成测试数据或组合分析 | 掌握`JOIN`的用法可灵活处理复杂的数据关联需求。建议通过实际数据测试不同`JOIN`的效果以加深理解。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值