How to Use Modifying Clauses in SQL
The modifying clauses available in SQL are FROM, WHERE, HAVING, GROUP BY, and ORDER BY. The FROM clause tells the database engine which table or tables to operate on. The WHERE and HAVING clauses specify a data characteristic that determines whether or not to include a particular row in the current operation. The GROUP BY and ORDER BY clauses specify how to display the retrieved rows.
|FROM||Specifies from which tables data should be taken|
|WHERE||Filters out rows that don’t satisfy the search condition|
|GROUP BY||Separates rows into groups based on the values in the grouping columns|
|HAVING||Filters out groups that don’t satisfy the search condition|
|ORDER BY||Sorts the results of prior clauses to produce final output|
If you use more than one of these clauses, they must appear in the following order:
SELECT column_list FROM table_list [WHERE search_condition] [GROUP BY grouping_column] [HAVING search_condition] [ORDER BY ordering_condition] ;
Here’s the lowdown on the execution of these clauses:
The WHERE clause is a filter that passes the rows that meet the search condition and rejects rows that don’t meet the condition.
The GROUP BY clause rearranges the rows that the WHERE clause passes according to the value of the grouping column.
The HAVING clause is another filter that takes each group that the GROUP BY clause forms and passes those groups that meet the search condition, rejecting the rest.
The ORDER BY clause sorts whatever remains after all the preceding clauses process the table.
As the square brackets ([ ]) indicate, the WHERE, GROUP BY, HAVING, and ORDER BY clauses are optional.
SQL evaluates these clauses in the order FROM, WHERE, GROUP BY, HAVING, and finally SELECT. The clauses operate like a pipeline — each clause receives the result of the prior clause and produces an output for the next clause. In functional notation, this order of evaluation appears as follows:
ORDER BY operates after SELECT, which explains why ORDER BY can only reference columns in the SELECT list. ORDER BY can’t reference other columns in the FROM table(s).