Different results in SELECT DISTINCT
(1) By Jason (JsToCode) on 2021-04-24 09:29:24 [link] [source]
Here are the two SQL statements: SELECT DISTINCT `login` FROM student; SELECT (SELECT DISTINCT `login`) FROM student; When there two students have the same name, the first SQL will return one name, but the second SQL will return two same name. That means the keyword DISTINCT plays no roles in the second SQL. Is there some implement error causing this problem? The followings are machine details: SQLite version 3.31.1 Ubuntu 20.04
(2) By Richard Hipp (drh) on 2021-04-24 10:58:10 in reply to 1 [link] [source]
This behavior seems correct to me. And PostgreSQL, MySQL, SQL Server, and Oracle all work the same way.
(3) By Ryan Smith (cuz) on 2021-04-24 12:26:32 in reply to 1 [link] [source]
This is standard scoping rules. If the field/identifier you reference in the local scope (inner query) doesn't exist in the local scope, then it refers to that of the parent scope (outer query) if it exists - rinse and repeat up to global scope (irrelevant to SQL queries).
Richard already pointed out that this works in most SQL engines as well as SQLite, but it's also prevalent in most programming languages.
A good way to avoid is to always fully qualify inner queries with Aliasing where needed.
(4) By Keith Medcalf (kmedcalf) on 2021-04-24 21:55:55 in reply to 1 [source]
The equivalent statements would be:
SELECT DISTINCT login FROM student; SELECT DISTINCT (SELECT login) FROM student; SELECT DISTINCT (SELECT DISTINCT login) FROM student;
In your example you "forgot" to apply DISTINCT to the output rows in the second case, so the output was not DISTINCT (the default is ALL).