Where to place functions inside/outside a subquery?

[MrSwitch]

When using a function around a field value which points to another table it usually creates a subquery, but where should the function go, around the field within the subquery, or around the subquery?

For example...

e.g.

{
	"table": "members"
	"fields": [
		{
			"hasEmail": "IF(email.id, 'YES', 'NO')"
		}
	]
}

In most instances, Dare would create a subquery, so the query would look like...

SELECT name, (
	SELECT IF(id, "YES", "NO")
	FROM email
	WHERE email.member_id = members.id
	LIMIT 1
)
FROM members

Notice how the IF(...) is within the Subquery itself.

However if there is are no matches in the joining table the result will be NULL not "NO" - because the subquery returned zero rows. It needs at-least one empty row so the IF condition can say "No".

What was really meant was...

SELECT name, IF((
	SELECT id
	FROM email
	WHERE email.member_id = members.id
	LIMIT 1
),  "YES", "NO")
FROM members

Of course some functions are aggregates like MAX, COUNT, GROUP_CONCAT, ... these will always need to be defined within the subquery to work. They even have the affect of aggregating the results of a query within mysql.

[MrSwitch]

Workaround: The above can be worked around using an aggregated function, because they force a single row (except when there is a groupby)

e.g. using COUNT(): IF(COUNT(email.id), 'YES', 'NO')

{
	"table": "members"
	"fields": [
		{
			"hasEmail": "IF(COUNT(email.id), 'YES', 'NO')"
		}
	]
}