SQL - Difference between FROM(subquery) and WHERE

I would like to ask to diference between following two SQL statements. The first one is working correctly, but the second one not. When I "create a new table" from subquery then result is correct, but if I use the same subquery in WHERE-IN statement then I get a different result.

SELECT `T`.`city`, COUNT(*)
FROM (
    SELECT `address`.`city`
    FROM `address`
    INNER JOIN `person` ON `person`.`address_id`=`address`.`address_id`
    INNER JOIN `person_detail` ON `person_detail`.`person_detail_id`=`person`.`person_detail_id`
    WHERE (`person_detail`.`phone` LIKE '% 42056%') OR (`person_detail`.`phone` LIKE '% 42057%')
) AS T
GROUP BY `T`.`city`  
ORDER BY `COUNT(*)`  ASC

///////////////////////////////////

SELECT `address`.`city`, COUNT(*)
FROM `address`
WHERE `address`.`city` IN (
    SELECT `address`.`city`
    FROM `address`
    INNER JOIN `person` ON `person`.`address_id`=`address`.`address_id`
    INNER JOIN `person_detail` ON `person_detail`.`person_detail_id`=`person`.`person_detail_id`
    WHERE (`person_detail`.`phone` LIKE '% 42056%') OR (`person_detail`.`phone` LIKE '% 42057%')
) 
GROUP BY `address`.`city`  
ORDER BY `COUNT(*)`;

CodePudding user response：

The first query will run the subquery first which returns a distinct list of 'city'. You then do another group by on it with a count which should lead to a result set of 'city' with all ones next to it. In essence you are running your query off of the subquery (not the address table itself).

Your second query will run the subquery first, return the distinct list of 'city' then using that list, go back to the original table and return everything that matches (which should be the entire table of address) and then group by it and return a count. This will lead to a different result since you are hitting the original table vs hitting the subquery result.