Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > body text

Analysis of join usage in MySQL

黄舟
Release： 2017-02-27 11:54:42
Original
1820 people have browsed it


Analysis of join usage in MySQL


The example database is as follows:
student table: 

mysql> select * from student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    |
| 201215122 | 刘晨      | 女   |   19 | CS    |
| 201215123 | 王敏      | 女   |   18 | MA    |
| 201215125 | 张立      | 男   |   19 | IS    |
| 201215128 | 陈冬      | 男   |   18 | IS    |
| 201215126 | 张成民    | 男   |   18 | CS    |
+-----------+-----------+------+------+-------+6 rows in set (0.00 sec)
Copy after login

sc Table: 

mysql> select * from sc;
+-----------+------+-------+
| Sno       | Cno  | Grade |
+-----------+------+-------+
| 201215121 |    1 |    92 |
| 201215121 |    2 |    85 |
| 201215121 |    3 |    88 |
| 201215122 |    2 |    90 |
| 201215122 |    3 |    80 |
| 201215128 |    1 |    78 |
+-----------+------+-------+6 rows in set (0.00 sec)
Copy after login

LEFT JOIN (left join)

Analysis of join usage in MySQL
The sql statement is as follows: 

select * from student left join sc on student.Sno=sc.Sno;
Copy after login
Copy after login

The running results are as follows: 

+-----------+-----------+------+------+-------+-----------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
+-----------+-----------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    | 201215128 |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    |      NULL | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    |      NULL | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    |      NULL | NULL |  NULL |
+-----------+-----------+------+------+-------+-----------+------+-------+
Copy after login
Copy after login

in In this example, the left join is based on the records in the student table. The student table can be regarded as the left table, and the sc table can be regarded as the right table. The records in the left table will be fully displayed, plus the matching right table. If If there is no match on the left side, the remaining parts will be displayed as null.

USING clause

The using clause and the on clause are similar, but the results are slightly different.
For example: 

mysql> select student.Sno,Sname,Grade from student left join sc on student.Sno=sc.Sno;
+-----------+-----------+-------+
| Sno       | Sname     | Grade |
+-----------+-----------+-------+
| 201215121 | 李勇      |    92 |
| 201215121 | 李勇      |    85 |
| 201215121 | 李勇      |    88 |
| 201215122 | 刘晨      |    90 |
| 201215122 | 刘晨      |    80 |
| 201215128 | 陈冬      |    78 |
| 201215123 | 王敏      |  NULL |
| 201215125 | 张立      |  NULL |
| 201215126 | 张成民    |  NULL |
+-----------+-----------+-------+
9 rows in set (0.00 sec)
Copy after login

The above is equivalent to 

select Sno,Sname,Grade from student left join sc using(Sno);
+-----------+-----------+-------+
| Sno       | Sname     | Grade |
+-----------+-----------+-------+
| 201215121 | 李勇      |    92 |
| 201215121 | 李勇      |    85 |
| 201215121 | 李勇      |    88 |
| 201215122 | 刘晨      |    90 |
| 201215122 | 刘晨      |    80 |
| 201215128 | 陈冬      |    78 |
| 201215123 | 王敏      |  NULL |
| 201215125 | 张立      |  NULL |
| 201215126 | 张成民    |  NULL |
+-----------+-----------+-------+
Copy after login

in different places, for example: 

select * from student left join sc on student.Sno=sc.Sno;
+-----------+-----------+------+------+-------+-----------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
+-----------+-----------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    | 201215128 |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    |      NULL | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    |      NULL | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    |      NULL | NULL |  NULL |
+-----------+-----------+------+------+-------+-----------+------+-------+
Copy after login
Copy after login
select * from student left join sc using (sno);
+-----------+-----------+------+------+-------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+-----------+------+------+-------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
+-----------+-----------+------+------+-------+------+-------+
Copy after login
Copy after login

The repeated Sno column will be output twice if the on word is used.

RIGHT JOIN (right join)

Same as LEFT JOIN, but based on the right table, for example: 

 select * from student right join sc using (sno);
