Is there any diff between the results of the following tables?

Basic information:Ollivander’s Inventory from Hacker rank

is there any diff between

SELECT code, power, MIN(coins_needed) AS coins_needed
FROM Wands
GROUP BY code, power

and

SELECT code, m.power, m.coins_needed
 FROM (SELECT code, power, MIN(coins_needed) AS coins_needed 
       FROM Wands
       GROUP BY code, power
      ) AS m JOIN 
      Wands AS w
     ON m.code = w.code AND m.power = w.power AND 
        m.coins_needed = w.coins_needed

and why we need to join the original table itself to get the right result? I tried to use Product table from w3schools, it returns the same result except the order of data.

Answer

Obviously the two queries look different and would probably generate different execution plans. The queries also are different.

The most important difference is that the first query returns one row for each unique combination of code and power int he database.

The second query will return duplicates if there are two rows that have the same minimum values. In other words, it returns all rows in the original data that have the minimum number of coins for each code/power combination.

A more subtle difference is how NULL values are treated. The first query returns NULL values for code and power. The second filters out the NULL values.

The two queries should return the same results if code and power have no NULL values and if coins_needed is unique on rows with the same code and power. However, those are big assumptions and the queries are not equivalent.

Note that window functions are a simpler way to write the second query:

SELECT code, power, coins_needed
FROM (SELECT w.*,
             RANK() OVER (PARTITION BY code, power ORDER BY coins_needed) as seqnum
      FROM Wands w
     ) w
WHERE seqnum = 1;

Well, this is not 100% equivalent because it includes NULL values for code and power.