Where with SQL subquery

Question

I have the following query:

SELECT 
`id`, `firstName`, `lastName`, `nickName`, `email`, 
`verificationCode`, `roleId`, `profileImageId`, `statusId`, `createdAt`, 
(SELECT COUNT(*) FROM RecipeStores WHERE RecipeStores.userId = User.id) AS `countStoredRecipes`, 
(SELECT COUNT(*) FROM RecipeFavorites WHERE RecipeFavorites.userId = User.id) AS `countLikedRecipes`, 
(SELECT COUNT(*) FROM Recipes WHERE Recipes.createdById = User.id) AS `countCreatedRecipes` 
FROM `Users` AS `User` WHERE `User`.`countStoredRecipes` >= 2;

However, it doesn't work. I would like to filter by the result of the subquery.

I understand that the subquery cannot be worked as one more column, so I would expect some other way to do it.

Use `HAVING` rather than `WHERE`. – Barmar Nov 17 '20 at 20:57 — Barmar, Nov 17 '20 at 20:57

GMB · Accepted Answer · 2020-11-17T21:01:44.757

MySQL implements an extension to standard SQL that lets you refer to an alias in the HAVING clause:

SELECT 
    `id`, `firstName`, `lastName`, `nickName`, `email`, 
    `verificationCode`, `roleId`, `profileImageId`, `statusId`, `createdAt`, 
    (SELECT COUNT(*) FROM RecipeStores rs WHERE rs.userId = u.id) AS `countStoredRecipes`, 
    (SELECT COUNT(*) FROM RecipeFavorites rf WHERE rf.userId = u.id) AS `countLikedRecipes`, 
    (SELECT COUNT(*) FROM Recipes r WHERE r.createdById = u.id) AS `countCreatedRecipes` 
FROM `Users` u
HAVING `User`.`countStoredRecipes` >= 2;

I am not a big fan of this technique: this really is bespoke MySQL syntax, that wouldn't run in any other database.

A more standard option is to turn the existing query to a subquery, and filter in the outer scope:

SELECT *
FROM (
    SELECT 
        `id`, `firstName`, `lastName`, `nickName`, `email`, 
        `verificationCode`, `roleId`, `profileImageId`, `statusId`, `createdAt`, 
        (SELECT COUNT(*) FROM RecipeStores rs WHERE rs.userId = u.id) AS `countStoredRecipes`, 
        (SELECT COUNT(*) FROM RecipeFavorites rf WHERE rf.userId = u.id) AS `countLikedRecipes`, 
        (SELECT COUNT(*) FROM Recipes r WHERE r.createdById = u.id) AS `countCreatedRecipes` 
    FROM `Users` u
) t
WHERE `countStoredRecipes` >= 2;

In very recent versions of MySQL, you can use lateral joins:

SELECT 
    `id`, `firstName`, `lastName`, `nickName`, `email`, 
    `verificationCode`, `roleId`, `profileImageId`, `statusId`, `createdAt`, 
    rs.*, rf.*, r.* 
    () AS  
FROM `Users` u
INNER JOIN LATERAL (
    SELECT COUNT(*) AS `countStoredRecipes` 
    FROM RecipeStores rs 
    WHERE rs.userId = u.id
) rs ON rs.`countStoredRecipes`>= 2
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS `countLikedRecipes` 
    FROM RecipeFavorites rf 
    WHERE rf.userId = u.id
) rf ON true
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS `countCreatedRecipes` 
    FROM Recipes r 
    WHERE r.createdById = u.id
) r ON true

Where with SQL subquery

1 Answers1