MySQL: Having

1. Eg.

  

# Fetch the difference between shop_price and market_price, and list the goods whose difference is bigger than 200

# The first part
select *, (market_price - shop_price) as difference from goods;

# The whole part
# The below approach will calculate twice, poor performance.
select *, (market_price - shop_price) as difference from goods where (market_price - shop_price) > 200;
# The below approach will calculate only once, better performance.
select *, (market_price - shop_price) as difference from goods having difference > 200;

# The key point to understand above is to understand the difference between real table and result set/virtual table/view.

# "where" is only applicable to real table column
# "having" is applicable to view/alias/result set

# It is hard to differentiate the difference of real table and result only judging from the structure.