Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the difference between internal connection and external connection in mysql". In daily operation, I believe that many people have doubts about the difference between internal connection and external connection in mysql. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the question of "what is the difference between internal connection and external connection in mysql?" Next, please follow the editor to study!

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

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

Difference

Inner join: take out the matching data in the connection table, and do not retain the data that cannot be matched.

External join (outer join): take out the matching data in the connection table, and keep the data that do not match. The value is NULL.

Sample 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) internal connection (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 | +- -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 an alias for the table, or it can be omitted.

Mysql > select * from users u join topics t on u.id=t.user_id +-+-- id | name | id | title | user_id | +- -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 | +- -john | 1 | Hello world | 1 | 2 | May | 2 | PHP is the best language in the world | 2 | +- -+-+ 2 rows in set (0.00 sec) external connection (outer join)

Left outer join (left outer join): the table on the left is the main table

Right outer join (right outer join): take the right table as the main table

Take a table as the main table and make an associated query. Regardless of whether it can be associated or not, the data of the main table will be retained, and those that are not related will be displayed in NULL.

The popular explanation is: first take out all the data of the main table, and then go to the associated table to find out if there is any data that meets the association conditions, if so, display it normally, if not, display it 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 | | 5 | James | NULL | +-+ | -+ 5 rows in set (0.00 sec)

It is equivalent to the following, but 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 | 4 | Jack | | NULL | 5 | James | +-+-+ -+-+ 5 rows in set (0.00 sec)

The left outer join and the right outer join are relative, which table is the main table to associate.

At this point, the study on "what is the difference between internal and external connections in mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.