内连接&左连接&右连接&全连接&交叉连接&自连接

最新推荐文章于 2024-09-01 13:09:09 发布
最新推荐文章于 2024-09-01 13:09:09 发布
阅读量426 收藏 1
点赞数
分类专栏： 数据库 文章标签： 数据库 mysql

实际在数据库里面演示一下把

在mysql的practice数据库中建几个表来学习一下


use practice;
create table MyTable1(
    ID varchar(5) primary key,
    Name varchar(20) not null);

insert into MyTable1(ID,Name) values("1","经验");
insert into MyTable1(ID,Name) values("2","娜娜");
insert into MyTable1(ID,Name) values("3","大姐");
insert into MyTable1(ID,Name) values("5","测试1");

create table MyTable2(
    ID varchar(5) primary key,
    Name varchar(20) not null);

insert into MyTable2(ID,Name) values("1","经验");
insert into MyTable2(ID,Name) values("2","包包");
insert into MyTable2(ID,Name) values("3","胖胖");
insert into MyTable2(ID,Name) values("4","测试2");

mysql> select * from MyTable1;
+----+---------+ 
| ID | Name    |     
+----+---------+ 
| 1  | 经验    |        
| 2  | 娜娜    |        
| 3  | 大姐    |        
| 5  | 测试1   |       
+----+---------+ 
mysql> select * from MyTable2;
+----+---------+ 
| ID | Name    |     
+----+---------+ 
| 1  | 经验    |        
| 2  | 包包    |        
| 3  | 胖胖    |        
| 4  | 测试2   |       
+----+---------+ 


left join是以表1的记录为基础，即左表的记录全部显示出来，

而表2只显示符合搜索条件的记录显示出来，

即右表根据条件显示，那这里的条件就是a.ID=b.ID,记录不足的会以NULL填充显示。

mysql> select * from MyTable1 a left join MyTable2 b on a.ID=b.ID;
+----+---------+------+--------+
| ID | Name    | ID   | Name   |
+----+---------+------+--------+
| 1  | 经验    | 1    | 经验   |
| 2  | 娜娜    | 2    | 包包   |
| 3  | 大姐    | 3    | 胖胖   |
| 5  | 测试1   | NULL | NULL   |
+----+---------+------+--------+

right join执行的效果刚好和left join执行的效果相反，

即以右表的数据为基础，显示右表全部数据，而只显示左表符合搜索条件的数据。
mysql> select * from MyTable1 a right join MyTable2 b on a.ID=b.ID;
+------+--------+----+---------+
| ID   | Name   | ID | Name    |
+------+--------+----+---------+
| 1    | 经验   | 1  | 经验    |
| 2    | 娜娜   | 2  | 包包    |
| 3    | 大姐   | 3  | 胖胖    |
| NULL | NULL   | 4  | 测试2   |
+------+--------+----+---------+

inner join不以任何数据为基础，只显示符合搜索条件的数据。

mysql> select * from MyTable1 a inner join MyTable2 b on a.ID=b.ID;
+----+--------+----+--------+
| ID | Name   | ID | Name   |
+----+--------+----+--------+
| 1  | 经验   | 1  | 经验   |
| 2  | 娜娜   | 2  | 包包   |
| 3  | 大姐   | 3  | 胖胖   |
+----+--------+----+--------+


以上是基础了解一下，后面再来仔细说说


表连接，更确切的说时是inner joins内连接
内连接仅选出两张表中互相匹配的记录，因此会导致有时我们需要的记录没有包含进来


再拿两个表来举一下例子

政党表party(Code,Name,Leader) 议员表msp(Name,Party,Constituency)
Code: 政党代码 Name: 议员名
Name: 政党名称 Party: 议员所在政党代码
Leader: 政党领袖 Constituency: 选区


SELECT msp.name , party.name FROM party JOIN msp WHERE ON code=party

