where the flamingcow roams

Converting subselects to joins, part 2

I previously discussed this in depth. However, today I saw a case that I didn't cover:

You have a table of Users and a table of Logins, with a row for each user login event. You're looking for users that have logged in within the last 31 days. The initial version of this I saw used a derived table:

SELECT
    UserId,
    LastLogin
  FROM Users
    JOIN (
      SELECT
          UserId,
          DATEDIFF(NOW(), MAX(TimeStamp)) AS LastLogin
        FROM Logins
        GROUP BY UserId
    ) AS Temp USING (UserId)
  WHERE LastLogin <= 31;

We can convert this to a simple JOIN with the magic of HAVING. HAVING is like WHERE, but applies after aggregation:

SELECT
    UserId,
    DATEDIFF(NOW(), MAX(TimeStamp)) AS LastLogin
  FROM Users
    JOIN Logins USING (UserId)
  GROUP BY UserId
  HAVING LastLogin <= 31;