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