Filter rows in a query using HAVING in MySQL
HAVING is usually used with GROUP BY, but in my query I need it so that I can filter the derived column
Example request:
SELECT
id,
NOW() < expiration_date
OR expiration_date IS NULL AS is_active
FROM
products
HAVING is_active = 1 ;
I could also use a temporary 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 ;
Anyway, I'm getting the results I want, but is it really useful to use HAVING even if you don't have a GROUP BY and just to filter the resulting rows. Which one is better?
source to share
The second query is better.
BTW, since you are limiting your results to an expression that you can even shorten to:
SELECT
id,
1 AS is_active
FROM
products
WHERE NOW() < expiration_date OR expiration_date IS NULL;
Your first request does not match. Mainly because it is not standard SQL and thus may confuse the reader. The request is invalid in most other dbms. HAVING - for aggregated records.
Typical is concatenation and GROUP BY, and then filtering the results with HAVING. Omitting GROUP BY usually gives you one record (as in select max(col) from mytable
). HAVING in this case will filter one result record, so you get it or not. Example:) select max(col) as maxc from mytable having maxc > 100
.
In MySQL, you can exclude GROUP BY clauses. For example, select id, name from mytable group by id
will give you an ID plus a name corresponding to that ID (and as usual, one entry per ID, you will get that one name). In other dbms, you will need to use an aggregated function by name like MIN or MAX, or have the name in the GROUP BY clause. In MySQL, you don't have to. Omitting it means: get one of the values in the found (group) records.
So your first query looks something like this: The collection of my data (because you are using HAVING) for one record (since there is no GROUP BY clause), so you will get one record with a random ID. Obviously, this is not what the request is doing, but honestly, I would not be able to cover it. I'm not a MySQL guy, so I would have to try and understand how it works if you didn't say that it works as you expected.
source to share