MySQL internal connection left connection right connection outer connection

mysql> desc Student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | Int (11) | NO | PRI | 0 | |
| NAME | varchar (16) | YES | | NULL | |
| Age | Int (11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 Rows in Set

Mysql> Desc SC;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| ID | Int (11) | NO | PRI | NULL | auto_increment |
| SID | Int (11) | YES | | NULL | |
| CID | Int (11) | YES | MUL | NULL | |
| Score | Int (11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
4 rows in Set

Mysql> select * from student;
+-------+----------+-----+
| ID | NAME | Age |
+-------+----------+-----+
| 10001 | Andy | 26 |
| 10002 | Bill | 27 |
| 10003 | Caroline | 34 |
| 10004 | David | 46 |
+-------+----------+-----+
4 rows in set

Mysql> SELECT * from SC;
+----+-------+-----+-------+
| ID | SID | CID | Score |
+----+-------+-----+-------+
| 1 | 10001 | 101 | | |
| 2 | 10001 | 102 | | |
| 3 | 10008 | 103 | | |
+----+-------+-----+-------+
3 rows in set
---------------------------------------------------------------- -------------------------------------------------
INNER JOIN
mysql> Select student.*, sc.* from student inner join sc on student.id = Sc.sid;
+-------+------+-----+----+-------+-----+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+------+-----+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
+-------+------+-----+----+-------+-----+-------+
2 rows in set

The

Inner join is equivalent to our usual natural connection, that is:
mysql> select student.*, sc.* from student,sc where student.id = Sc.sid;
+-------+------+-----+----+-------+-----+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+------+-----+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
+-------+------+-----+----+-------+-----+-------+
2 rows in set

------------------------------------------------------------------------------------------------------------ -----
Left Connection
Consider the following requirements, I would like to list all students corresponding results, a student may have multiple scores, or may not score. There are multiple scores to list the scores, no results, the results these fields are filled with null values. How to solve this problem? The
uses the left connection, the student table, and the right join SC table, as follows:
mysql> Select student.*, sc.* from student Ieft Join SC on student.id = Sc.sid;
+-------+----------+-----+------+-------+------+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+----------+-----+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
+-------+----------+-----+------+-------+------+-------+
5 rows in set

--------------------------------------------------------------------------------------------------------------- --
Right connection
Consider the following requirements, I would like to list all the students, a score of the corresponding students, may not have the corresponding students, such as the student expelled. Students are listed as students, no students, students the values of these fields are filled with null. How to solve this problem?
Using the right connection, the student table is a join SC table, as follows:
Mysql> Select Student.*, sc.* from student right Join SC on student.id = Sc.sid;
+-------+------+------+----+-------+-----+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+------+------+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
| NULL | NULL | NULL | 3 | 10008 | 103 | 100 |
+-------+------+------+----+-------+-----+-------+
3 Rows in Set

Based on symmetry, a LEFT join B is equivalent to B right join A
--------------------------------------------------------------------------------------------------------------- --
External connection
Left join is also called the Left outer connection (the same as the right connection), where the outer connection is also called the full outside connection.
Consider the following requirements, I would like to list all students corresponding to all the results, where there are students may not have scores, scores may not be corresponding to the students, no, also to list, the values of these fields are filled with null.
Currently, MySQL does not support outer joins, and the solution is to combine the results of the left and right joins using the Union combination query. As follows:
Mysql> Select Student.*, sc.* from student left join SC on student.id = sc.sid Union Select student.*, sc.* from Studen T right join SC on student.id = Sc.sid;
+-------+----------+------+------+-------+------+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+----------+------+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | 3 | 10008 | 103 | 100 |
+-------+----------+------+------+-------+------+-------+
6 Rows in Set

Note: The union here automatically removes duplicate rows, and if you do not want to remove duplicate rows, use the UNION ALL

------------------------------------------------------------------------------------------------------------ -----
There is one more thing to note: The condition after the on, as follows:
mysql> Select student.*, sc.* from student left join SC on student.id = sc.sid;
+-------+----------+-----+------+-------+------+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+----------+-----+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
+-------+----------+-----+------+-------+------+-------+
5 rows in set

Mysql> Select Student.*, sc.* from student left join SC on student.id = Sc.sid and sc.cid=101;
+-------+----------+-----+------+-------+------+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+----------+-----+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
+-------+----------+-----+------+-------+------+-------+
4 rows in Set

Mysql> Select Student.*, sc.* from student left join SC on student.id = sc.sid where sc.cid=101;
+-------+------+-----+----+-------+-----+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+------+-----+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
+-------+------+-----+----+-------+-----+-------+
1 row in Set

Here we see the difference between the second query and the third one, which is clear in the other way.
Mysql> Select Student.*, sc.* from student left join SC on (student.id = Sc.sid and sc.cid=101);
+-------+----------+-----+------+-------+------+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+----------+-----+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
+-------+----------+-----+------+-------+------+-------+
4 rows in Set

Mysql> Select Student.*, sc.* from student left join SC on (student.id = sc.sid) where sc.cid=101;
+-------+------+-----+----+-------+-----+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+------+-----+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
+-------+------+-----+----+-------+-----+-------+
1 row in Set

Mysql> Select Student.*, sc.* from student left join SC on (student.id = SC.SID where sc.cid=101);
1064-you has an error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' where sc.cid=101 ' in Lin E 1

--------------------------------------------------------------------------------------------------------------- --
Select Student.*, sc.* from student left join SC on (student.id = Sc.sid and sc.cid=101); Equivalent:
1. Internal connection
Mysql> Select Student.*, sc.* from student inner JOIN SC on student.id = Sc.sid;
+-------+------+-----+----+-------+-----+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+------+-----+----+-------+-----+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
+-------+------+-----+----+-------+-----+-------+
2 rows in Set
2, select the Sc.cid=101, and then the left connection, no results using null padding

--------------------------------------------------------------------------------------------------------------- --
Select Student.*, sc.* from student left join SC on (student.id = sc.sid) where sc.cid=101; Equivalent:
1, left JOIN connect
Mysql> Select Student.*, sc.* from student left join SC on student.id = Sc.sid;
+-------+----------+-----+------+-------+------+-------+
| ID | NAME | Age | ID | SID | CID | Score |
+-------+----------+-----+------+-------+------+-------+
| 10001 | Andy | 26 | 1 | 10001 | 101 | 78 |
| 10001 | Andy | 26 | 2 | 10001 | 102 | 67 |
| 10002 | Bill | 27 | NULL | NULL | NULL | NULL |
| 10003 | Caroline | 34 | NULL | NULL | NULL | NULL |
| 10004 | David | 46 | NULL | NULL | NULL | NULL |
+-------+----------+-----+------+-------+------+-------+
5 rows in Set
2. Re-election of sc.cid=101

MySQL internal connection left connection right connection outer connection