MYSQL Subquery in columns of select statement

Question

How are subqueries in the column field of select (projection) paired with the result of the main query? in the form:

 SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name
 FROM Users

Is the subquery executed once per row, from the output of SELECT id,email FROM Users,and thus, one should use LIMIT 1 on the subquery (since only 1 row from the subquery can be paired with a row from the main query), or does the subquery run once, and then each result is paired with the corresponding row from SELECT id,email FROM Users, much like the equivalent join: SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id

Answer 1

in ideal situation, when Names.id = Users.id return only 1 record, both queries the same.

The difference when it not true.

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name
 FROM Users

will stop work and return error, so You will need add LIMIT class

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id ORDER BY something LIMIT 1) as name
 FROM Users

at the same time query

SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id

continue work without errors, this query return all rows from Names related to Users

In some other cases when You expect only 1 Name, You will need add GROUP BY conditions

SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id GROUP BY Users.id

But this condition could return unpredicted name from Names (and it not 100% legal construction for SQL), and You again would need add 1 more level of JOIN with derived tables, and some time it could be ugly construction,

so, You always can compare what form of query more correct for selected case, simple example:

SELECT t1.id,t1.email,t2.name FROM Users t1 JOIN 
(SELECT id, name FROM Names n1 INNER JOIN 
(SELECT MAX(dateregistered) as dateregistered, id FROM Names GROUP BY id) n2 ON n1.id=n2.id AND n1.dateregistered=n2.dateregistered) t2
ON t1.id = t2.id

will return same result as:

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id ORDER BY dateregistered DESC LIMIT 1) as name
FROM Users

Add: Example with Names look like not realistic, but real situation - when You need request not name which is really 1 per person, but actual postal address for client with 10 years history. He can have 20 addresses, and You need return most resent

Answer 2

In my experience Yes, your sub query will run for every row of your outer query.

Also since you are not using any form of grouping/aggregation in your sub query, there is no guarantee on what row that query will return.

As a side not that query is better written as such:

SELECT u.id,u.email,n.name
FROM Users u
JOIN Names n
    ON n.id = u.id

As pointed out below, if Table Users and Table Names do not hold a true 1 to 1 relationship. there is a potential for a row in Users to have multiple rows in Names with the same id. If this is the case, you could make the following changes to the query to account for this.

SELECT u.id,u.email,n.name
FROM Users u
RIGHT JOIN Names n
    ON n.id = u.id
WHERE u.id IS NOT NULL
LIMIT 1

This will yield you every row from the Users table, that posses a matching id in the Names table. But limit the responses to 1. Keep in mind that without the LIMIT 1, this would list duplicate u.emails, if for some reason, multiple entries in the Names table have the same id.

Answer 3

MYSQL, v8:

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale.customer_name
FROM
  salesperson,
  -- find maximum size and customer at same time
  LATERAL
  (SELECT amount, customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC LIMIT 1)
  AS max_sale;