Let's talk about MySQL joint query in detail

Union query

Union can merge tables queried by two or more select statements and eliminate duplicate rows in the tables.

The select statement must have the same number of columns with the same data type.

1. Query the ID and name of each province in China

select ProID,ProName from T_Province

2. ID and name of all prefecture-level cities in Hunan Province

select CityID,CityName from T_City
where ProID = (
    select ProID from T_Province where ProName="湖南省"
);

3. Combine them with union

select ProID,ProName from T_Province
union
select CityID,CityName from T_City
where ProID = (
    select ProID from T_Province where ProName="湖南省"
);

This results in the union of the two query results.

The column names in the combined set of a UNION are always equal to the column names in the first SELECT statement in the UNION.

Union query union all

select ProID,ProName from T_Province
union all
select CityID,CityName from T_City
where ProID = (
    select ProID from T_Province where ProName="湖南省"
);

When using union all, duplicate rows are not eliminated.

Inner join of joint query

1. Check how many prefecture-level cities there are in Hubei Province

No need for union query:

select count(CityID) from T_City
where ProID = (select ProID from T_Province where ProName="湖北省")

Join the two tables together using ProID

select ProName,CityName from（
    T_City join T_Province
    on T_City.ProID = T_Province.ProID
）
where ProName="Hubei Province"

2. Count the number of prefecture-level cities in each province and output the province name and the number of prefecture-level cities

select T_City.ProID,ProName,count(CityID) as cc from(
    T_City join T_Province
    on T_City.ProID = T_Province.ProID
)
group by T_City.ProID
order by cc desc;

The ProID to be output in the select statement should be one of T_City and T_Province, otherwise an error will be reported.

Two tables need to have a common "language" (the column names do not necessarily have to be the same) in order to be joined.

You can give tables aliases, set the alias of T_City table to tc and the alias of T_Province to tp.

select tc.ProID,ProName,count(CityID) as cc from(
    T_City tc join T_Province tp
    on T_City.ProID = T_Province.ProID
)
group by tc.ProID
order by cc desc;

3. Query cities with more than 20 districts and counties, and output the city name and the number of districts and counties

select CityName,count(DisName) disCount from (
    T_City tc join T_District td
    on tc.CityID = td.CityID
)
group by CityName
having disCount > 20;

Three-table union query

1. Which cities in which provinces are the top three cities with the most districts and counties? The query results include the province name, city name, and the number of districts and counties.

select tp.ProName,tcd.CityName,tcd.ci from
(
select ProID,CityName,count(ID) ci from(T_City tc join T_District td on tc.CityID = td.CityID) 
 
   group by tc.CityID
order by ci desc
limit 3
)tcd
join T_Province tp on tcd.ProID = tp.ProID;

Joint query left join & right join

The inner join is based on the common parts of the left and right tables.

The left join is based on the common parts of the left and right tables plus the unique parts of the left table.

A right join is based on the common parts of the left and right tables plus the unique parts of the right table.

Query all provinces and their cities information

select * from(
T_Province tp join T_City tc
on tp.ProID = tc.ProID
);

Query all provinces and their cities and provinces without cities

select * from(
T_Province tp left join T_City tc
on tp.ProID = tc.ProID
);

Query all provinces and their city information and city information without provinces

select * from(
T_Province tp right join T_City tc
on tp.ProID = tc.ProID
);

Summarize

This is the end of this article about MySQL joint query. For more relevant MySQL joint query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!