3

Say I want to find all the employees that earn more than all of the employees of Google, is it possible to use the result from max as if it was a single value and not a table like so?

select person-name
from Works
where salary > ( select max(salary)
        from Works
        where company-name == "Google" )

(Works has the columns person-name, salary and company-name)

| improve this question | | | | |
9

Yes this is possible. Your query has several syntax errors however.

  1. a dash in an identifier is invalid. I used an underscore instead in the below example. If you did create the columns that way, you will need to quote them, e.g. "person-name"
  2. the equality operator in SQL is =, not ==
  3. String constants need to be put in single quotes (') not double quotes

It is possible to use the result of the max subquery as a single values because it is a single value. To be accurate it is a subquery that because of the max() returns a single column and a single row.

select person_name
from Works
where salary > (select max(salary)
                from Works
                where company_name = 'Google');

You could use an alternative way with > ALL instead of >, with a subquery that doesn't have an aggregate function and returns many values (many rows but still a single column):

select person_name
from Works
where salary > ALL (select salary
                    from Works
                    where company_name = 'Google'
                      and salary is not null );

Note the salary is not null condition in this second query. Nulls must be taken out of the comparison because each of them would evaluate the > operation to unknown rather than true or false, and as a result the whole > ALL condition result would be unknown and you would get no rows in the output.

| improve this answer | | | | |
1

This is SQL Server syntax (you haven't said what version of SQL you are on) but you could declare it as a variable;

DECLARE @GoogleSalary int; SET @GoogleSalary = (SELECT MAX(salary) FROM Works WHERE [company-name] = 'Google')

SELECT
    [person-name]
FROM Works
WHERE salary > @GoogleSalary
| improve this answer | | | | |
  • 1
    It's old theoretical SQL, not some live version. I'm not sure what is the official name of it other than just "SQL". – shinzou Nov 21 '16 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.