Summary of various common join table query examples in MySQL

This article uses examples to describe various common join table queries in MySQL. Share with you for your reference, the details are as follows:

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

1. Connections can be divided into three categories:

(1) Inner join: join, inner join

(2) Outer join: left join, left outer join, right join, right outer join, union, union all

(3) Cross join

2. Prepare the table for demonstration:

CREATE TABLE `a` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `a_name` 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',
 `a_id` int(11) DEFAULT '0' COMMENT 'a table ID',
 `b_name` varchar(32) DEFAULT '' COMMENT 'b table name',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The data in table a and table b are shown in the figure:

3. Inner join or join

select * from a inner join b on a.id = b.a_id;
select * from a join b on a.id = b.a_id;
select * from a, b where a.id = b.a_id;

The results are as follows:

Inner join can be understood as a combination of data records in two tables that simultaneously meet certain conditions. That is, all records in table A and table B that satisfy the condition a.id = b.a_id.

When a record in table A corresponds to multiple records in table B, multiple records in table B will appear in the result set in a repeated manner.

When a record in table B corresponds to multiple records in table A, multiple records in table A will appear in the result set in a repeated manner.

4. Outer join left join or right join

(1) Left outer join

select * from a left join b on a.id = b.a_id;
select * from a left outer join b on a.id = b.a_id;

A left outer join uses table A on the left as the primary table and returns all rows, even if there are no matching rows in table B on the right.

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

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

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

If we only want to find out the data in Table A that meet the conditions or do not meet the conditions, how should we check?

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 data in Table A that meets the conditions.

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 the data in Table A that do not meet the conditions.

(2) Right outer join

select * from a right join b on a.id = b.a_id;
select * from a right outer join b on a.id = b.a_id;

The right outer join is actually the same as the left outer join. The difference lies in the determination of the primary table. The two can be converted to each other.

The description of right outer join is basically the same as left outer join, so I will not describe it in detail here.

(3) Full join

MySQL does not support full join, but there is a corresponding alternative, which is left join union right join instead.

select * from a left join b on a.id = b.a_id
union
select * from a right join b on a.id = b.a_id;

A full join returns all rows from both Table A and Table B. If a row in Table A does not have a match in Table B, or a row in Table B does not have a match in Table A, these rows will be displayed, and non-existent fields will be supplemented with null.

Union will merge the duplicate rows.

In this case, both the records that meet the conditions and the records that do not meet the conditions in Table A and Table B are displayed.

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

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

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

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

5. Cross-connect

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

select * from a cross join b;
select * from a, b;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.