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

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?

CodePudding user response：

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.

CodePudding user response：

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.