SQL – JOIN the parsing

  • SQL JOIN SQL JOIN SQL JOIN
  • B. The differences between four types of JOIN
  • 3. How to use various joins
    • (I) Prepare test data
    • (2) left connection
    • (3) right connection
    • (4) internal connection
    • (5) external connection
  • Four,

SQL JOIN SQL JOIN SQL JOIN

SQL JOIN is used to JOIN data rows from multiple tables according to certain rules to form a large data table.

For example, the overused graph below will help you quickly understand the effect of each join usage:

This diagram describes seven usages related to left join, right Join, inner join, and outer join.

I changed a version:

It’s easier to understand

You can follow my public account, reply to "mysql", you can get a large hd picture

B. The differences between four types of JOIN

  • 1.INNER JOIN: Returns rows if there is at least one match in the table;
  • 2,LEFT JOIN: Returns all rows from the left table even if there is no match in the right table;
  • 3,RIGHT JOIN: Returns all rows from the right table even if there is no match in the left table;
  • 4,FULL JOIN: Returns rows as long as a match exists in one of the tables

    。

3. How to use various joins

(I) Prepare test data

The data for the test was simple, and it was implemented using the same old data sheets, student tables, and grade tables that we use in class and in books.

1. Student Table:

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `sno` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'student id',
  `sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Student name'.PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1.'202001'.'Joe');
INSERT INTO `student` VALUES (2.'202002'.'bill');
INSERT INTO `student` VALUES (3.'202003'.'Cathy');
INSERT INTO `student` VALUES (4.'202004'.'Daisy');
INSERT INTO `student` VALUES (5.'202005'.'Ming');
INSERT INTO `student` VALUES (6.'202006'.'little red');
INSERT INTO `student` VALUES (7.'202007'.'xiao gang');
INSERT INTO `student` VALUES (8.'202008'.'xiao li');

SET FOREIGN_KEY_CHECKS = 1;
Copy the code

2. Transcript:

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `sno` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'student id',
  `courseName` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Course Name',
  `grade` double(3.0) NULL DEFAULT NULL COMMENT 'results'.PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES (1.'202001'.'High count one'.90);
INSERT INTO `grade` VALUES (2.'202003'.'High number two'.88);
INSERT INTO `grade` VALUES (3.'202003'.'English one'.77);
INSERT INTO `grade` VALUES (4.'202004'.'English 2'.79);
INSERT INTO `grade` VALUES (5.'202002'.'C++ design '.87);
INSERT INTO `grade` VALUES (6.'202005'.'Java Object-oriented Basics'.98);
INSERT INTO `grade` VALUES (7.'202006'.'Algorithm Analysis and Implementation'.76);
INSERT INTO `grade` VALUES (8.'202007'.'Software Engineering A'.65);
INSERT INTO `grade` VALUES (9.'202007'.'Computer Applications and Fundamentals'.59);

SET FOREIGN_KEY_CHECKS = 1;
Copy the code

Here are the current numbers:

mysql> select * from grade;
+----+--------+------------------+-------+
| id | sno    | courseName       | grade |
+----+--------+------------------+-------+
|  1 | 202001 |A high number of|    90 |
|  2 | 202003 |The high number of two|    88 |
|  3 | 202003 |English is a|    77 |
|  4 | 202004 |English 2|    79 |
|  5 | 202002 | C++Language design|    87 |
|  6 | 202005 |Java Object-oriented basics|    98 |
|  7 | 202006 |Algorithm analysis and implementation|    76 |
|  8 | 202007 |Software Engineering A|    65 |
|  9 | 202007 |Computer applications and fundamentals|    59 |
+----+--------+------------------+-------+
9 rows in set (0.12 sec)

mysql> 
mysql> select * from student;
+----+--------+-------+
| id | sno    | sname |
+----+--------+-------+
|  1 | 202001 |Zhang SAN|
|  2 | 202002 |Li si|
|  3 | 202003 |Cathy|
|  4 | 202004 |Zhao six|
|  5 | 202005 |Xiao Ming|
|  6 | 202006 |The little red|
|  7 | 202007 |pika~chu|
|  8 | 202008 |Xiao li|
+----+--------+-------+
8 rows in set (0.12 sec)

mysql> 
Copy the code

The data structure is as follows:

mysql> 
mysql> desc grade;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sno        | varchar(20) | YES  |     | NULL    |                |
| courseName | varchar(20) | YES  |     | NULL    |                |
| grade      | double(3.0) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.09 sec)

mysql> 
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| sno   | varchar(20) | YES  |     | NULL    |                |
| sname | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.10 sec)

mysql> 
Copy the code

(2) left connection

Among the 7 usages of join, there are two usages of left join, as shown in the figure below:

The first:

mysql> select * from student t1 
    -> left join grade t2 
    -> on t1.sno=t2.sno;
+----+--------+-------+------+--------+------------------+-------+
| id | sno    | sname | id   | sno    | courseName       | grade |
+----+--------+-------+------+--------+------------------+-------+
|  1 | 202001 |Zhang SAN|    1 | 202001 |A high number of|    90 |
|  3 | 202003 |Cathy|    2 | 202003 |The high number of two|    88 |
|  3 | 202003 |Cathy|    3 | 202003 |English is a|    77 |
|  4 | 202004 |Zhao six|    4 | 202004 |English 2|    79 |
|  2 | 202002 |Li si|    5 | 202002 | C++Language design|    87 |
|  5 | 202005 |Xiao Ming|    6 | 202005 |Java Object-oriented basics|    98 |
|  6 | 202006 |The little red|    7 | 202006 |Algorithm analysis and implementation|    76 |
|  7 | 202007 |pika~chu|    8 | 202007 |Software Engineering A|    65 |
|  7 | 202007 |pika~chu|    9 | 202007 |Computer applications and fundamentals|    59 |
|  8 | 202008 |Xiao li| NULL | NULL   | NULL             | NULL  |
+----+--------+-------+------+--------+------------------+-------+
10 rows in set (0.10 sec)

mysql> 
Copy the code

It can be seen from the above results that there is Xiao Li among our students, but there is no result of Xiao Li in the transcript. So there will be null. LEFT JOIN: returns all rows from the LEFT table even if there is no match in the right table; All rows are returned from the left table even if there is no matching data in the score table.

In most cases, we don’t want to display null data. What if? Very simple, you can switch the position of T1 and T2, as follows:

mysql> 
mysql> select * from grade t1 
    -> left join student t2 
    -> on t1.sno=t2.sno;
+----+--------+------------------+-------+----+--------+-------+
| id | sno    | courseName       | grade | id | sno    | sname |
+----+--------+------------------+-------+----+--------+-------+
|  1 | 202001 |A high number of|    90 |  1 | 202001 |Zhang SAN|
|  5 | 202002 | C++Language design|    87 |  2 | 202002 |Li si|
|  2 | 202003 |The high number of two|    88 |  3 | 202003 |Cathy|
|  3 | 202003 |English is a|    77 |  3 | 202003 |Cathy|
|  4 | 202004 |English 2|    79 |  4 | 202004 |Zhao six|
|  6 | 202005 |Java Object-oriented basics|    98 |  5 | 202005 |Xiao Ming|
|  7 | 202006 |Algorithm analysis and implementation|    76 |  6 | 202006 |The little red|
|  8 | 202007 |Software Engineering A|    65 |  7 | 202007 |pika~chu|
|  9 | 202007 |Computer applications and fundamentals|    59 |  7 | 202007 |pika~chu|
+----+--------+------------------+-------+----+--------+-------+
9 rows in set (0.16 sec)

mysql> 
Copy the code

This is an important point: as mentioned in the official Mysql5.7 manual, this optimization method:

At the parser stage, queries with right outer join operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed such that this right join:

In the parser phase, queries with a right outer join are converted to equal queries with only left join operations. In general, a left join is converted to a right join

(T1, ...) RIGHT JOIN (T2, ...) ONP(T1, ... , T2, ...)Copy the code

Becomes this equivalent left join:

(T2, ...) LEFT JOIN (T1, ...) ONP(T1, ... , T2, ...)Copy the code

And you can see that when you switch, you’re going to change t1 to T2, and you’re going to change t2 to T1.

The second:

mysql> 
mysql> select * from student t1 
    -> left join grade t2 
    -> on t1.sno=t2.sno
    -> where t2.sno is null;
+----+--------+-------+------+------+------------+-------+
| id | sno    | sname | id   | sno  | courseName | grade |
+----+--------+-------+------+------+------------+-------+
|  8 | 202008 |Xiao li| NULL | NULL | NULL       | NULL  |
+----+--------+-------+------+------+------------+-------+
1 row in set (19.59 sec)

mysql> 
Copy the code

From the result, it is clear that only data with SNO null is detected. What if not null?

mysql> 
mysql> select * from student t1 
    left join grade t2 
    on t1.sno=t2.sno
    where t2.sno is NOT null;
+----+--------+-------+----+--------+------------------+-------+
| id | sno    | sname | id | sno    | courseName       | grade |
+----+--------+-------+----+--------+------------------+-------+
|  1 | 202001 |Zhang SAN|  1 | 202001 |A high number of|    90 |
|  3 | 202003 |Cathy|  2 | 202003 |The high number of two|    88 |
|  3 | 202003 |Cathy|  3 | 202003 |English is a|    77 |
|  4 | 202004 |Zhao six|  4 | 202004 |English 2|    79 |
|  2 | 202002 |Li si|  5 | 202002 | C++Language design|    87 |
|  5 | 202005 |Xiao Ming|  6 | 202005 |Java Object-oriented basics|    98 |
|  6 | 202006 |The little red|  7 | 202006 |Algorithm analysis and implementation|    76 |
|  7 | 202007 |pika~chu|  8 | 202007 |Software Engineering A|    65 |
|  7 | 202007 |pika~chu|  9 | 202007 |Computer applications and fundamentals|    59 |
+----+--------+-------+----+--------+------------------+-------+
9 rows in set (19.46 sec)

mysql> 
Copy the code

It’s amazing that it’s the same result that we wanted in the last one. So this SQL is discarding null data.

(3) right connection

This is the opposite of left join, so think about it in your head.

Without further ado, let’s see what happens.

mysql> 
mysql> select * from student t1 
    right join grade t2 
    on t1.sno=t2.sno;
+----+--------+-------+----+--------+------------------+-------+
| id | sno    | sname | id | sno    | courseName       | grade |
+----+--------+-------+----+--------+------------------+-------+
|  1 | 202001 |Zhang SAN|  1 | 202001 |A high number of|    90 |
|  2 | 202002 |Li si|  5 | 202002 | C++Language design|    87 |
|  3 | 202003 |Cathy|  2 | 202003 |The high number of two|    88 |
|  3 | 202003 |Cathy|  3 | 202003 |English is a|    77 |
|  4 | 202004 |Zhao six|  4 | 202004 |English 2|    79 |
|  5 | 202005 |Xiao Ming|  6 | 202005 |Java Object-oriented basics|    98 |
|  6 | 202006 |The little red|  7 | 202006 |Algorithm analysis and implementation|    76 |
|  7 | 202007 |pika~chu|  8 | 202007 |Software Engineering A|    65 |
|  7 | 202007 |pika~chu|  9 | 202007 |Computer applications and fundamentals|    59 |
+----+--------+-------+----+--------+------------------+-------+
9 rows in set (19.49 sec)

mysql> 
Copy the code

It can be seen from the above effect that only matched the data in the score table, xiao Li did not display. RIGHT JOIN: returns all rows from the RIGHT table even if there is no match in the left table;

(4) internal connection

INNER JOIN: returns rows if there is at least one match in the table;

mysql> select * from student t1 
    inner join grade t2 
    on t1.sno=t2.sno;
+----+--------+-------+----+--------+------------------+-------+
| id | sno    | sname | id | sno    | courseName       | grade |
+----+--------+-------+----+--------+------------------+-------+
|  1 | 202001 |Zhang SAN|  1 | 202001 |A high number of|    90 |
|  3 | 202003 |Cathy|  2 | 202003 |The high number of two|    88 |
|  3 | 202003 |Cathy|  3 | 202003 |English is a|    77 |
|  4 | 202004 |Zhao six|  4 | 202004 |English 2|    79 |
|  2 | 202002 |Li si|  5 | 202002 | C++Language design|    87 |
|  5 | 202005 |Xiao Ming|  6 | 202005 |Java Object-oriented basics|    98 |
|  6 | 202006 |The little red|  7 | 202006 |Algorithm analysis and implementation|    76 |
|  7 | 202007 |pika~chu|  8 | 202007 |Software Engineering A|    65 |
|  7 | 202007 |pika~chu|  9 | 202007 |Computer applications and fundamentals|    59 |
+----+--------+-------+----+--------+------------------+-------+
9 rows in set (0.12 sec)

mysql> 
Copy the code

(5) external connection

This is not supported in Mysql and can be tested on SQL Server. I’m not going to test it here.

Four,

  • 1.INNER JOIN: Returns rows if there is at least one match in the table;
  • 2,LEFT JOIN: Returns all rows from the left table even if there is no match in the right table;
  • 3,RIGHT JOIN: Returns all rows from the right table even if there is no match in the left table;
  • 4,FULL JOIN: Returns rows as long as a match exists in one of the tables

    。

Welcome to study together, communicate together and make progress together.

Follow my wechat public number to push you wonderful content for the first time oh:

Reply menu, more good gift, surprise is waiting for you.