+-----------+------+-------+--------+------+------+-------+
| Sno       | Cno  | Grade | Sname  | Ssex | Sage | Sdept |
+-----------+------+-------+--------+------+------+-------+
| 201215121 |    1 |    92 | 李勇   | 男   |   22 | CS    |
| 201215121 |    2 |    85 | 李勇   | 男   |   22 | CS    |
| 201215121 |    3 |    88 | 李勇   | 男   |   22 | CS    |
| 201215122 |    2 |    90 | 刘晨   | 女   |   19 | CS    |
| 201215122 |    3 |    80 | 刘晨   | 女   |   19 | CS    |
| 201215128 |    1 |    78 | 陈冬   | 男   |   18 | IS    |
+-----------+------+-------+--------+------+------+-------+
Copy after login
Copy after login

INNER JOIN (equal join or inner join)

Analysis of join usage in MySQL

It will not show who it is based on, only the records that meet the conditions will be displayed

 select * from student inner join sc on student.Sno=sc.Sno;
+-----------+--------+------+------+-------+-----------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
+-----------+--------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    1 |    92 |
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    2 |    85 |
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    3 |    88 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    2 |    90 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    3 |    80 |
| 201215128 | 陈冬   | 男   |   18 | IS    | 201215128 |    1 |    78 |
+-----------+--------+------+------+-------+-----------+------+-------+
Copy after login
Copy after login

The above statement is equivalent to: 

select * from student,sc where student.Sno=sc.Sno;
+-----------+--------+------+------+-------+-----------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
+-----------+--------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    1 |    92 |
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    2 |    85 |
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    3 |    88 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    2 |    90 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    3 |    80 |
| 201215128 | 陈冬   | 男   |   18 | IS    | 201215128 |    1 |    78 |
+-----------+--------+------+------+-------+-----------+------+-------+
Copy after login
Copy after login

Extension

If you only want to retrieve some records from table A, but do not include table B

Analysis of join usage in MySQL

You can add a where statement after the left join

select * from student left join sc using(Sno) where sc.Sno is null;
+-----------+-----------+------+------+-------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+-----------+------+------+-------+------+-------+
| 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
+-----------+-----------+------+------+-------+------+-------+
Copy after login
Copy after login

Find the difference set

Analysis of join usage in MySQL

#You can combine union words. Since in this example, all the ones on the right are corresponding, so the displayed result is consistent with the previous one. 

select * from student left join sc using(Sno) where student.Sno is null union 
select * from student left join sc using(Sno) where sc.Sno is null;
+-----------+-----------+------+------+-------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+-----------+------+------+-------+------+-------+
| 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
+-----------+-----------+------+------+-------+------+-------+
Copy after login
Copy after login

FULL JOIN

Analysis of join usage in MySQL

 select * from student left join sc on student.Sno=sc.Sno union  select * from student right join sc on student.Sno=sc.Sno;
+-----------+-----------+------+------+-------+-----------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
+-----------+-----------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    | 201215128 |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    |      NULL | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    |      NULL | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    |      NULL | NULL |  NULL |
+-----------+-----------+------+------+-------+-----------+------+-------+
Copy after login
Copy after login

Note: A left join B is equivalent to B right join A

mysql> select * from student left join sc using(Sno);
+-----------+-----------+------+------+-------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+-----------+------+------+-------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
+-----------+-----------+------+------+-------+------+-------+
9 rows in set (0.00 sec)mysql> select * from sc right join student using(Sno);
+-----------+-----------+------+------+-------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+-----------+------+------+-------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
+-----------+-----------+------+------+-------+------+-------+
Copy after login
Copy after login

Join usage analysis in MySQL

The instance database is as follows:
student table: 

mysql> select * from student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    |
| 201215122 | 刘晨      | 女   |   19 | CS    |
| 201215123 | 王敏      | 女   |   18 | MA    |
| 201215125 | 张立      | 男   |   19 | IS    |
| 201215128 | 陈冬      | 男   |   18 | IS    |
| 201215126 | 张成民    | 男   |   18 | CS    |
+-----------+-----------+------+------+-------+
6 rows in set (0.00 sec)
Copy after login

sc table: 

