当前位置： 代码网 > it编程>数据库>MsSqlserver > SQL多表联查的几种方法示例总结

SQL多表联查的几种方法示例总结

2024年09月30日 • MsSqlserver •我要评论
1.内连接（inner join）语法：select 列名from 表1inner join 表2 on 表1.列名 = 表2.列名;示例：select employees.name, departm

1. 内连接（inner join）

语法：

select 列名
from 表1
inner join 表2 on 表1.列名 = 表2.列名;

示例：

select employees.name, departments.department_name
from employees
inner join departments on employees.department_id = departments.id;

2. 左外连接（left join）

语法：

select 列名
from 表1
left join 表2 on 表1.列名 = 表2.列名;

示例：

select employees.name, departments.department_name
from employees
left join departments on employees.department_id = departments.id;

3. 右外连接（right join）

语法：

select 列名
from 表1
right join 表2 on 表1.列名 = 表2.列名;

示例：

select employees.name, departments.department_name
from employees
right join departments on employees.department_id = departments.id;

4. 全外连接（full join）

语法：

select 列名
from 表1
left join 表2 on 表1.列名 = 表2.列名
union
select 列名
from 表1
right join 表2 on 表1.列名 = 表2.列名;

示例：

select employees.name, departments.department_name
from employees
left join departments on employees.department_id = departments.id
union
select employees.name, departments.department_name
from employees
right join departments on employees.department_id = departments.id;

5. 交叉连接（cross join）

语法：

select 列名
from 表1
cross join 表2;

示例：

select employees.name, departments.department_name
from employees
cross join departments;

6. 自连接（self join）

自联接（self join）是指一个表与自身进行联接。这种操作通常用于表中记录之间的比较或关联。自联接可以帮助解决例如员工与其经理的关系、产品与产品之间的关系等问题。

自连接查询，可以是内连接查询，也可以是外连接查询。

语法：

select 列名1, 列名2, ...
from 表名 as 别名1
join 表名 as 别名2
on 别名1.列名 = 别名2.列名
where 条件;

示例：员工与经理自联接

假设我们有一个 employees 表，如下：

employees 表结构：

idnamemanager_id
1alicenull
2bob1
3carol1
4dave2

查询员工及其经理的姓名：

select employees.name as employee_name, manager.name as manager_name
from employees
left join employees as manager
on employees.manager_id = manager.id;

7. 左外连接排除内连接

定义：返回左表中所有记录和右表中匹配的记录，但排除那些在右表中也有匹配的记录。

语法：

select 列名
from 表1
left join 表2 on 表1.列名 = 表2.列名
where 表2.列名 is null;

示例：

select employees.name
from employees
left join departments on employees.department_id = departments.id
where departments.id is null;

8. 右外连接排除内连接

定义：返回右表中所有记录和左表中匹配的记录，但排除那些在左表中也有匹配的记录。

语法：

select 列名
from 表1
right join 表2 on 表1.列名 = 表2.列名
where 表1.列名 is null;

示例：

select departments.department_name
from departments
right join employees on departments.id = employees.department_id
where employees.id is null;

9. 全外连接排除内连接

定义：返回两个表的所有记录，但排除那些在两个表中都匹配的记录。

语法：

select 列名
from 表1
left join 表2 on 表1.列名 = 表2.列名
where 表2.列名 is null
union
select 列名
from 表2
right join 表1 on 表2.列名 = 表1.列名
where 表1.列名 is null;

示例：

select employees.name
from employees
left join departments on employees.department_id = departments.id
where departments.id is null
union
select departments.department_name
from departments
right join employees on departments.id = employees.department_id
where employees.id is null;

总结

  • 内连接：仅返回两个表中匹配的记录。
  • 左外连接：返回左表的所有记录和右表中匹配的记录，右表中没有匹配的记录显示为 null。
  • 右外连接：返回右表的所有记录和左表中匹配的记录，左表中没有匹配的记录显示为 null。
  • 全外连接：返回两个表的所有记录，通过 union 模拟。
  • 交叉连接：返回两个表的笛卡尔积。
  • 自连接：表与自身的联接，常用于记录间的比较。
  • 左外连接排除内连接：返回左表中的记录，这些记录在右表中没有匹配项。
  • 右外连接排除内连接：返回右表中的记录，这些记录在左表中没有匹配项。
  • 全外连接排除内连接：返回两个表的所有记录，排除那些在两个表中都有匹配的记录。

联合查询 

联合查询（也称为集合操作）用于将多个 select 查询的结果集合并在一起。

