Filter rows in a query using HAVING in MySQL

Question

HAVING is usually used with GROUP BY, but in my query I need it so that I can filter a derived column

sample query:

SELECT 
  id,
  NOW() < expiration_date 
  OR expiration_date IS NULL AS is_active 
FROM
  products 
HAVING is_active = 1 ;

I could also use a temp table and just use WHERE instead of HAVING,

example:

SELECT id 
FROM
  (SELECT 
    id,
    NOW() < expiration_date 
    OR expiration_date IS NULL AS is_active 
  FROM
    products) 
WHERE is_active = 1 ;

Either way, I'm getting the desired results but is it really appropriate to use HAVING even if you have no GROUP BY and just for filtering derived rows. Which one is better?

Answer 1

The second query is better.

BTW, as you limit your results to the expression you can even shorten it to:

SELECT 
  id,
  1 AS is_active 
FROM
  products
WHERE NOW() < expiration_date OR expiration_date IS NULL;

Your first query is not good. Mainly because it's not standard SQL and may thus confuse its reader. The query is not valid in most other dbms. HAVING is for aggregated records.

The typical thing is to aggregate and GROUP BY and then filter the results with HAVING. Omitting GROUP BY would usually give you one record (as in select max(col) from mytable). HAVING would in this case filter the one result record, so you get that one or none. Example: select max(col) as maxc from mytable having maxc > 100).

In MySQL you are allowed to omit GROUP BY expressions. For instance select id, name from mytable group by id would give you the id plus a name matching that ID (and as there is usually one record per ID, you get that one name). In another dbms you would have to use an aggregate function on name, such as MIN or MAX, or have name in the GROUP BY clause. In MySQL you don't have to. Omitting it means: get one of the values in the (group's) records found.

So your first query looks a bit like: Aggregate my data (because you are using HAVING) to one record (as there is no GROUP BY clause), so you'd get one record with a random id. This is obviously not what the query does, but to tell the truth I wouldn't have been able to tell just from the look at it. I am no MySQL guy, so I would have had to try to know how it is working, had you not told us it is working as expected by you.