Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.
Union of federated queries
A union can merge tables from more than two SELECT statements and eliminate duplicate rows in the table.
Where the SELECT statement needs to have the same number of columns and the same data type.
1. Query the ID and name of each province in China
select ProID,ProName from T_Province
Copy the code
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 ");Copy the code
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=" hunan ");Copy the code
This results in the union of the two query results.
The column name in the UNION merged collection is always equal to the column name in the first SELECT statement in the UNION.
The union all of the 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 ");Copy the code
When using union all, duplicate lines are not eliminated.
Inner join for federated query
1. Query the number of prefecture-level cities in Hubei Province
No federated query:
Select count(CityID) from T_City where ProID = (select ProID from T_Province where ProName=" hubei ")Copy the code
Join the two tables together by ProID
Select ProName,CityName from (T_City join T_Province on T_City.ProID = T_Province.ProID) where ProName=" hubei"Copy the code
2. Count the number of prefecture-level cities in each province and output the name of the province 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;
Copy the code
The ProID of the select statement should be either T_City or T_Province, otherwise an error will be reported.
Two tables need a common (not necessarily the same) “language” to join.
You can give the table a different name by aliasing T_City to TC and 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;
Copy the code
3. Query the cities with more than 20 districts and counties, and output the 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 CityName having disCount > 20;Copy the code
Union query three table union
1. The query result includes the name of the province, the name of the city, 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;
Copy the code
Left Join&right Join for joinquery
The inner join is based on the part common to the left and right tables
The left join is based on the parts common to the left and right tables plus the parts unique to the left table
The right join is based on the parts common to the left and right tables plus the parts unique to the right table
Query information about all provinces and their cities
select * from(
T_Province tp join T_City tc
on tp.ProID = tc.ProID
);
Copy the code
Query information about all provinces and their cities and provinces without cities
select * from(
T_Province tp left join T_City tc
on tp.ProID = tc.ProID
);
Copy the code
Query information about all provinces and their cities and cities without provinces
select * from(
T_Province tp right join T_City tc
on tp.ProID = tc.ProID
);
Copy the code