3

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 general (as in, not necessarily true for MySQL), the query optimizer is going to make that call. In some cases it may treat this exactly as if it were written as an INNER JOIN; in others, in may actually have to run the query for every row. Hopefully someone can provide a more definitive answer. – RDFozz Mar 30 '17 at 17:52
2

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

  • it is possible select more of one column in subquery in a SELECT statement, when I try that returns and error Operator must have one column(s) ? – jjoselon Jan 4 at 16:14
0

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.

  • 1
    If the subquery returns more than one row, you get an error. – Rick James Apr 1 '17 at 20:08
  • By nature of the provided Names, the (email) it is assumed that Users table should hold a 1 to 1 relationship with Names. If this is not the design, I suppose one could replace the word "JOIN" with "RIGHT JOIN". and add WHERE u.id IS NOT NULL to the end of the statement. Would still be better then sub query. – DarbyM Apr 2 '17 at 18:38
  • @DarbyM then what is point of this sentence? "there is no guarantee on what row that query will return." – ypercubeᵀᴹ Apr 2 '17 at 20:16
  • 2
    Grouping/aggregation does not guarantee which row will be deliver with LIMIT 1. You need ORDER BY for such a guarantee. – Rick James Apr 3 '17 at 2:44
  • @ypercube what Rick James said. – DarbyM Apr 4 '17 at 0:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.