MYSQL Subquery in columns of select statement

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

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

StackExchange Database Administrators Q#168673, answer score: 8