SQL JOIN 连接

sql语法

1.什么是join(连接)

JOIN(连接)是sql的操作之一,如果我们想根据两个或以上的表的列之间的关系来对这些表进行查询,就应该考虑使用join

2.什么时候使用join

当需要进行多表联查的时候

3.join(连接)的分类

例表:
学生表student:

成绩表mark:

3.1内连接(INNER JOIN)

INNER JOIN是我们最经常用到连接,只返回两个表中连接字段相等的行,我们经常会写到这样的连接查询:

select a.name,b.mark from student as a ,marks as b where a.id=b.studentId

也可以写成:

select a.name,b.marks from student as a inner join mark as b on a.id=b.studentId

所以当我们只想要得到两张表的关联信息的时候,我们就可以用inner join 所以结果是;

3.2左/右外连接

外链接分为左外连接和右外链接.左连接会返回左表所有的行和右表与之相匹配的行,如果左表的行的数据在右表中没有找到相匹配的数据,那么返回集中相对应的右表的信息就会用null填充.
例如我们对上边的表进行左连接:

select a.name,b.mark from student as a left join mark as b on a.id = b.studentId

也可以写成

select a.name,b.mark from student as a left join mark as b on a.id=b.studentId

返回结果是:

如表中,结果集中将student表中所有的行以及mark表中与student表中相关的行都查找出来,而student表中在mark表中没有相匹配行的"王五"的"mark"就是null
所以当我们想获得左表中全部消息同时把右表中相关联的信息显示出来的时候,我们就可以用做链接.比如我们想打印一个班级里每个人的成绩,但是有熊孩子交了白卷,但是我们还是要打印出他的名字来,就可以用左连接.

相同的,右连接就是把右表的所有行都显示出来,然后在左表中有匹配信息的就显示出来,没有匹配的行就显示为null:
如:

select a.name,b.mark from student as a right join mark as b on a.id=b.studnetId

结果如下:

3.3全连接:

全连接full join会返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值,所以当我们不光想知道左右两表的匹配行,还想知道他们没有匹配到的行,就可以用full join例如:

select a.name ,b.mark from student as a full join mark as b where a.id=b.studentid

特别的,mysql是不支持 full join的(T.T害我来回打了好多遍sql语句),但是可以通过以下语句来实现全连接:

select * FROM student AS a LEFT JOIN mark AS b ON a.id=b.studentid UNION 
select * FROM student AS a RIGHT JOIN mark AS b ON a.id=b.studentid

如上语句,先执行了左查询,再执行右查询,将两个结果集去重,即如果如下:

3.4交叉连接:

交叉连接(cross join)是一种没有where语句的查询,使用交叉连接会返回两个数据表的笛卡尔积,即他返回的行数是两张表行数乘积.
//至于情况下使用交叉连接,交叉连接到底有什么用还没有搞清楚-.-

可以在交叉连接后面跟上where语句,但是这个where语句是对结果集进行操作的,并没有影响到交叉连接本身.
同样的内外连接都可以在其后跟上where来对结果集进行操作,但是where的条件是作用于结果集而不是在执行内外连接的过程中限制的.

3.5相关知识点:

on and与on where的区别:

on是指限制连接操作的条件,他会影响执行内外执行结果;而where是对内外连接的结果集进行操作的.也就是说,当执行连接语句的时候,会先取on后面跟的条件,多个条件通过and连接,执行完以后会得到一个结果集,得到结果集之后再执行where语句,对结果集进行筛选,得到最后我们想要得到结果集.
例如我们分别执行下面两条语句:

SELECT a.name,b.mark FROM student AS a LEFT JOIN mark AS b on a.id = b.studentid AND b.mark=88           //ON AND
SELECT a.name,b.mark FROM student AS a LEFT JOIN mark AS b on a.id = b.studentid where b.mark=88         //ON WHERE

ON AND 的执行结果:

明显的,on and返回的是一个左连接查询的结果集,说明and连接的两个条件都用来当做左连接的限制条件

ON WHERE的执行结果

可以看出,我们可以看一下3.2里左连接语句的执行结果,然后在加上这个where语句的限制,正好查询结果能够对上,说明where语句是对left join的结果集进行操作的.

4相关知识点连接:

1.详解SQL Server连接（内连接、外连接、交叉连接）
2.MySQL Full Join的实现
3.SQL的内连接与外连接
4.SQL左右连接中的on and和on where的区别