Joint query of Mysql

union of joint query

union can merge tables queried by more than two select statements and eliminate duplicate rows in the table.

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

1. Query the ID and province 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="Hunan Province"
);

3. Merge 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="Hunan Province"
);

In this way, the union of the two query results is obtained.

The column name in the UNION merged collection is always equal to the column name in the first SELECT statement in the UNION.

union all of union query

select ProID,ProName from T_Province
union all
select CityID,CityName from T_City
where ProID = (
	select ProID from T_Province where ProName="Hunan Province"
);

When using union all, duplicate lines are not eliminated.

inner join of joint query

1. How many prefecture level cities are there in Hubei Province

No joint query:

select count(CityID) from T_City
where ProID = (select ProID from T_Province where ProName="Hubei province")

Join two tables together through 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 provinces and prefecture level cities, and output the names of provinces 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;

What should the ProID to be output in the select statement be T_City and t_ One of the provinces, or an error will be reported.

Two tables need to have a common (column names are not necessarily the same) "language" to join.

You can give the table an alias and change t_ Set the alias of the city table to tc and set t_ The alias of province is set 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 city name and 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 CityNamehaving disCount > 20;

Table 3 of joint query

1. Which province or city has the largest number of districts and counties? The query results include province name, city name and 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.CityIDorder by ci desclimit 3)tcdjoin T_Province tp on tcd.ProID = tp.ProID;

Left join & right join of joint query

Inner connection is based on the common part of the left and right tables

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

The right connection is based on the common part of the left and right tables plus the unique part of the right table

Query all provinces and their cities

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

Query the information of all provinces and their cities and the information of provinces without cities

select * from(T_Province tp left join T_City tcon 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 tcon tp.ProID = tc.ProID);