这个sql语句用了join也就是inner join ，本来是想列出所有议员的名字和他所属的政党

但是查询的结果可能不全，因为如果有议员不属于任何政党，即他们的政党字段（party）为空值

这时议员表中的party字段在政党表中找不到对应的记录作匹配

即FROM party JOIN msp WHERE ON code=party没有把该记录连接起来，而是过滤出去了

在该语句中，表party在join的左边，称为左表；表msp在join的右边，所以称为右表


SELECT msp.name , party.name FROM party LEFT JOIN msp ON party.code=msp.party

这个查询结果列出了所有议员和政党，包含没有议员的政党，不包括没有政党的议员


SELECT msp.name , party.name FROM party RIGHT JOIN msp ON party.code=msp.party

这个查询结果列出了所有议员和政党，包含没有政党的议员，不包括没有议员的政党


要想查询包含没有议员的政党，又要包含没有政党的议员，用全连接！！

SELECT msp.name , party.name FROM party FULL JOIN msp ON party.code=msp.party


来来来，再继续深入一下

再建两个表

CREATE TABLE votemaster(
id int(5) primary key,
voteTitle varchar(20) not null,
voteSum int(20) not null 
);
INSERT INTO votemaster(id,voteTitle,voteSum) values(1,"对我们的满意程度",20);
INSERT INTO votemaster(id,voteTitle,voteSum) values(2,"从哪里知道我们的",16);
INSERT INTO votemaster(id,voteTitle,voteSum) values(3,"aaaaa",0);
INSERT INTO votemaster(id,voteTitle,voteSum) values(4,"bbbbb",0);
INSERT INTO votemaster(id,voteTitle,voteSum) values(5,"ccccc",0);
INSERT INTO votemaster(id,voteTitle,voteSum) values(6,"ddddd",0);

CREATE TABLE voter(
id int(5),
ip varchar(20),
voteTime timestamp,
voteNum int(20)
);
INSERT INTO voter(id,ip,voteTime,voteNum) values(1,"222.125.35.66","2013-09-09 23:42:22",1);
INSERT INTO voter(id,ip,voteTime,voteNum) values(1,"127.0.0.1","2014-09-07 13:22:32",12);
INSERT INTO voter(id,ip,voteTime,voteNum) values(2,"127.0.0.1","2015-02-09 10:11:22",11);

mysql> select * from voter;
+------+---------------+---------------------+---------+ 
| id   | ip                    | voteTime                    | voteNum |
+------+---------------+---------------------+---------+ 
|    1 | 222.125.35.66 | 2013-09-09 23:42:22 |       1  |      
|    1 | 127.0.0.1         | 2014-09-07 13:22:32 |      12 |      
|    2 | 127.0.0.1         | 2015-02-09 10:11:22 |      11 |      
+------+---------------+---------------------+---------+ 

mysql> select * from votemaster;
+----+--------------------------+---------+
| id | voteTitle                      | voteSum |     
+----+--------------------------+---------+
|  1 | 对我们的满意程度         |      20 |           
|  2 | 从哪里知道我们的         |      16 |           
|  3 | aaaaa                          |       0 |            
|  4 | bbbbb                         |       0 |            
|  5 | ccccc                           |       0 |            
|  6 | ddddd                         |       0 |            
+----+--------------------------+---------+


votemaster为投票主表

voter为投票者信息表--记录投票人IP及对应投票类型

左右连接实际说是我们联合查询的结果以哪个表为准～


1.右连接right join 或 right outer join

我们以右边表voter为准，则左表(votemaster)中的记录只有当其id在voter表中存在时才会显示出来

mysql> SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm RIGHT JOIN voter as vt ON vm.id=vt.id ;
+------+--------------------------+---------------+
| id   | voteTitle                      | ip                    |
+------+--------------------------+---------------+
|    1 | 对我们的满意程度         | 222.125.35.66 |
|    1 | 对我们的满意程度         | 127.0.0.1         |
|    2 | 从哪里知道我们的         | 127.0.0.1         |
+------+--------------------------+---------------+


