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.

Modifying Clause Function
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:

SELECT(HAVING(GROUP BY(WHERE(FROM...))))

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).

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com