mysql> select * from sc;
+-----------+------+-------+
| Sno       | Cno  | Grade |
+-----------+------+-------+
| 201215121 |    1 |    92 |
| 201215121 |    2 |    85 |
| 201215121 |    3 |    88 |
| 201215122 |    2 |    90 |
| 201215122 |    3 |    80 |
| 201215128 |    1 |    78 |
+-----------+------+-------+
6 rows in set (0.00 sec)
Copy after login

LEFT JOIN (left join)

Analysis of join usage in MySQL##sql The statement is as follows: 

select * from student left join sc on student.Sno=sc.Sno;
Copy after login
Copy after login

The running result is as follows: 

+-----------+-----------+------+------+-------+-----------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
+-----------+-----------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    | 201215128 |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    |      NULL | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    |      NULL | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    |      NULL | NULL |  NULL |
+-----------+-----------+------+------+-------+-----------+------+-------+
Copy after login
Copy after login

In this example, the left join is based on the records in the student table. The student table can be regarded as the left table, and the sc table can be regarded as In the right table, the records in the left table will be fully displayed, plus the matched right table. If the left table is not matched, the remaining parts will be displayed as null.

USING clause

The using clause and the on clause are similar, but the results are slightly different.

For example: 

mysql> select student.Sno,Sname,Grade from student left join sc on student.Sno=sc.Sno;
+-----------+-----------+-------+| Sno       | Sname     | Grade |
+-----------+-----------+-------+| 201215121 | 李勇      |    92 |
| 201215121 | 李勇      |    85 |
| 201215121 | 李勇      |    88 |
| 201215122 | 刘晨      |    90 |
| 201215122 | 刘晨      |    80 |
| 201215128 | 陈冬      |    78 |
| 201215123 | 王敏      |  NULL |
| 201215125 | 张立      |  NULL || 201215126 | 张成民    |  NULL |
+-----------+-----------+-------+9 rows in set (0.00 sec)
Copy after login

The above is equivalent to 

select Sno,Sname,Grade from student left join sc using(Sno);
+-----------+-----------+-------+| Sno       | Sname     | Grade |
+-----------+-----------+-------+| 201215121 | 李勇      |    92 |
| 201215121 | 李勇      |    85 |
| 201215121 | 李勇      |    88 |
| 201215122 | 刘晨      |    90 |
| 201215122 | 刘晨      |    80 |
| 201215128 | 陈冬      |    78 |
| 201215123 | 王敏      |  NULL |
| 201215125 | 张立      |  NULL || 201215126 | 张成民    |  NULL |
+-----------+-----------+-------+
Copy after login

in different places, for example: 

select * from student left join sc on student.Sno=sc.Sno;
+-----------+-----------+------+------+-------+-----------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
+-----------+-----------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    | 201215128 |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    |      NULL | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    |      NULL | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    |      NULL | NULL |  NULL |
+-----------+-----------+------+------+-------+-----------+------+-------+
Copy after login
Copy after login
select * from student left join sc using (sno);
+-----------+-----------+------+------+-------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+-----------+------+------+-------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
+-----------+-----------+------+------+-------+------+-------+
Copy after login
Copy after login

The repeated Sno column will be output twice if the on word is used.

RIGHT JOIN (right join)

Same as LEFT JOIN, but based on the right table, for example: 

 select * from student right join sc using (sno);
+-----------+------+-------+--------+------+------+-------+
| Sno       | Cno  | Grade | Sname  | Ssex | Sage | Sdept |
+-----------+------+-------+--------+------+------+-------+
| 201215121 |    1 |    92 | 李勇   | 男   |   22 | CS    |
| 201215121 |    2 |    85 | 李勇   | 男   |   22 | CS    |
| 201215121 |    3 |    88 | 李勇   | 男   |   22 | CS    |
| 201215122 |    2 |    90 | 刘晨   | 女   |   19 | CS    |
| 201215122 |    3 |    80 | 刘晨   | 女   |   19 | CS    |
| 201215128 |    1 |    78 | 陈冬   | 男   |   18 | IS    |
+-----------+------+-------+--------+------+------+-------+
Copy after login
Copy after login

INNER JOIN (equal join or inner join)

Analysis of join usage in MySQL

It will not show who it is based on, only the records that meet the conditions will be displayed

 select * from student inner join sc on student.Sno=sc.Sno;
