Полезная информация

next up previous contents index
Next: Distinct Rows Up: Customizing Queries Previous: Regular Expressions

CASE Clause

Many programming languages have conditional statements, stating if condition is true then do-something, else do-something-else. This allows execution of statements based on some condition. While SQL is not a procedural programming language, it does allow conditional control over what data is returned from a query. The WHERE clause allows great control, while the CASE statement allows more fine-grained control. Figure [*] shows a query using CASE to create a new output column showing adult or minor as appropriate, based on the age field.

  
Figure: CASE example
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...\par Victor~~~~~~~~~\vert~22\vert adult
\par (6~rows)\end{list}\par
\end{figure}

Of course, the values adult and minor do not appear in the table friends. The CASE clause allows the creation of those conditional strings.

A more complex example is shown in figure [*].

  
Figure: Complex CASE example
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...ictor~~~~~~~~~\vert PA~~~\vert close~~~
\par (6~rows)\end{list}\par
\end{figure}

In this example, there are multiple WHEN clauses, and an AS clause to label the column with the word distance. Though I have shown only SELECT examples, CASE can be used in UPDATE and other complex situations. CASE allows the creation of conditional values, which can be used for output or for further processing in the same query. CASE values only exist inside a single query, so they can't be used outside the query that defines them.


next up previous contents index
Next: Distinct Rows Up: Customizing Queries Previous: Regular Expressions
Bruce Momjian
1999-11-21