目录
1 数据库设计
表名A
表A数据
/*创建表*/
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`id` int NOT NULL,
`address` varchar(255) DEFAULT NULL,
`age` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*创建数据*/
INSERT INTO `a` VALUES ('1', '上海', '23', '李四');
INSERT INTO `a` VALUES ('2', '北京', '33', '张三');
INSERT INTO `a` VALUES ('3', '哈哈', '23', '亲戚');
INSERT INTO `a` VALUES ('4', '深圳', '85', '李四');
表名B
表B的数据
/*创建表*/
DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
`id` int NOT NULL,
`age` int DEFAULT NULL,
`sex` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*创建数据*/
INSERT INTO `b` VALUES ('1', '20', '男', '李四');
INSERT INTO `b` VALUES ('2', '30', '女', '马六');
INSERT INTO `b` VALUES ('3', '10', '男', '李四');
2 外连接(out join)
在左连接和右连接时都会以一张A表为基础表,该表的内容会全部显示,然后加上A表和B表匹配的内容。 如果A表的数据在B表中没有记录。 那么在相关联的结果集行中列显示为空值(NULL)。
外连接分为外左连接(left outer join)和外右连接(right outer join)
注释:left outer join 与 left join 等价, 一般写成left join
right outer join 与 right join等价,一般写成right join
左连接,取左边的表的全部,右边的表按条件,符合的显示,不符合则显示null
举例:select <select list> from A left join B on A.id=B.id
右连接:取右边的表的全部,左边的表按条件,符合的显示,不符合则显示null
举例:select <select list> from A right join B on A.id=B.id
1. SELECT * from A LEFT JOIN B ON A.id= B.id and A.`name` = B.`name`
SELECT * from A LEFT JOIN B ON A.id= B.id and A.`name` = B.`name`
可以看到A表中所有数据,满足条件(A.id=B.id AND A.name=B.name)的B表的数据
2. SELECT * from A LEFT JOIN B ON A.id= B.id WHERE A.name = B.name
SELECT * from A LEFT JOIN B ON A.id= B.id WHERE A.name = B.name
这里是在SELECT * from A LEFT JOIN B ON A.id= B.id作为一张中间的临时表,然后再将这张临时表满足条件的数据返回给用户!
3. SELECT * from A LEFT JOIN B ON A.id= B.id
SELECT * from A LEFT JOIN B ON A.id= B.id
可以看到A表中所有数据,满足条件(A.id=B.id )的B表的数据
4. SELECT * from A LEFT JOIN B ON A.name = B.name
特别注意:这个属于A表中所有数据,和满足条件的B表数据;但是由于A作为主表,B作为从表有着一对多的关系。所以会多次显示A中数据。
5. SELECT * from A LEFT JOIN B ON A.name= B.name WHERE B.age =10
SELECT * from A LEFT JOIN B ON A.name= B.name WHERE B.age =10
由以上 3条 SQL 的分析 可知
1. sql 中的 on 和 where 的 区别?
数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户!
在使用left jion时,on和where条件的区别如下:
1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
2. Left Join
select * from tbl1 Left Join tbl2 where tbl1.ID = tbl2.ID
左连接后的检索结果是显示tbl1的所有数据和tbl2中满足where 条件的数据。
简言之 Left Join影响到的是右边的表
6 SELECT * from A RIGHT JOIN B on A.id = B.id
SELECT * from A RIGHT JOIN B on A.id = B.id
B中所有数据都显示,A中有一条数据没有显示,只显示符合条件的
7 SELECT * from A RIGHT JOIN B ON A.id= B.id WHERE A.name = B.name
SELECT * from A RIGHT JOIN B ON A.id= B.id WHERE A.name = B.name
8 SELECT * from A RIGHT JOIN B on A.id = B.id AND A.name = B.name WHERE B.age =10
SELECT * from A RIGHT JOIN B on A.id = B.id AND A.name = B.name WHERE B.age =10
分析可知:
当进行 右连接的时候 会显示 的 显示 右边表的数据 和 满足 左边表的消息
select * from tbl1 Right Join tbl2 where tbl1.ID = tbl2.ID 检索结果是tbl2的所有数据和tbl1中满足where 条件的数据。
简言之 Right Join影响到的是左边的表。
关于左连接和右连接总结性的一句话:
左连接where只影向右表,右连接where只影响左表。
3 内连接(inner join)
内连接:也称为等值连接,返回两张表都满足条件的部分
注释:inner join 就等于 join
inner join:理解为“有效连接”,两张表中都有的数据才会显示
1 .SELECT * from A INNER JOIN B on A.id = B.id
SELECT * from A INNER JOIN B on A.id = B.id
2 SELECT * from A INNER JOIN B on A.name = B.name
SELECT * from A INNER JOIN B on A.name = B.name
内连接和where的区别:
数据库表连接数据行匹配时所遵循的算法就是以上提到的笛卡尔积,表与表之间的连接可以看成是在做乘法运算。
比如现在数据库中有两张表,A表和B表,如下所示:
表A
表B
我们执行以下的sql语句,只是纯粹的进行表连接。
SELECT * from A JOIN B;
SELECT * from B JOIN A;
从执行结果上来看,结果符合我们以上提出的两点结论(红线标注部分);
以第一条sql语句为例我们来看一下他的执行流程,
1,from语句把A表 和 B表从数据库文件加载到内存中。
2,join语句相当于对两张表做了乘法运算,把A表中的每一行记录按照顺序和student_subject表中记录依次匹配。
3,匹配完成后,我们得到了一张有 (A中记录数 × B表中记录数)条的临时表。 在内存中形成的临时表如表1.0所示。我们又把内存中表1.0所示的表称为‘笛卡尔积表’。
再看一下sql中主要关键字的执行顺序:
from
on
join
where
group by
having
select
distinct
union
order by
我们看到on是在join和where前面的
如果两张表的数据量都比较大的话,那样就会占用很大的内存空间这显然是不合理的。所以,我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法,ON语句的执行是在JOIN语句之前的,也就是说两张表数据行之间进行匹配的时候,会先判断数据行是否符合ON语句后面的条件,再决定是否JOIN。
因此,有一个显而易见的SQL优化的方案是,当两张表的数据量比较大,又需要连接查询时,应该使用 FROM table1 JOIN table2 ON xxx的语法,避免使用 FROM table1,table2 WHERE xxx 的语法,因为后者会在内存中先生成一张数据量比较大的笛卡尔积表,增加了内存的开销。
4 全连接(Full join)
全外连接是在结果中除了显示满足连接的条件的行外,还显示了join两侧表中所有满足检索条件的行
SELECT * from A Full join B on A.id = B.id
注意:MySQL是不支持全外的连接的,这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。下图是上面SQL在Oracle下执行的结果:
5 交叉连接
使用cross joi实现交叉连接,可以将两个表的交叉连接,所得到的结果是将这两个表中各行数据的所有的组合,即这两个表所有数据行的笛卡儿积。
交叉连接与简单连接操作非常相似,不同的是,使用交叉连接时,在from子句中多个表名之间不是逗号,而是使用cross join关键字隔开。令外,在交叉连接中不需要使用关键字on限定连接条件,但是可以添加where子句设置连接条件。