CODEWITHSUNDEEP
mysqlsubquery
oneCoderToRuleThemAll
oneCoderToRuleThemAll

Reputation: 865

MySQL: Using subquery in SELECT clause and how many times is it executed?

If I have query such as :

SELECT firstName, age, COALESCE(studentStatus, SELECT status FROM Statuses WHERE default = true) FROM Student;

How many times is the query inside the COALESCE executed? I'm thinking since it doesn't depend on the outer query, it should be only executed once. Is this true and how does it work ? Does MySQL cache the result somewhere ?

Upvotes: 0

Views: 105

Answers (1)

spencer7593
spencer7593

Reputation: 108480

Use EXPLAIN to see query execution plan. MySQL could execute that subquery for each row returned by the outer query, it would be valid to do that. Since it's not a correlated subquery, its likely that MySQL will optimize that by executing the subquery just once, and cache the result, and return the cached value for each row.

Note that if that subquery would ever return more than one row, then the whole statement will throw an error. The subquery in the SELECT list should include some sort of guarantee that it won't return more than one row. Either an aggregate in the SELECT list (e.g. SELECT MAX(status) ) or a LIMIT 1 clause.

NOTE:

It's usually a bad idea to store age attribute. The age of a person is derived as the difference, in years, between date of birth and the current date. If we want the age "as of" a particular date, we use that date in place of current date.

My personal preference would be to avoid doing a subquery in the SELECT list, and do a join to an inline view that returns the default status.

Q: Can you provide an example of achieving the above with a join?

Here's an example:

SELECT t.firstname                              AS `first_name`
     , TIMESTAMPDIFF(YEAR,t.dob,DATE(NOW()))    AS `age_yrs` 
     , IFNULL(t.studentstatus,s.default_status) AS `status`
  FROM `Student` t
 CROSS
  JOIN ( SELECT MAX(d.status) AS default_status
           FROM `Statuses` d
          WHERE d.default = TRUE
       ) s
 ORDER
    BY t.firstname

Upvotes: 1

Related Questions