mysql左右连接的区别_my sql 下左连接 右链接、内连接等应用，INNER JOIN LEFT JOIN RIGHT JOIN...

1.数据准备 建两个表格：

1 create tablestudent2 (idstu int,3 namestu varchar(50)4 );5

6 insert into weiying.student values(1,"张三")(2,"李四"),(3,"王五"),(4,"赵六")7

8 create tableweiying.score9 (10 idscore int,11 inall int

12 );13

14 insert into weiying.score values(2,198),(3,165),(4,178),(5,159)15

16 SELECT * FROMweiying.score;17 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

结果：