SQL ZOO

記錄一下我跟別人不同的寫法，以後對 SQL 語法的效能更有感之後，再回頭來看看有什麼差別。

目前還不太確定 SQL 語法的 coding style，例如縮排要空幾格、怎麼分行之類的。

SELECT in SELECT

8. List each continent and the name of the country that comes first alphabetically.

我的

SELECT continent, name FROM world x
WHERE name = (SELECT name FROM world y WHERE y.continent = x.continent 
              ORDER BY name LIMIT 1);

別人的

SELECT continent, name
FROM world x
WHERE name <= ALL(SELECT name FROM world y WHERE y.continent = x.continent)

不過我用 ORDER 應該會比較慢，最快的 merge sort，時間複雜度是 O(n log n)。別人用 <= ALL，時間複雜度應該是 O(n)。

9. Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

我的

SELECT name, continent, population FROM world 
WHERE continent NOT IN (SELECT continent FROM world WHERE population > 25000000);

別人的

SELECT name, continent, population FROM world 
WHERE continent IN (SELECT continent FROM world  x WHERE 25000000 >= (
  SELECT MAX(population) FROM world y WHERE x.continent = y.continent
));

or

SELECT y.name, y.continent, y.population
FROM world AS y
JOIN
(SELECT continent,max(population)
FROM world
GROUP BY continent
HAVING max(population) <= 25000000) AS x
ON y.continent = x.continent

別人的

SELECT name, continent, population
FROM world x
WHERE 25000000  > ALL(SELECT population FROM world y 
                      WHERE x.continent = y.continent AND y.population > 0)

別人的寫法出處：

• 4_soutions.sql

• select-in-select.sql

The JOIN operation

3. show the player, teamid, stadium and mdate for every German goal.

我的

SELECT player, teamid, stadium, mdate
    FROM game JOIN goal ON id = matchid
WHERE teamid = 'GER';

別人的

SELECT player, teamid, mdate
FROM game
  JOIN goal ON (id=matchid AND teamid='GER')

限制的條件，放在 WHERE，或放在 JOIN 不知有什麼差別。

8. show the name of all players who scored a goal against Germany.

我的

SELECT DISTINCT(player)
    FROM goal JOIN game ON matchid = id
WHERE teamid <> 'GER' AND
    (
        (teamid = team1 AND team2 = 'GER') OR 
        (teamid = team2 AND team1 = 'GER')
    );

別人的

SELECT DISTINCT(player)
FROM game
  JOIN goal ON matchid = id
WHERE ((team1='GER' OR team2='GER') AND teamid != 'GER')

別人的 where 寫得比我精簡多了。

10. Show the stadium and the number of goals scored in each stadium.

我的

SELECT stadium, COUNT(matchid)
    FROM game JOIN goal ON id = matchid
GROUP BY stadium;

別人的

SELECT stadium, COUNT(player) AS goals
FROM game
  JOIN goal ON (id=matchid)
GROUP BY stadium

別人的

SELECT stadium,COUNT(1)
  FROM goal JOIN game ON id=matchid
GROUP BY stadium

我寫的時候很猶豫，到底要 COUNT 哪個欄位，結果，原來 COUNT 是這樣的 COUNT！

別人的寫法出處：

• join.sql

• SQL ZOO Cheat mode

---

最後更新日期：2021 年 11 月 6 日