+-----------+--------+------+------+-------+-----------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
+-----------+--------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    1 |    92 |
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    2 |    85 |
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    3 |    88 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    2 |    90 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    3 |    80 |
| 201215128 | 陈冬   | 男   |   18 | IS    | 201215128 |    1 |    78 |
+-----------+--------+------+------+-------+-----------+------+-------+
Copy after login
Copy after login

The above statement is equivalent to: 

select * from student,sc where student.Sno=sc.Sno;
+-----------+--------+------+------+-------+-----------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
+-----------+--------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    1 |    92 |
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    2 |    85 |
| 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    3 |    88 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    2 |    90 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    3 |    80 |
| 201215128 | 陈冬   | 男   |   18 | IS    | 201215128 |    1 |    78 |
+-----------+--------+------+------+-------+-----------+------+-------+
Copy after login
Copy after login

Extension

If you only want to retrieve some records from table A, but do not include table B

Analysis of join usage in MySQL

You can add a where statement after the left join

select * from student left join sc using(Sno) where sc.Sno is null;
+-----------+-----------+------+------+-------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+-----------+------+------+-------+------+-------+
| 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
+-----------+-----------+------+------+-------+------+-------+
Copy after login
Copy after login

Find the difference set

Analysis of join usage in MySQL

#You can combine union words. Since in this example, all the ones on the right are corresponding, so the displayed result is consistent with the previous one. 

select * from student left join sc using(Sno) where student.Sno is null union 
select * from student left join sc using(Sno) where sc.Sno is null;
+-----------+-----------+------+------+-------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+-----------+------+------+-------+------+-------+
| 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
+-----------+-----------+------+------+-------+------+-------+
Copy after login
Copy after login

FULL JOIN

Analysis of join usage in MySQL

 select * from student left join sc on student.Sno=sc.Sno union  select * from student right join sc on student.Sno=sc.Sno;
+-----------+-----------+------+------+-------+-----------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
+-----------+-----------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    | 201215128 |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    |      NULL | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    |      NULL | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    |      NULL | NULL |  NULL |
+-----------+-----------+------+------+-------+-----------+------+-------+
Copy after login
Copy after login

Note: A left join B is equivalent to B right join A

mysql> select * from student left join sc using(Sno);
+-----------+-----------+------+------+-------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+-----------+------+------+-------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
+-----------+-----------+------+------+-------+------+-------+
9 rows in set (0.00 sec)mysql> select * from sc right join student using(Sno);
+-----------+-----------+------+------+-------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+-----------+------+------+-------+------+-------+
| 201215121 | 李勇      | 男   |   22 | CS    |    1 |    92 |
| 201215121 | 李勇      | 男   |   22 | CS    |    2 |    85 |
| 201215121 | 李勇      | 男   |   22 | CS    |    3 |    88 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    2 |    90 |
| 201215122 | 刘晨      | 女   |   19 | CS    |    3 |    80 |
| 201215128 | 陈冬      | 男   |   18 | IS    |    1 |    78 |
| 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
| 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
+-----------+-----------+------+------+-------+------+-------+
Copy after login
Copy after login
The above is the analysis of join usage in MySQL For more related content, please pay attention to the PHP Chinese website (www.php.cn)!



Related labels：
MySQL，join
source：php.cn
Previous article：mysql performance optimization max, count optimization Next article：Detailed introduction to mysql data types
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
How to display only the lowest value from a result set (MYSQL) I have the following statement: selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'...
From 2024-04-06 21:44:19
0
1
603
Calculate the sum of fields in another table using MySQL SQL query I have a schema like this: User table with attributes "user_id" and "userna...
From 2024-04-06 19:39:29
0
1
441
MySQL: "Select text from... as <variable here or subquery>" I have the following table with the following data: id text language 1 german text german ...
From 2024-04-06 19:25:54
0
2
529
How to group and count in MySQL? I'm trying to write a query that extracts the total number of undeleted messages sent to f...
From 2024-04-06 18:30:17
0
1
353
MySQL gets data from multiple tables I have a eg_design table which contains the following columns: and eg_domains table which ...
From 2024-04-06 18:42:44
0
2
479
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template