What is the difference between mysql inner join and outer join?

The difference between mysql inner connection and outer connection: inner connection will take out the matching data in the connection table, and the data that cannot be matched will not be retained; while the outer connection will take out the matching data in the connection table, and the data that cannot be matched will not be retained. will also be retained, its value is NULL.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Difference

• Inner join (inner join): take out the matching data in the connection table, and do not retain the unmatched data
• Outer join (outer join) : Take out the matching data in the connection table, and the data that does not match will be retained. Its value is NULL

Example table

users table

mysql> select * from users;
+----+-------+
| id | name  |
+----+-------+
|  1 | john  |
|  2 | May   |
|  3 | Lucy  |
|  4 | Jack  |
|  5 | James |
+----+-------+
5 rows in set (0.00 sec)

topics table

mysql> select * from topics;
+----+---------------------------------------+---------+
| id | title                                 | user_id |
+----+---------------------------------------+---------+
|  1 |  Hello world                          |       1 |
|  2 | PHP is the best language in the world |       2 |
|  3 | Laravel artist                        |       6 |
+----+---------------------------------------+---------+
3 rows in set (0.00 sec)

Inner join

• Example

mysql> select * from users as u inner join topics as t on u.id=t.user_id;
+----+------+----+---------------------------------------+---------+
| id | name | id | title                                 | user_id |
+----+------+----+---------------------------------------+---------+
|  1 | john |  1 |  Hello world                          |       1 |
|  2 | May  |  2 | PHP is the best language in the world |       2 |
+----+------+----+---------------------------------------+---------+
2 rows in set (0.00 sec)

inner can be omitted, as is to give the table an alias, You can also omit

mysql> select * from users u join topics t on u.id=t.user_id;
+----+------+----+---------------------------------------+---------+
| id | name | id | title                                 | user_id |
+----+------+----+---------------------------------------+---------+
|  1 | john |  1 |  Hello world                          |       1 |
|  2 | May  |  2 | PHP is the best language in the world |       2 |
+----+------+----+---------------------------------------+---------+
2 rows in set (0.00 sec)

The above two sentences are equivalent to

mysql> select * from users,topics where users.id=topics.user_id;
+----+------+----+---------------------------------------+---------+
| id | name | id | title                                 | user_id |
+----+------+----+---------------------------------------+---------+
|  1 | john |  1 |  Hello world                          |       1 |
|  2 | May  |  2 | PHP is the best language in the world |       2 |
+----+------+----+---------------------------------------+---------+
2 rows in set (0.00 sec)

outer join

• left outer join: take the left The table is the main table
• Right outer join: Use the table on the right as the main table

Use a certain table as the main table to perform related queries, whether it is possible or not If it is related, the data of the main table will be retained. If it is not related, it will be displayed as NULL

The popular explanation is: first take out all the data of the main table, and then go to the related table to find out whether there is any matching relationship. If there is any conditional data, it will be displayed normally. If not, it will be displayed as NULL

Example

mysql> select * from users as u left join topics as t on u.id=t.user_id;
+----+-------+------+---------------------------------------+---------+
| id | name  | id   | title                                 | user_id |
+----+-------+------+---------------------------------------+---------+
|  1 | john  |    1 |  Hello world                          |       1 |
|  2 | May   |    2 | PHP is the best language in the world |       2 |
|  3 | Lucy  | NULL | NULL                                  |    NULL |
|  4 | Jack  | NULL | NULL                                  |    NULL |
|  5 | James | NULL | NULL                                  |    NULL |
+----+-------+------+---------------------------------------+---------+
5 rows in set (0.00 sec)

is equivalent to the following, except that the position of the field is different

mysql> select * from topics as t right join users as u on u.id=t.user_id;
+------+---------------------------------------+---------+----+-------+
| id   | title                                 | user_id | id | name  |
+------+---------------------------------------+---------+----+-------+
|    1 |  Hello world                          |       1 |  1 | john  |
|    2 | PHP is the best language in the world |       2 |  2 | May   |
| NULL | NULL                                  |    NULL |  3 | Lucy  |
| NULL | NULL                                  |    NULL |  4 | Jack  |
| NULL | NULL                                  |    NULL |  5 | James |
+------+---------------------------------------+---------+----+-------+
5 rows in set (0.00 sec)

Left outer Connections and right outer connections are relative. The main thing is which table is used as the main table for association.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the difference between mysql inner join and outer join?. For more information, please follow other related articles on the PHP Chinese website!