2.左连接left join 或 left outer join

mysql> SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm LEFT JOIN voter as vt ON vm.id=vt.id ;
+----+--------------------------+---------------+
| id | voteTitle                      | ip            |
+----+--------------------------+---------------+
|  1 | 对我们的满意程度         | 222.125.35.66 |
|  1 | 对我们的满意程度         | 127.0.0.1     |
|  2 | 从哪里知道我们的         | 127.0.0.1     |
|  3 | aaaaa                           | NULL          |
|  4 | bbbbb                         | NULL          |
|  5 | ccccc                            | NULL          |
|  6 | ddddd                         | NULL          |
+----+--------------------------+---------------+


3.内连接inner join 或join

mysql> SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm JOIN voter as vt ON vm.id=vt.id ;
+----+--------------------------+---------------+
| id | voteTitle                      | ip            |
+----+--------------------------+---------------+
|  1 | 对我们的满意程度         | 222.125.35.66 |
|  1 | 对我们的满意程度         | 127.0.0.1     |
|  2 | 从哪里知道我们的         | 127.0.0.1     |
+----+--------------------------+---------------+


4.全连接full join 或 full outer join

注意！！mysql不支持full join，但还是有方法的


left join + union(可去除重复数据)+ right join

select * from A left join B on A.id = B.id (where 条件）
union
select * from A right join B on A.id = B.id （where条件);

在这个问题里

SELECT  vm.id , vm.voteTitle , vt.ip FROM votemaster as vm LEFT JOIN voter as vt ON vm.id=vt.id 

UNION

SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm RIGHT JOIN voter as vt ON vm.id=vt.id ;


5.交叉连接[完全连接]cross join [不带where条件的]

没有where子句的交叉联结将产生联结所涉及的表的笛卡儿积

第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小(votemaster和voter交叉连接产生6*3=18条记录)

 SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm CROSS JOIN voter as vt ;[这里没有where条件]

这个语句等价于SELECT vm.id , vm.voteTitle , vt.ip FROM votemaster as vm,voter as vt ;
+----+--------------------------+---------------+
| id | voteTitle                      | ip            |
+----+--------------------------+---------------+
|  1 | 对我们的满意程度         | 222.125.35.66 |
|  1 | 对我们的满意程度         | 127.0.0.1     |
|  1 | 对我们的满意程度         | 127.0.0.1     |
|  2 | 从哪里知道我们的         | 222.125.35.66 |
|  2 | 从哪里知道我们的         | 127.0.0.1     |
|  2 | 从哪里知道我们的         | 127.0.0.1     |
|  3 | aaaaa                    | 222.125.35.66 |
|  3 | aaaaa                    | 127.0.0.1     |
|  3 | aaaaa                    | 127.0.0.1     |
|  4 | bbbbb                    | 222.125.35.66 |
|  4 | bbbbb                    | 127.0.0.1     |
|  4 | bbbbb                    | 127.0.0.1     |
|  5 | ccccc                    | 222.125.35.66 |
|  5 | ccccc                    | 127.0.0.1     |
|  5 | ccccc                    | 127.0.0.1     |
|  6 | ddddd                    | 222.125.35.66 |
|  6 | ddddd                    | 127.0.0.1     |
|  6 | ddddd                    | 127.0.0.1     |
+----+--------------------------+---------------+


6.自连接

这个要再建一个表来演示一下

这里有一个表 Dep

这是一个部门表，里面存放了部门及其上级部门，但都放在了同一张表中


假设现在需要用sql查询出各部门及其上级部门

不用自连接也可以

SELECT DepartmentName 部门名 , 

(SELECT DepartmentName FROM Dep as inDep WHERE indep.DepID=outDep.pID) 上级部门名

FROM Dep as outDep

