HAVING, ORDER BY, GROUP BY, COUNT in SQL
HAVING is used to filter the result of a GROUP BY by a given boolean condition.
Difference between HAVING and WHERE clauses
HAVING is used as WHERE, but in a different part of the SQL statement and, accordingly, at a different stage in the formation of the answer.
ORDER BY operator
ORDER BY orders the query output according to the values in a given number of selected columns. Numerous columns are ordered one within the other. It is possible to define ASC ascending or DESC decreasing for each column. The default is ascending.
GROUP BY clause
GROUP BY is used to aggregate result records based on specified attributes.
When using GROUP BY, all NULL values are considered equal.
Difference between GROUP BY and DISTINCT clauses
DISTINCT indicates that only unique column values are used for calculations. NULL counts as a separate value. GROUP BY creates a separate group for all possible values (including NULL).
If only duplicates need to be removed, it is better to use DISTINCT, GROUP BY is best used to define groups of records on which aggregate functions can be applied.
List of the main aggregate functions
Aggregate functions are functions that take groups of values and reduce them to a single value.
SQL provides several aggregate functions:
- COUNT - counts records that meet the query condition
- SUM - calculates the arithmetic sum of all column values
- AVG - calculates the arithmetic average of all values
- MAX - determines the largest of all selected values
- MIN - determines the smallest of all selected values
Difference between COUNT(*) and COUNT({column})
COUNT (*) counts the number of records in a table without ignoring the NULL value, since this function operates on records, not columns.
COUNT ({column}) counts the number of values in {column}. This form of the COUNT function does not take NULL values into account when counting the number of column values.
EXISTS operator
EXISTS takes a subquery as an argument and evaluates to TRUE if the subquery returns any records and FALSE if not.
Read also:
Comments
Post a Comment