Bad Luck on Subquery

面试碰到这样一道SQL题:

数据库结构如图

要求取出

1. Country Name

2. 在这个Ｃｏｕｎｔｒｙ的city count

3. 在这个Ｃｏｕｎｔｒｙ的hotel count

 

当时一想后面题还多着呢，　祭出Ｓｕｂｑｕｅｒｙ　三下五除二，　搞定，　

 

SELECT 
    Country.Name,
    (SELECT 
　　　　　　COUNT(City.CityID) 
　　　　FROM 
　　　　　　tblCity City 
　　　　WHERE 
　　　　　　City.CountryID = Country.CountryID
　　　　) AS Cities,
    (SELECT 
　　　　　　COUNT(Hotel.HotelID) 
　　　　FROM 
　　　　　　tblCity City 
　　　　LEFT JOIN tblHotel Hotel 
　　　　　　　　ON City.CityID = Hotel.CityID 
　　　　WHERE 
　　　　　　City.CountryID = Country.CountryID
　　　　) AS Hotels
FROM 
　　tblCountry Country
ORDER BY 
　　Country.Name

 

离开面试公司后，　一直觉得不妥，咋就觉得那是个设好的套让我钻呢．

 

琢磨着用ＪＯＩＮ也能解决这个问题如下：

SELECT 
    Country.Name,
    ISNULL(Cities, 0) AS Cities,
    ISNULL(Hotels, 0) AS Hotels
FROM
    tblCountry Country LEFT JOIN
        (SELECT 
            City.CountryID, 
            COUNT(Hotel.HotelID) AS Hotels 
        FROM 
            tblCity City LEFT JOIN tblHotel Hotel 
                ON City.CityID = Hotel.CityID 
            GROUP BY 
            City.CountryID
        ) AS CountryHotels
        ON Country.CountryID = CountryHotels.CountryID
    LEFT JOIN
        (SELECT 
            City.CountryID, 
            COUNT(City.CityID) AS Cities 
        FROM 
            tblCity City 
        GROUP BY 
            City.CountryID
        ) AS CountryCities
        ON Country.CountryID = CountryCities.CountryID

 

 

放在一起比较下Execution Plan, 竟发现Ｓｕｂｑｕｅｒｙ解法的执行效率竟只有ＪＯＩＮ解法的一半．这时才惊醒，原来是上来鬼子的当了．

 

 

原来，　如果子查询中有使用主查询的字段的话，　ｓｕｂｑｕｅｒｙ会执行ｎ次，如果主查询有ｎ的备选结果．　如果子查询没有使用到主查询的字段的话，　子查询只会在执行一次．

 

以此成贴，引以为戒．