subquery: is it good practise to use having clause to filter out rows of outer query that contains subquery’s null values

I have the following tables:

SELECT * FROM medicine

| id | name        | producer |
+----+-------------+----------+
|  1 | aspirin     | pharma   |
|  2 | cetamol     | tamiko   |
|  3 | brufen      | pharma   |
|  4 | cetacodeine | adamco   |
|  5 | actifed     | tamiko   |
|  6 | claritin    | tamiko   |
+----+-------------+----------+

SELECT * FROM liquid

+-----+------+-------+
| lid | m_id | price |
+-----+------+-------+
| l1  |    2 |  1000 |
| l2  |    3 |  1500 |
| l3  |    4 |  3000 |
| l4  |    5 |  2000 |
| l5  |    6 |  1800 |
+-----+------+-------+

SELECT * FROM tablet

+-----+------+-------+
| tid | m_id | price |
+-----+------+-------+
| t1  |    1 |   900 |
| t2  |    2 |   600 |
| t3  |    3 |  1200 |
| t4  |    5 |  2000 |
+-----+------+-------+

Where m_id in the last two tablest has foriegn key referencing to id in the first table.

I’m trying to list lid and name of medicines that are liquid and produced only by producer tamiko Executing the following statement will result in rows that contains null values returned from the subquery and I want to omit

SELECT lid, (SELECT name FROM medicine WHERE liquid.m_id=medicine.id AND medicine.producer='tamiko') AS byTamiko FROM liquid;

+-----+----------+
| lid | byTamiko |
+-----+----------+
| l1  | cetamol  |                                                                                 
| l2  | NULL     |                                                                                 
| l3  | NULL     |                                                                                  
| l4  | actifed  |                                                                                 
| l5  | claritin |                                                                                
+-----+----------+

So I had some tries and using HAVING clause seems like a solution

SELECT lid, (SELECT name FROM medicine WHERE liquid.m_id=medicine.id AND medicine.producer='tamiko') AS byTamiko FROM liquid HAVING byTamiko IS NOT NULL;

| lid | byTamiko |
+-----+----------+
| l1  | cetamol  |
| l4  | actifed  |
| l5  | claritin |
+-----+----------+

However, my teacher disagreed saying that HAVING clause should be only used with aggregated functions and along with GROUP BY statement. I’m now confused whether this is an appropriate usage or not? and what happened exactly that made it work?

Note: I have to do this query using sub queries only and not any JOIN or different statement.

Note2: Be aware that This is not a duplicated, I’m trying to get an explanation on how it worked and whether it’s an correct usage or not.

Answer

It is not really appropriate.

In the SQL Standard having is defined as filtering after aggregation and where is for filtering before aggregation (if there is any aggregation).

MySQL extends this to allow having in non-aggregation queries. This extension allows you to filter on column aliases without using a subquery. But you should use where for filtering before aggregation.

Advertisement

Answer