my sql 下左连接 右链接、内连接等应用，INNER JOIN LEFT JOIN RIGHT JOIN

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

　　结果：