1.数据准备 建两个表格：
create table student
(idstu int,
namestu varchar(50)
); insert into weiying.student values(1,"张三")(2,"李四"),(3,"王五"),(4,"赵六") create table weiying.score
(
idscore int,
inall int
); insert into weiying.score values(2,198),(3,165),(4,178),(5,159) SELECT * FROM weiying.score;
SELECT * FROM weiying.student;
结果如下：
2.内连接 INNER JOIN ，交集
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
例子如下：
SELECT *
FROM weiying.student as stu
INNER JOIN weiying.score as sco
ON stu.idstu = sco.idscore
结果：
3.LEFT JOIN以左表为主，右表为辅，关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
例子：
SELECT *
FROM weiying.student as stu
LEFT JOIN weiying.score as sco
ON stu.idstu = sco.idscore
结果：
4.3.LEFT JOIN 右表 (table_name2) 为空的情况（is null）
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Where table_name2.column_name is null
例子：
SELECT *
FROM weiying.student as stu
LEFT JOIN weiying.score as sco
ON stu.idstu = sco.idscore
Where sco.idscore is null
结果：
5. FULL JOIN，只要其中某个表存在匹配，FULL JOIN 关键字就会返回行，去全集时
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
例子：
SELECT *
FROM weiying.student as stu
FULL JOIN weiying.score as sco
ON stu.idstu = sco.idscore
5. FULL JOIN 去掉中间
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Where table_name2.column_name is null
or table_name1.column_name is null
例子：
SELECT *
FROM weiying.student as stu
FULL JOIN weiying.score as sco
ON stu.idstu = sco.idscore
Where sco.idscore is null
or stu.idstu is null
6.RIGHT JOIN以右表为主，左表为辅，关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
例子：
SELECT *
FROM weiying.student as stu
RIGHT JOIN weiying.score as sco
ON stu.idstu = sco.idscore
结果：
7.RIGHT JOIN 取补集；
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
where table_name1.column_name is null
例子：
SELECT *
FROM weiying.student as stu
RIGHT JOIN weiying.score as sco
ON stu.idstu = sco.idscore
where stu.idstu is null
结果：