基础篇-表关联 Joining Tables

Simple Equijoins 简单等分联接

SELECT
    country.Name,
    countrylanguage.Language
FROM country, countrylanguage
WHERE
    country.Code = countrylanguage.CountryCode

Inner Joins 内连接

SELECT
    country.Name,
    countrylanguage.Language
FROM country
INNER JOIN countrylanguage ON
    country.Code = countrylanguage.CountryCode

Joining Multiple Tables 连接多个表

SELECT
    country.Name,
    countrylanguage.Language,
    city.Name
FROM country, countrylanguage, city
WHERE
    country.Code = countrylanguage.CountryCode AND
    country.Code = city.CountryCode AND
    countrylanguage.IsOfficial = 'T' AND
    city.Population > 1000000

Using Table Aliases 使用表别名

SELECT
    c.Name,
    cL.Language,
    city.Name
FROM country AS c, countrylanguage AS cL, city
WHERE
    c.Code = cL.CountryCode AND
    c.Code = city.CountryCode AND
    cL.IsOfficial = 'T' AND
    city.Population > 1000000

Multiple Use of The Same Table 同一表格的多次使用

SELECT
    c1.Name,
    c2.Population
FROM country AS c1, country AS c2
WHERE
    c1.Code = c2.Code

Select ALL Table Columns 选择所有表列

SELECT
    c.Name,
    cl.*
FROM country AS c, countrylanguage AS cl
WHERE
    c.Code = cl.CountryCode

Left Outer Joins 左外连接

SELECT
    c.Name,
    ct.Name AS NameCity,
    ct.Population
FROM country AS c
LEFT JOIN city AS ct ON
    ct.CountryCode = c.Code AND
    ct.Population > 1000000

Right Outer Joins 右外连接

SELECT
    c.Name,
    ct.Name AS NameCity,
    ct.Population
FROM country AS c
RIGHT JOIN city AS ct ON
    ct.CountryCode = c.Code AND
    ct.Population > 1000000

Full Outer Joins 全外连接

SELECT
    c.Name,
    ct.Name AS NameCity,
    ct.Population
FROM country AS c
FULL JOIN city AS ct ON
    ct.CountryCode = c.Code AND
    ct.Population > 1000000

Join with Aggergate Functions 用聚合函数联接

SELECT
    c.Name,
    COUNT(ct.Name) AS CityCount
FROM country AS c
INNER JOIN city AS ct ON
    ct.CountryCode = c.Code
GROUP BY c.Name
ORDER BY 2 DESC