WHERE or HAVING: Where Should Calculated Columns Go in SQL?

Correct position of calculated column in SQL query: WHERE or HAVING?

In SQL, the conditions used to retrieve data can be placed in the WHERE or HAVING clause. When using computed columns, understanding their correct location is critical to ensuring efficient queries.

WHERE and HAVING: Key Differences

• WHERE clause: Filters before data selection , allowing the use of any table column condition.
• HAVING clause: Filters after data selection , allowing conditions using selected columns, aliases or aggregate functions.

Calculated column position

Computed columns must be placed in the HAVING clause, not the WHERE clause. This is because:

• The WHERE clause acts on the original table data.
• HAVING clause acts on the calculated transformed data.

Example:

Consider a table containing 'id' and 'value' columns:

CREATE TABLE `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

To get values ​​greater than 5, you can use two queries:

-- WHERE 子句
SELECT `value` v FROM `table` WHERE `value` > 5;

-- HAVING 子句
SELECT `value` v FROM `table` HAVING `value` > 5;

These two queries return the same results, but there are key differences. The WHERE clause raises an error if the alias 'v' is used, while the HAVING clause allows it.

Performance impact

Using WHERE clauses on computed columns on large tables may have a negative impact on performance. This is because filtering is applied before is calculated, potentially resulting in unnecessary calculations. Improve performance by placing calculations in the HAVING clause, using only filtered data.

Conclusion

When using a calculated column, it must be placed in the HAVING clause to ensure correct calculation and optimal performance. The distinction between WHERE and HAVING clauses allows for greater flexibility and efficient data filtering.

The above is the detailed content of WHERE or HAVING: Where Should Calculated Columns Go in SQL?. For more information, please follow other related articles on the PHP Chinese website!