Using max result as a value for a predicate in SQL

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)

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

• 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"

• the equality operator in SQL is =, not ==

• 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.

StackExchange Database Administrators Q#155886, answer score: 8