1.  union

  • 功能：将两个或多个 select 查询的结果集合并为一个结果集，并去除重复行。
  • 语法：
    select column1, column2 from table1
union
select column1, column2 from table2;

    注意：所有 select 查询必须具有相同数量的列，并且对应列的类型应兼容。

2.  union all

  • 功能：将两个或多个 select 查询的结果集合并为一个结果集，包括所有重复行。
  • 语法：
    select column1, column2 from table1
union all
select column1, column2 from table2;

    注意：比 union 更高效，因为它不去重。

3. intersect

  • 功能：返回两个 select 查询结果中的交集，即两个查询中都存在的行。
  • 语法：
    select column1, column2 from table1
intersect
select column1, column2 from table2;
  • 注意：mysql 8.0 及之前的版本不直接支持 intersect，可以使用 inner join 来实现类似功能。

4. except (或 minus)

  • 功能：返回在第一个 select 查询中存在但在第二个 select 查询中不存在的行。
  • 语法：
    select column1, column2 from table1
except
select column1, column2 from table2;

    注意：mysql 8.0 及之前的版本不直接支持 except。可以使用 left join 和 is null 实现类似功能。

三表联查 

定义：将三个表通过指定的连接条件联接在一起，通常用于从多个表中获取相关数据。

语法格式：

select 列名1, 列名2, ...
from 表1
join 表2 on 表1.列名 = 表2.列名
join 表3 on 表2.列名 = 表3.列名
where 条件
group by 列名
having 条件
order by 列名 [asc|desc]
limit n offset m;

假设我们有以下三个表：

  • employees（员工表）：包含员工的基本信息。
  • departments（部门表）：包含部门的信息。
  • salaries（薪资表）：包含员工的薪资信息。

表结构示例：

employees 表：

idnamedepartment_id
1alice1
2bob2
3carol1

departments 表：

iddepartment_name
1hr
2it

salaries 表：

employee_idsalary
170000
280000
375000

查询语句：

select employees.name, departments.department_name, salaries.salary
from employees
inner join departments on employees.department_id = departments.id
inner join salaries on employees.id = salaries.employee_id;

/*
解释：

inner join departments on employees.department_id = departments.id：

将 employees 表与 departments 表通过 department_id 和 id 列进行连接，提取部门名称。
inner join salaries on employees.id = salaries.employee_id：

将 employees 表与 salaries 表通过 id 和 employee_id 列进行连接，提取薪资信息。
select employees.name, departments.department_name, salaries.salary：

从连接后的结果中选择员工姓名、部门名称和薪资信息。
*/

四表联查

定义：将四个表通过指定的连接条件联接在一起，用于从多个表中获取更复杂的数据。

语法格式：

select 列名1, 列名2, ...
from 表1
join 表2 on 表1.列名 = 表2.列名
join 表3 on 表2.列名 = 表3.列名
join 表4 on 表3.列名 = 表4.列名
where 条件
group by 列名
having 条件
order by 列名 [asc|desc]
limit n offset m;

四表联查示例

假设我们有以下四个表：

  • employees（员工表）：包含员工的基本信息。
  • departments（部门表）：包含部门的信息。
  • salaries（薪资表）：包含员工的薪资信息。
  • projects（项目表）：包含项目的信息。

表结构示例：

employees 表：

idnamedepartment_id
1alice1
2bob2
3carol1

departments 表：

iddepartment_name
1hr
2it

salaries 表：

employee_idsalary
170000
280000
375000

projects 表：

project_idproject_namedepartment_id
1project x1
2project y2

查询语句：

select employees.name, departments.department_name, salaries.salary, projects.project_name
from employees
inner join departments on employees.department_id = departments.id
inner join salaries on employees.id = salaries.employee_id
inner join projects on departments.id = projects.department_id;

/*
解释：

inner join departments on employees.department_id = departments.id：

将 employees 表与 departments 表通过 department_id 和 id 列进行连接，提取部门名称。
inner join salaries on employees.id = salaries.employee_id：

将 employees 表与 salaries 表通过 id 和 employee_id 列进行连接，提取薪资信息。
inner join projects on departments.id = projects.department_id：

将 departments 表与 projects 表通过 department_id 和 department_id 列进行连接，提取项目名称。
select employees.name, departments.department_name, salaries.salary, projects.project_name：

从连接后的结果中选择员工姓名、部门名称、薪资信息和项目名称。
*/

总结 

到此这篇关于sql多表联查的几种方法的文章就介绍到这了,更多相关sql多表联查内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com