Value Expressions

We've been selecting the highest-paid people in the employees table a lot. To learn about a new kind of subquery, let's just focus on employees of 1 company: Footbook. How do we get the highest-paid person at Footbook? Suppose we know ahead of time that the highest salary at Footbook is $254,000. Then, the query is simple.

SELECT
  name,
  department,
  job_title
FROM employees
WHERE company = 'Footbook'
  AND salary = 254000;

That query is fine when we knew ahead of time the specific salary and company name, but what if we don't know the salary ahead of time? Can we get the answer in a single query? After learning about putting subqueries in table expressions, we can do it this way…

SELECT
  employees.name,
  employees.department,
  employees.job_title
FROM (
  SELECT salary
  FROM employees
  WHERE company = 'Footbook'
  ORDER BY salary DESC
  LIMIT 1
) top_salary
JOIN employees
  ON employees.company = 'Footbook'
  AND employees.salary = top_salary.salary;

However, it is a little complicated and confusing. If someone just gave you this query without any context, it might take you a while to figure out what it means.

Rewrite the Query More Intuitively

The version we had at first is just more readable.

SELECT
  name,
  department,
  job_title
FROM employees
WHERE company = 'Footbook'
  AND salary = (top_salary);

All we really want is to substitute a single value for (top_salary). Is there a way we could nest a subquery in the place of (top_salary)? At first, there seems to be no reason why we can't do that. However, let's recall our diagram showing SQL statement structure…

In our query (top_salary) occurs inside a WHERE clause. According to our diagram, WHERE expects a value expression, not a table expression, and I told you that subqueries can be in place of a table expression, as in FROM, JOIN, or WITH clauses.

So, can we put a subquery inside our WHERE clause? Yes, it is still possible.

SELECT
  name,
  department,
  job_title
FROM employees
WHERE company = 'Footbook'
  AND salary = (
    SELECT salary
    FROM employees
    WHERE company = 'Footbook'
    ORDER BY salary DESC
    LIMIT 1
  );

However, there is an additional constraint when nesting subqueries in place of value expressions. The subquery must return a table with 1 row and 1 column, or else you get an error.

Let's just see what happens when you break that rule…

SELECT
  name,
  department,
  job_title
FROM employees
WHERE company = 'Footbook'
  AND salary = (
    SELECT salary
    FROM employees
    WHERE company = 'Footbook'
    ORDER BY salary DESC
    -- 2 rows being returned
    LIMIT 2
  );

error: more than one row returned by a subquery used as an expression

SELECT
  name,
  department,
  job_title
FROM employees
WHERE company = 'Footbook'
  AND salary = (
    -- 2 columns being returned
    SELECT salary, name
    FROM employees
    WHERE company = 'Footbook'
    ORDER BY salary DESC
    LIMIT 1
  );

error: subquery must return only one column

Watch out for this difference in the future. Try to keep in mind when table expressions vs value expressions are expected. SQL is rather unique in that it has 2 different types of expressions, and this is one of its most common criticisms, as it can be quite confusing.