How to execute a subquery in SQL (SELECT passing parameter from main query?

Question

MySQL

Please tell me how to solve this problem.

Conditionally, there is a table Users
id, name

There is a table Orders
id, number, user_id

I want to calculate the number of orders for each user through a subquery.

Something like
select users.name as username,
(select count(id) from orders where user_id=users.id) as orders_count from
users

subqueries are executed from the bottom up, users.id cannot be passed to the subquery.

Yes, you can do a join, but I have a problem with it, because in my case, it is already there according to other criteria with the same tables, according to which I want to calculate the data, in general, some other mechanism is needed. Those.

Answer 1

select u.name as username,
(select count(id) from orders where user_id=u.id) as orders_count
from users u

Add an alias for the users table. This is how it should work.
But the option is not very good, I suppose that knowing all the conditions, you can come up with a more productive solution.

Answer 2

SELECT users.name AS username,
       count(orders.id) AS orders_count 
FROM users
JOIN orders ON orders.user_id=users.id
GROUP BY users.name

If you want to display also users who have no orders with zero quantity, then use LEFT JOIN .
PS. The query from the question text is also correct and should give the correct result. Moreover, with a 50% probability it will be converted to mine (more precisely, both will give the same execution plan). The remaining 50% - that it will be executed iteratively (and then most likely it will just take longer).