How to know where are all places we can use subqueries in mysql?

Question

I am trying using subqueries in mysql, and came across the below statements where we can use subqueries in different places.

select Salary from Employee where (select max(Salary) from Employee) < 400;
select Salary from Employee where Salary < (select max(Salary) from Employee);
select Salary from (select * from Employee) as seq1 where Salary < 400;

Now I am getting confused where are all places we can use subquery as there are multiple possibilities in using it.

When I check the documentation for WHERE clause, it just mentioned giving condition as input, but nothing mentioned about where are all we can use subqueries to make the syntax correct

Barmar · Accepted Answer · 2020-05-04T21:59:37.307

You can use a subquery anywhere that a table is allowed and you're just reading the table, such as the FROM or JOIN clauses. This is called a derived table. That's your third example. Since it has to be only for reading, you can't use it as the target for UPDATE or DELETE (but you can join with a subquery in an UPDATE or DELETE query).

You can use a subquery anywhere that an expression is allowed. The subquery has to select a single value and must return at most one row (if it returns now rows it evaluates to NULL). A restriction is that you can't use a subquery in the WHERE clause of an UPDATE or DELETE query that's targeted to the same table; in that cause you have to join with the subquery (see You can't specify target table for update in FROM clause).

This means that a subquery can be used in the SELECT list, in the WHERE clause, and in the HAVING clause. Even in the ON clause, although I've rarely seen examples of this.

You can use a subquery as in place of the list of values in an IN expression, e.g.

SELECT Salary
FROM Employee
WHERE id in (SELECT Emp_id FROM Employee_Department WHERE Dept_id = 100)

However, in my experience MySQL often optimizes these poorly, and they're better written as joins:

SELECT e.Salary
FROM Employee AS e
JOIN Employee_Department AS d ON e.id = d.emp_id
WHERE d.dept_id = 100;

A SELECT query can also be used as the source of data in an INSERT query:

INSERT INTO low_salary_employees
SELECT employee_id
FROM Employee
WHERE Salary < (SELECT MAX(Salary)/10 FROM Employee)

you mentioned that I can use subquery anywhere that an expression is allowed, but I cannot use it with functions like max or count right? so max and count wont take expression as input? — Kalyan Kumar, May 04 '20 at 22:06
For an aggregation function, the expression has to be dependent on the rows being aggregated. I haven't tried, but maybe you could use a correlated subquery there. — Barmar, May 04 '20 at 22:11

score 1 · Answer 2 · answered May 04 '20 at 21:49

The first two example queries make use of scalar subqueries, ie subqueries that return a single row and column. You can use such query everywhere you need a scalar value - so this opens a wide range of use cases, like in a select clause, in a where predicate, and so on.

The third example is different: here, the subquery returns a set of records (potentially more than one row and more than one column). This is called a derived table. You can use pretty much the same way you would use a regular table, so mostly in the from clause of a query.

How to know where are all places we can use subqueries in mysql?

2 Answers2