sql中的left join及on、where条件关键字的区别详解

2023-09-08 13:18:05 142

LEFTJOIN关键字会从左表(table_name1)那里返回所有的行，即使在右表(table_name2)中没有匹配的行。

LEFTJOIN关键字语法

SELECTcolumn_name(s)
FROMtable_name1
LEFTJOINtable_name2
ONtable_name1.column_name=table_name2.column_name

注释：在某些数据库中，LEFTJOIN称为LEFTOUTERJOIN。

创建两张表并插入一些数据

createtableclass(
class_idint,
class_namevarchar(20),
class_gradechar(1)
);
insertintoclassvalues(1,'语文','A');
insertintoclassvalues(2,'数学','B');
insertintoclassvalues(3,'英语','C');
createtablescore(
class_idint,
stu_idvarchar(20),
Scoreint
);
insertintoscorevalues(1,'A001',91);
insertintoscorevalues(2,'A001',95);
insertintoscorevalues(1,'A002',82);
insertintoscorevalues(2,'A002',87);
insertintoscorevalues(3,'B003',65);

查看表中的数据

mysql>select*fromclass;
+----------+------------+-------------+
|class_id|class_name|class_grade|
+----------+------------+-------------+
|1|语文|A|
|2|数学|B|
|3|英语|C|
+----------+------------+-------------+
3rowsinset(0.00sec)
mysql>select*fromscore;
+----------+--------+-------+
|class_id|stu_id|Score|
+----------+--------+-------+
|1|A001|91|
|2|A001|95|
|1|A002|82|
|2|A002|87|
|3|B003|65|
+----------+--------+-------+
5rowsinset(0.00sec)
mysql>

比较下面几组查询结果

--如果你对leftjoin足够熟悉的话，先不要看结果，是否可以直接说出下面查询的结果

mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_id;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|1|A001|91|
|2|数学|B|2|A001|95|
|1|语文|A|1|A002|82|
|2|数学|B|2|A002|87|
|3|英语|C|3|B003|65|
+----------+------------+-------------+----------+--------+-------+
5rowsinset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idand1=1;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|1|A001|91|
|2|数学|B|2|A001|95|
|1|语文|A|1|A002|82|
|2|数学|B|2|A002|87|
|3|英语|C|3|B003|65|
+----------+------------+-------------+----------+--------+-------+
5rowsinset(0.01sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idand1=0;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|NULL|NULL|NULL|
|2|数学|B|NULL|NULL|NULL|
|3|英语|C|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
3rowsinset(0.00sec)
mysql>select*fromclassAleftjoinscoreBon1=0;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|NULL|NULL|NULL|
|2|数学|B|NULL|NULL|NULL|
|3|英语|C|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
3rowsinset(0.00sec)

mysql>leftjoin的最重要特点是：不管on后面是什么条件，都会返回左表中的所有行！

mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idandA.class_name='语文';
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|1|A001|91|
|1|语文|A|1|A002|82|
|2|数学|B|NULL|NULL|NULL|
|3|英语|C|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
4rowsinset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idandA.class_name='数学';
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|2|数学|B|2|A001|95|
|2|数学|B|2|A002|87|
|1|语文|A|NULL|NULL|NULL|
|3|英语|C|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
4rowsinset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idandA.class_name='英语';
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|3|英语|C|3|B003|65|
|1|语文|A|NULL|NULL|NULL|
|2|数学|B|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
3rowsinset(0.01sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idandA.class_name='体育';
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|NULL|NULL|NULL|
|2|数学|B|NULL|NULL|NULL|
|3|英语|C|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
3rowsinset(0.00sec)
mysql>

如果on后面的条件是左表中的列(andleftTable.colName='***'),左表中满足条件的行和右表中的行进行匹配(根据onleftTable.id=rightTable.id)；左表中不满足条件的行，直接输出，其对应的右表中的列都是null。

mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idandB.Score=90;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|NULL|NULL|NULL|
|2|数学|B|NULL|NULL|NULL|
|3|英语|C|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
3rowsinset(0.01sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idandB.Score=65;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|3|英语|C|3|B003|65|
|1|语文|A|NULL|NULL|NULL|
|2|数学|B|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
3rowsinset(0.01sec)
mysql>

如果on后面的条件是右表中的列(andrightTable.colName='***'),首先会根据(andrightTable.colName='***')过滤掉右表中不满足条件的行；然后，左表中的行根据(onleftTable.id=rightTable.id)和右表中满足条件的行进行匹配。

mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idandA.class_name='语文'andB.Score=90;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|NULL|NULL|NULL|
|2|数学|B|NULL|NULL|NULL|
|3|英语|C|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
3rowsinset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idandA.class_name='语文'andB.Score=91;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|1|A001|91|
|2|数学|B|NULL|NULL|NULL|
|3|英语|C|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
3rowsinset(0.01sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idandA.class_name='体育'andB.Score=90;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|NULL|NULL|NULL|
|2|数学|B|NULL|NULL|NULL|
|3|英语|C|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
3rowsinset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idandA.class_name='体育'andB.Score=82;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|NULL|NULL|NULL|
|2|数学|B|NULL|NULL|NULL|
|3|英语|C|NULL|NULL|NULL|
+----------+------------+-------------+----------+--------+-------+
3rowsinset(0.00sec)
mysql>

/**********************过滤条件在on中时**********************/

总结一下，如果leftjoinonleftTable.id=rightTable.id后还有其他条件：

(1)andleftTable.colName='***'，过滤左表，但是左表不满足条件的行直接输出，并将右表对应部分置为null
(2)andrightTable.colName='***'，过滤右表，对左表没有影响
(3)andleftTable.colName='***'andrightTable.colName='***'，就是上面(1)和(2)一起发挥作用

不管on后面有哪些条件，leftjoin都要返回左表中的所有行！

mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idwhereA.class_name='语文';
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|1|A001|91|
|1|语文|A|1|A002|82|
+----------+------------+-------------+----------+--------+-------+
2rowsinset(0.01sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idwhereA.class_name='数学';
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|2|数学|B|2|A001|95|
|2|数学|B|2|A002|87|
+----------+------------+-------------+----------+--------+-------+
2rowsinset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idwhereA.class_name='英语';
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|3|英语|C|3|B003|65|
+----------+------------+-------------+----------+--------+-------+
1rowinset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idwhereA.class_name='体育';
Emptyset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idwhereB.Score=90;
Emptyset(0.01sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idwhereB.Score=91;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|1|A001|91|
+----------+------------+-------------+----------+--------+-------+
1rowinset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idwhereA.class_name='语文'andB.Score=90;
Emptyset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idwhereA.class_name='语文'andB.Score=91;
+----------+------------+-------------+----------+--------+-------+
|class_id|class_name|class_grade|class_id|stu_id|Score|
+----------+------------+-------------+----------+--------+-------+
|1|语文|A|1|A001|91|
+----------+------------+-------------+----------+--------+-------+
1rowinset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idwhereA.class_name='体育'andB.Score=90;
Emptyset(0.00sec)
mysql>select*fromclassAleftjoinscoreBonA.class_id=B.class_idwhereA.class_name='体育'andB.Score=91;
Emptyset(0.00sec)
mysql>

/**********************过滤条件在where中时**********************/

过滤条件写在where中时，先根据where条件对表进行过滤，然后再执行leftjoin

总结

以上所述是小编给大家介绍的sql中的leftjoin及on、where关键字的区别详解，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！

sql中的left join及on、where条件关键字的区别详解

热门推荐

随机推荐