在这个查询中使用了一个子查询完成对上级部门名的查询，如果使用自连接，结构上感觉会清晰很多


自连接：

SELECT dep.DepartmentName 部门名 , jDep.DepartmentName 上级部门名 

FROM Dep dep LEFT JOIN Dep jDep ON dep.pID=jDep.depID

其实就是一个表，自己和自己连接


这里除了使用自连接外，还使用了左连接，因为省电力没有上级部门，

如果使用内连接，就会把这条记录过滤掉，因为没有匹配的上级部门

自连接用的比较多的就是对权形结构的查询，类似上表


nanaMasuda
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
解析:内联,左外联,右外联,全连接,交叉连接的区别
12-15
连接分为：内连接、外连接、交叉连接 一、内连接——最常用 定义：仅将两个表中满足连接条件的行组合起来作为结果集。 在内连接中，只有在两个表中匹配的行才能在结果集中出现 关键词：INNER JOIN 格式：SELECT 列名...
SQL 连接 JOIN 例解左连接，右连接，全连接，内连接，交叉连接，自连接
12-03
本文将详细介绍六种主要类型的 JOIN：左连接（LEFT JOIN）、右连接（RIGHT JOIN）、全连接（FULL JOIN）、内连接（INNER JOIN）、交叉连接（CROSS JOIN）以及自连接（SELF JOIN）。通过实例讲解每种连接的特点和应用...
数据库左连接、右连接、内连接、全连接区别
u012045045的博客
04-08 1442
基本定义：　　 　　left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。 　　right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。 　　inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。 　　full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。 1、内联接（典型...
什么是内连接、外连接、交叉连结、笛卡尔积？
✨ 欢迎来到【Seal ^_^ 的CSDN博客】！这里是我记录技术心得、分享经验的地方。✨
06-05 5762
在数据库查询中，连接（Join）是一种用于将两个或多个表中的行联合起来的操作。在实际应用中，常见的连接类型包括内连接、外连接、交叉连接以及全外连接，每种连接类型都有其独特的特点和用途。
MySQL-交叉连接 内连接 左连接 右连接 全连接详解
MrBlake的博客
10-12 1346
相当于交叉连接前先进行了筛选,比如筛选条件是两个表的ID相同.那么先筛选出ID为1的表1和表2中的数据行,然后对他们进行交叉连接.再筛选出ID为2的表1和表2中的数据行,然后对他们进行交叉连接,直到所有ID相同的数据行都被连接完.(前文说了因为他相当于先做了筛选),比如user_profile表中device_id为4321的复旦大学28岁的一个男性用户,因为在题库中没有他的答题信息所以被过滤掉了.那么如果我们想保留他,我们该怎么做呢,这就需要提到外连接.:表1通常包含在表2,表3。
内连接，外连接(左连接，右连接)，交叉连接(全连接)详解
啊大海全是水的博客
11-28 944
在sql 语法中从大类可分为3中连接方式 1 内连接  : inner join  其中 inner 可以省略不写 2 外连接，外连接又细分左连接(left join )和右连接(right join)  2部分 3 全连接 表1 如下所示  biaoa 表2 如下所示 biaob 内连接 ： select * from  biaoa a join b
PostgreSQL表连接：内连接，外连接，自连接，交叉连接
dugushangliang的博客
01-03 3626
版权声明：转载请注明作者（独孤尚良dugushangliang）出处：https://blog.csdn.net/dugushangliang/article/details/103817246 搜了搜，基本上都是写内连接、外连接、交叉连接这三种类型，但我发现PostgreSQL还有自连接。不妨一并写来做个记录。 先说概念： 内连接，就是两个表逐行匹配，匹配上的内容都显示，没有匹配的都...
【mysql】关系数据库内连接、外连接、交叉连接
m0_45406092的博客
01-01 1096
左外连接 Left outer join LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，排除在右表 (table_name2) 中没有匹配的行。 SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name 例： SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_
SQL的：内连接、左联接、右连接、全连接、交叉连接
探索世界，改变世界
08-30 794
SQL的：内连接、左联接、右连接、全连接、交叉连接
Oracle连接查询，彻底搞懂外连接（左外连接&右外连接）
热门推荐
ljinxin的博客
10-11 5万+
Oracle连接查询有3种：交叉连接、内连接、外连接。 交叉连接结果是其他连接结果的超集，外连接结果是内连接结果的超集。 接下的例子以departments_v、employees_v两个视图数据为例（4条部门数据，9条人员数据） 1.交叉连接：又称笛卡尔积连接，是两个或多个表间的无条件连接，因此它会将表1的每一条数据与表2的每一条数据连接，因此结果会有4*9=36条数据
内连接，外链接（左连接、右连接、全连接），交叉连接大总结
basycai的博客
08-14 1万+
1.什么是连接查询呢？     概念：根据两个表或多个表的列之间的关系，从这些表中查询数据。     目的：实现多个表查询操作。 2.分类： 首先划分一下，连接分为三种：内连接、外连接、交叉连接      内连接(INNER JOIN)：        分为三种：等值连接、自然连接、不等连接          外连接(
内连接（自然连接）、左/右/全外连接、自身连接、交叉连接、联合查询
书之岁华
10-26 1161
内连接 内连接也叫连接，自然连接。内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。 select fieldlist from table1 [inner] join table2 on table1.column=table2.column ...
数据库 左连接 右连接 全连接用法小结
09-14
本文主要讨论了四种类型的连接：内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（FULL JOIN），这些都是SQL-92标准定义的FROM子句连接语法的一部分。 1. **内连接**： 内连接返回满足...
SQL的几种连接：内连接、左联接、右连接、全连接、交叉连接
06-09
根据连接的方式和结果，SQL连接可以分为内连接、左连接、右连接、全连接和交叉连接等多种类型。 内连接 内连接是最基本的连接类型，它将两个表格中的数据根据某个条件连接起来。内连接可以分为等值连接、不等值...
右连接、左连接、全连接、内连接、交叉连接、自连接.pdf
10-04
全连接结合了左连接和右连接，返回两个表中所有记录，无论是否存在匹配。如果某条记录在其中一个表中没有匹配项，结果将用NULL填充缺失的列。 4. **内连接（Inner Join 或 Join）**： 内连接是最常见的连接类型，...
Redis基础命令和事务，redis持久化和主从复制
baomingshu的博客
08-31 1049
nosql数据库介绍：解释为 non-relational(非关系型数据库)。在NoSQL 数据库中数据之间是无联系的数据的结构是松散的，可变的。优势：大数据量，高性能，灵活的数据模型，高可用，低成本劣势：（1）无关系，数据之间是无联系的。
mysql-day02
m0_64370841的博客
08-29 1964
【代码】mysql-day02。
数据库管理-第236期 数据库一体机的价值（20240829）
yhw1809的博客
08-29 1396
数据库一体机是硬件、操作系统和数据库之间的融合，利用更少的硬件实现小体积的高性能密度。
编译原理简介
最新发布
m0_52796585的博客
09-01 579
编译原理是计算机科学中的一个核心领域，它涉及到将高级编程语言编写的源代码转换成机器语言的过程。这个过程对于计算机程序的运行至关重要，因为计算机硬件只能理解机器语言指令。编译原理不仅关注编译器的设计和实现，还涉及语言的语义、语法分析、代码优化等多个方面。
MySQL连接查询详解：内连接、外连接、交叉连接
SQL92标准只支持内连接，而SQL99标准（推荐使用）不仅支持内连接，还增加了对外连接（左外连接、右外连接）和交叉连接的支持。 1. 内连接（INNER JOIN）： - 等值连接：基于指定的等式条件，返回两个表中匹配的行...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值