Using max result as a value for a predicate in SQL

Question

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)

Answer 1

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.

Answer 2

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