Shulou(Shulou.com)06/01 Report--

This paper gives an example of all kinds of common join connected table queries in mysql. Share with you for your reference, the details are as follows:

Usually we need to join multiple tables to query data to get the desired results.

1. Connections can be divided into three categories:

(1) Internal connection: join,inner join

(2) external connection: left join,left outer join,right join,right outer join,union,union all

(3) Cross connection: cross join

Prepare the tables that need to be demonstrated:

CREATE TABLE `a` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `aname` varchar (32) DEFAULT' 'COMMENT'a table name', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;CREATE TABLE `b` (`id`int (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `aid` int (11) DEFAULT' 0' COMMENT'a table ID', `bname` varchar (32) DEFAULT 'COMMENT'b table name', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The data of table an and table b are shown in the figure:

Third, internal connection inner join or join

Select * from an inner join b on a.id = b. A select * from a join b on a.id = b. A select * from a, b where a.id = b.a_id

The results are as follows:

An inner join can be understood as a combination of data records in two tables that meet certain conditions at the same time. That is, all records in tables An and B that meet the condition a.id = b.a_id.

When one record in Table A corresponds to multiple records in Table B, multiple Table B records appear in the result set in a repetitive manner.

When one record in Table B corresponds to multiple records in Table A, multiple Table A records appear in the result set in a repetitive manner.

Fourth, external connection left join or right join

(1) left outer connection

Select * from a left join b on a.id = b.an accountability.select * from a left outer join b on a.id = b.a_id

The left outer join returns all rows with table An on the left as the main table, even if there are no matching rows in table B on the right.

If table An on the left cannot find a record in table B on the right, all records in table An are returned and the corresponding fields in table B are set to null.

If table An on the left finds multiple records in table B on the right, multiple records are displayed in the result set with the same table A records and different table B records.

In this case, all the records in Table An are actually queried, including those that do not meet the conditions.

If we only want to find out what meets or does not meet the conditions in Table A, how should we find out?

Select * from a left join b on a.id = b.a_id where b.a_id is not null;select * from a left outer join b on a.id = b.a_id where b.a_id is not null

The above statement queries the conditions that are met in Table A.

Select * from a left join b on a.id = b.a_id where b.a_id is null;select * from a left outer join b on a.id = b.a_id where b.a_id is null

The above statement queries those in Table A that do not meet the conditions.

(2) right external connection

Select * from a right join b on a.id = b.an accountability.select * from a right outer join b on a.id = b.a_id

In fact, the right outer connection is the same as the left outer join, the difference lies in the determination of the main table, the two can be converted to each other.

The description of the right outer connection is basically the same as the left outer connection, so I won't describe it too much here.

(3) fully connected full join

Mysql does not support full connectivity, but there is an alternative, that is, left join union right join.

Select * from a left join b on a.id = b.a_idunionselect * from a right join b on a.id = b.a_id

Full join returns all rows from Table An and Table B. if the rows in Table A do not match in Table B, or if the rows in Table B do not match in Table A, these rows are displayed, and the fields that do not exist are supplemented by null.

Union merges the duplicate lines.

In this case, the records in tables An and B that meet and do not meet the conditions are displayed.

If you only want to display all records that do not meet the criteria, use the following statement:

Select * from a left join b on a.id = b.a_id where b.a_id is nullunionselect * from a right join b on a.id = b.a_id where a.id is null

If you want to display only all records that meet the criteria, use the following statement:

Select * from a left join b on a.id = b.a_id where b.a_id is not nullunionselect * from a right join b on a.id = b.a_id where a.id is not null

V. Cross connection

A cross join is actually the Cartesian product of table An and table B.

Select * from a cross join BTX select * from a, b

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.