Subquery returns more than one item - how can the result set be compared to one column?

Question

SELECT 
    "employees"."FIRST_NAME", 
    "employees"."LAST_NAME", 
    "employees"."SALARY" 
FROM 
    'employees' 
WHERE 
    (("employees"."SALARY" > (SELECT "employees"."SALARY" 
                              FROM 'employees' 
                              WHERE (("employees"."FIRST_NAME" = "Alexander")))))`

The subquery returns 2 values. How can they be compared with "employees"."salary" ? That is, there are 2 employees with first name "Alexander"... Replacing the subquery with a 2 element tuple gives a query that is not accepted from the SQL client... That is this query should be equivalent to the one above but it does not execute correctly:

SELECT 
    "employees"."FIRST_NAME", "employees"."LAST_NAME", 
    "employees"."SALARY" 
FROM 
    'employees' 
WHERE 
    (("employees"."SALARY" > (3500, 9000)))

What is going on?

Answer 1

From SQL Language Expressions/11. Subquery Expressions:

A SELECT statement enclosed in parentheses is a subquery. All types of SELECT statement, including aggregate and compound SELECT queries (queries with keywords like UNION or EXCEPT) are allowed as scalar subqueries. The value of a subquery expression is the first row of the result from the enclosed SELECT statement. The value of a subquery expression is NULL if the enclosed SELECT statement returns no rows.

Your query would not run in any other database than SQLite.
But, SQLite as you can see from the documentation, instead of throwing an error like the subquery returns more than 1 rows, allows the subquery by keeping only the 1st of the rows that it returns.
This is one of SQLite's non-standard sql features and in your case it leads to wrong results.

What you would want, I believe, is to compare each employee's salary to the max salary of all employees named 'Alexander'.
You can do this by changing the subquery to:

SELECT MAX(SALARY) FROM employees WHERE FIRST_NAME = 'Alexander'

This is a not correlated scalar subquery, so there is no need for any aliases.

Answer 2

Note assuming that the SQLite tag is correct , i.e. there are many flavours of SQL and that the database manager being is used is therefore important and relevant.

the subquery returns 2 values. How can they be compared with "employees"."salary" ?

You compare multiple values using a function that can take multiple values such as max, which could be what you require.

e.g.

SELECT "employees"."FIRST_NAME", "employees"."LAST_NAME", "employees"."SALARY" FROM 'employees' WHERE (("employees"."SALARY" > max(3500, 9000)))

What is going on?

The first is using a WHERE clause that is a valid expression that is either true or false. The second is misusing values i.e. a list of values is provided where a single value is expected.

Answer 3

First, writing your queries, you should not have to "quote" every part, it gets cluttered and bloated. Also, you can use aliases to help readability. you'll see soon. If you use quotes, use the single quotes around specific values such as a date like > '2022-02-22'.

Now on to your query. Your query is looking for salaries greater than a given person (Alexander), but there are multiple people by that name. To get ONE answer, you might need the MAX() salary for the critiera. So this essentially becomes TWO queries... one relying upon the other.

So, to get you an answer, the outer query is what you will get as the results, the WHERE query is pre-qualifying that one salary you are interested in.

Select
      e.first_name,
      e.last_name,
      e.salary
   from
      employees e
   where
      e.salary > ( select max( e2.salary ) 
                      from employees e2
                      where e2.first_name = 'Alexander' )

Notice the where clause is getting whatever the MAX() salary value is from the employee table for the employee 'Alexander'. So now, that ONE value comes back as the basis for the outer query.

Notice the indentation, you can better see how the outer query is reliant on that. Also makes for more readable queries.