The extended features are grouped as follows:

- Sorting Query Results -- using the ORDER BY clause
- Expressions -- in the SELECT clause and WHERE clause
- Literal -- self-defining values
- Function Call -- expression functions
- System Value -- builtin system values
- Special Construct -- special expression construct
- Numeric or String Operator -- expression operators

- Joining Tables -- in the FROM clause
- Outer Join -- extended join
- Self Join -- joining a table to itself

- Subqueries -- embedding a query in another
- Predicate Subqueries -- subqueries in logical expressions
- Scalar Subqueries -- subqueries in scalar expressions
- Table Subqueries -- subqueries in the FROM clause

- Grouping Queries -- using the GROUP BY clause,
Set Function and HAVING clause
- GROUP BY Clause -- specifying grouping columns
- Set Functions -- summary functions
- HAVING Clause -- filtering grouped rows

- Aggregate Queries -- using Set Functions and the HAVING clause
- Union Queries -- using the query operator, UNION
- Union-Compatible Queries -- query requirements for Union

When the ORDER BY clause is missing, the result rows from a query have
no defined order (they are *unordered*).
The ORDER BY clause defines the ordering of rows based on columns from
the SELECT clause.
The ORDER BY clause has the following general format:

ORDER BY column-1 [ASC|DESC] [ column-2 [ASC|DESC] ] ...

ORDER BY sorts rows using the ordering columns in left-to-right, major-to-minor order. The rows are sorted first on the first column name in the list. If there are any duplicate values for the first column, the duplicates are sorted on the second column (within the first column sort) in the Order By list, and so on. There is no defined inner ordering for rows that have duplicate values for all Order By columns.

Database *nulls* require special processing in ORDER BY.
A *null* column sorts higher than all regular values;
this is reversed for DESC.

In sorting, *nulls* are considered duplicates of each other for ORDER BY.
Sorting on *hidden* information makes no sense in utilizing the results
of a query. This is also why SQL only allows select list columns in ORDER BY.

For convenience when using expressions in the select list, select items can be specified by number (starting with 1). Names and numbers can be intermixed.

Example queries:

SELECT * FROM sp ORDER BY 3 DESC

sno | pno | qty |
---|---|---|

S1 | P1 | NULL |

S3 | P1 | 1000 |

S3 | P2 | 200 |

S2 | P1 | 200 |

SELECT name, city FROM s ORDER BY name

name | city |
---|---|

John | London |

Mario | Rome |

Pierre | Paris |

SELECT * FROM sp ORDER BY qty DESC, sno

sno | pno | qty |
---|---|---|

S1 | P1 | NULL |

S3 | P1 | 1000 |

S2 | P1 | 200 |

S3 | P2 | 200 |

- Literal -- quoted string, numeric value, datetime value
- Function Call -- reference to builtin SQL function
- System Value -- current date, current user, ...
- Special Construct -- CAST, COALESCE, CASE
- Numeric or String Operator -- combining sub-expressions

- String -- ASCII text framed by single quotes (').
Within a literal, a single quote is represented by 2 single quotes ('').
- Numeric -- numeric digits (at least 1) with an optional decimal point
and exponent. The format is
**[ddd][[.]ddd][E[+|-]ddd]**

- Date -- DATE 'yyyy-mm-dd'
- Time -- TIME 'hh:mm:ss[.fff]'
- Timestamp -- TIMESTAMP 'yyyy-mm-dd hh:mm:ss[.fff]'
- Interval -- INTERVAL [+|-] string interval-qualifier

- SUBSTRING(exp-1 FROM exp-2 [FOR exp-3])
Extracts a substring from a string -

*exp-1*, beginning at the integer value -*exp-2*, for the length of the integer value -*exp-3*.*exp-2*is 1 relative. If*FOR exp-3*is omitted, the length of the remaining string is used. Returns the substring. - UPPER(exp-1)
Converts any lowercase characters in a string -

*exp-1*to uppercase. Returns the converted string. - LOWER(exp-1)
Converts any uppercase characters in a string -

*exp-1*to lowercase. Returns the converted string. - TRIM([LEADING|TRAILING|BOTH] [FROM] exp-1)

TRIM([LEADING|TRAILING|BOTH] exp-2 FROM exp-1)Trims leading, trailing or both characters from a string -

*exp-1*. The trim character is a space, or if*exp-2*is specified, it supplies the trim character. If LEADING, TRAILING, BOTH are missing, the default is BOTH. Returns the trimmed string. - POSITION(exp-1 IN exp-2)
Searches a string -

*exp-2*, for a match on a substring -*exp-2*. Returns an integer, the 1 relative position of the match or 0 for no match. - CHAR_LENGTH(exp-1)

CHARACTER_LENGTH(exp-1)Returns the integer number of characters in the string -

*exp-1*. - OCTET_LENGTH(exp-1)
Returns the integer number of octets (8-bit bytes) needed to represent the string -

*exp-1*. - EXTRACT(sub-field FROM exp-1)
Returns the numeric sub-field extracted from a datetime value -

*exp-1*.*sub-field*is YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR or TIMEZONE_MINUTE. TIMEZONE_HOUR and TIMEZONE_MINUTE extract sub-fields from the Timezone portion of*exp-1*. QUARTER is (MONTH-1)/4+1.

- USER -- returns a string with the current SQL authorization identifier.
- CURRENT_USER -- same as USER.
- SESSION_USER -- returns a string with the current SQL session authorization identifier.
- SYSTEM_USER -- returns a string with the current operating system user.
- CURRENT_DATE -- returns a Date value for the current system date.
- CURRENT_TIME -- returns a Time value for the current system time.
- CURRENT_TIMESTAMP -- returns a Timestamp value for the current system timestamp.

- CAST(exp-1 AS data-type)
Converts the value -

*exp-1*, into the specified*date-type*. Returns the converted value. - COALESCE(exp-1, exp-2 [, exp-3] ...)
Returns

*exp-1*if it is not*null*, otherwise returns*exp-2*if it is not*null*, otherwise returns*exp-3*, and so on. Returns*null*if all values are*null*. - CASE exp-1 { WHEN exp-2 THEN exp-3 } ... [ELSE exp-4] END

CASE { WHEN predicate-1 THEN exp-3 } ... [ELSE exp-4] ENDThe first form of the CASE construct compares

*exp-1*to*exp-2*in each WHEN clause. If a match is found, CASE returns*exp-3*from the corresponding THEN clause. If no matches are found, it returns*exp-4*from the ELSE clause or*null*if the ELSE clause is omitted.The second form of the CASE construct evaluates

*predicate-1*in each WHEN clause. If the predicate is true, CASE returns*exp-3*from the corresponding THEN clause. If no predicates evaluate to true, it returns*exp-4*from the ELSE clause or*null*if the ELSE clause is omitted.

- String Operators
There is just one string operator - ||, for string concatenation. Both operands of || must be strings. The operator concatenates the second string to the end of the first. For example,

**'ab' || 'cd' ==> 'abcd'**

The numeric operators are common to most languages:

**+**-- addition**-**-- subtraction*****-- multiplication**/**-- division

- Integer -- TINYINT, SMALLINT, INT, BIGINT
- Exact -- NUMERIC, DECIMAL
- Approximate -- FLOAT, DOUBLE, REAL

The **+** and **-** operators can also be used as unary operators.

The numeric operators can be applied to datetime values, with some restrictions. The basic rules for datetime expressions are:

- A date, time, timestamp value can be added to an interval; result is a date, time, timestamp value.
- An interval value can be subtracted from a date, time, timestamp value; result is a date, time, timestamp value.
- An interval value can be added to or subtracted from another interval; result is an interval value.
- An interval can be multiplied by or divided by a standard numeric value; result is an interval value.

A special form can be used to subtract a date, time, timestamp value from another date, time, timestamp value to yield an interval value:

The(datetime-1 - datetime-2) interval-qualifier

A second special form allows a ? parameter to be typed as an interval:

? interval-qualifier

An example can best illustrate the rationale behind joins. The following query:

Produces:SELECT * FROM sp, p

sno | pno | qty | pno | descr | color |
---|---|---|---|---|---|

S1 | P1 | NULL | P1 | Widget | Blue |

S1 | P1 | NULL | P2 | Widget | Red |

S1 | P1 | NULL | P3 | Dongle | Green |

S2 | P1 | 200 | P1 | Widget | Blue |

S2 | P1 | 200 | P2 | Widget | Red |

S2 | P1 | 200 | P3 | Dongle | Green |

S3 | P1 | 1000 | P1 | Widget | Blue |

S3 | P1 | 1000 | P2 | Widget | Red |

S3 | P1 | 1000 | P3 | Dongle | Green |

S3 | P2 | 200 | P1 | Widget | Blue |

S3 | P2 | 200 | P2 | Widget | Red |

S3 | P2 | 200 | P3 | Dongle | Green |

A more usable query would correlate the rows from *sp* with rows from
*p*, for instance matching on the common column -- *pno*:

This produces:SELECT * FROM sp, p WHERE sp.pno = p.pno

sno | pno | qty | pno | descr | color |
---|---|---|---|---|---|

S1 | P1 | NULL | P1 | Widget | Blue |

S2 | P1 | 200 | P1 | Widget | Blue |

S3 | P1 | 1000 | P1 | Widget | Blue |

S3 | P2 | 200 | P2 | Widget | Red |

The join in this example is known as an *inner* *equi*-join.
*equi* meaning that the join predicate uses = (equals) to match the
join columns.
Other types of joins use different comparison operators.
For example, a query might use a *greater-than* join.

The term *inner* means only rows that match are included.
Rows in the first table that have no matching rows in the second table
are excluded and vice versa (in the above join, the row in *p* with
*pno* P3 is not included in the result.)
An *outer* join includes unmatched rows in the result.
See Outer Join below.

More than 2 tables can participate in a join.
This is basically just an extension of a 2 table join.
3 tables -- *a*, *b*, *c*, might be joined in various ways:

*a*joins*b*which joins*c**a*joins*b*and the join of*a*and*b*joins*c**a*joins*b*and*a*joins*c*

This query performs a 3 table join:

It joinsSELECT name, qty, descr, color FROM s, sp, p WHERE s.sno = sp.sno AND sp.pno = p.pno

name | qty | descr | color |
---|---|---|---|

Pierre | NULL | Widget | Blue |

John | 200 | Widget | Blue |

Mario | 1000 | Widget | Blue |

Mario | 200 | Widget | Red |

The outer join is specified in the FROM clause and has the following general format:

table-1 { LEFT | RIGHT | FULL } OUTER JOIN table-2 ON predicate-1

- LEFT -- only unmatched rows from the left side table (
*table-1*) are retained - RIGHT -- only unmatched rows from the right side table (
*table-2*) are retained - FULL -- unmatched rows from both tables (
*table-1*and*table-2*) are retained

SELECT pno, descr, color, sno, qty FROM p LEFT OUTER JOIN sp ON p.pno = sp.pno

pno | descr | color | sno | qty |
---|---|---|---|---|

P1 | Widget | Blue | S1 | NULL |

P1 | Widget | Blue | S2 | 200 |

P1 | Widget | Blue | S3 | 1000 |

P2 | Widget | Red | S3 | 200 |

P3 | Dongle | Green | NULL | NULL |

SELECT DISTINCT a.pno FROM sp a, sp b WHERE a.pno = b.pno AND a.sno <> b.sno

pno |
---|

P1 |

Self joins are often used in subqueries. See Subqueries below.

There are 3 basic types of subqueries in SQL:

- Predicate Subqueries -- extended logical constructs in the WHERE (and HAVING) clause.
- Scalar Subqueries -- standalone queries that return a single value; they can be used anywhere a scalar value is used.
- Table Subqueries -- queries nested in the FROM clause.

- IN Subquery
The IN Subquery tests whether a scalar value matches the single query column value in any subquery result row. It has the following general format:

**value-1 [NOT] IN (query-1)**

For example, to list parts that have suppliers:NOT value-1 IN (query-1)

SELECT * FROM p WHERE pno IN (SELECT pno FROM sp)

pno | descr | color |
---|---|---|

P1 | Widget | Blue |

P2 | Widget | Red |

The Self Join example in the previous subsection can be expressed with an IN Subquery:

SELECT DISTINCT pno FROM sp a WHERE pno IN (SELECT pno FROM sp b WHERE a.sno <> b.sno)

pno |
---|

P1 |

Note that the subquery where clause references a column in the outer query
(*a.sno*). This is known as an *outer reference*.
Subqueries with outer references are sometimes known as
*correlated subqueries*.

A quantified subquery allows several types of tests and can use the full set of comparison operators. It has the following general format:

The comparison operator specifies how to comparevalue-1 {=|>|<|>=|<=|<>} {ANY|ALL|SOME} (query-1)

For example, to list all parts that have suppliers:

SELECT * FROM p WHERE pno =ANY (SELECT pno FROM sp)

pno | descr | color |
---|---|---|

P1 | Widget | Blue |

P2 | Widget | Red |

A self join is used to list the supplier with the highest quantity of each
part (ignoring *null* quantities):

SELECT * FROM sp a WHERE qty >ALL (SELECT qty FROM sp b WHERE a.pno = b.pno AND a.sno <> b.sno AND qty IS NOT NULL)

sno | pno | qty |
---|---|---|

S3 | P1 | 1000 |

S3 | P2 | 200 |

The EXISTS Subquery tests whether a subquery retrieves at least one row,
that is, whether a qualifying row *exists*.
It has the following general format

Any valid EXISTS subquery must contain anEXISTS(query-1)

Note: the select list in the EXISTS subquery is not actually used in
evaluating the EXISTS, so it can contain any valid select list
(though ***** is normally used).

To list parts that have suppliers:

SELECT * FROM p WHERE EXISTS(SELECT * FROM sp WHERE p.pno = sp.pno)

pno | descr | color |
---|---|---|

P1 | Widget | Blue |

P2 | Widget | Red |

When the subquery returns a single row, the value of the single select list
column becomes the value of the Scalar Subquery.
When the subquery returns no rows, a database *null* is used as the
result of the subquery.
Should the subquery retreive more than one row, it is a *run-time* error
and aborts query execution.

A Scalar Subquery can appear as a scalar value in the select list and where
predicate of an another query.
The following query on the *sp* table uses a Scalar Subquery in the
select list to retrieve the supplier city associated with the supplier
number (*sno* column in *sp*):

SELECT pno, qty, (SELECT city FROM s WHERE s.sno = sp.sno) FROM sp

pno | qty | city |
---|---|---|

P1 | NULL | Paris |

P1 | 200 | London |

P1 | 1000 | Rome |

P2 | 200 | Rome |

SELECT * FROM sp WHERE 'Blue' = (SELECT color FROM p WHERE p.pno = sp.pno)

sno | pno | qty |
---|---|---|

S1 | P1 | NULL |

S2 | P1 | 200 |

S3 | P1 | 1000 |

The following two queries produce the same result:

SELECT p.*, qty FROM p, sp WHERE p.pno = sp.pno AND sno = 'S3'

pno | descr | color | qty |
---|---|---|---|

P1 | Widget | Blue | 1000 |

P2 | Widget | Red | 200 |

SELECT p.*, qty FROM p, (SELECT pno, qty FROM sp WHERE sno = 'S3') WHERE p.pno = sp.pno

pno | descr | color | qty |
---|---|---|---|

P1 | Widget | Blue | 1000 |

P2 | Widget | Red | 200 |

A Grouping Query groups rows based on common values in a set of grouping
columns.
Rows with the same values for the grouping columns are placed in distinct
groups.
Each *group* is treated as a single row in the query result.

Even though a *group* is treated as a single row,
the underlying rows can be subject to summary operations known as
Set Functions whose results can be included in the query.
The optional HAVING Clause supports filtering for group
rows in the same manner as the WHERE clause filters FROM rows.

For example, grouping the *sp* table on the *pno* column
produces 2 groups:

sno | pno | qty | |
---|---|---|---|

S1 | P1 | NULL | 'P1' Group |

S2 | P1 | 200 | |

S3 | P1 | 1000 | |

S3 | P2 | 200 | 'P2' Group |

- The
*P1*group contains 3*sp*rows with*pno='P1'* - The
*P2*group contains a single*sp*row with*pno='P2'*

Grouping the *sp* table on the *qty* column produces 3 groups:

sno | pno | qty | |
---|---|---|---|

S1 | P1 | NULL | NULL Group |

S2 | P1 | 200 | 200 Group |

S3 | P2 | 200 | |

S3 | P1 | 1000 | 1000 Group |

GROUP BY column-1 [, column-2] ...

GROUP BY operates on the rows from the FROM clause as filtered by the WHERE
clause.
It collects the rows into groups based on common values in the grouping
columns.
Except *nulls*, rows with the same set of values for the grouping
columns are placed in the same group. If any grouping column for a row
contains a *null*, the row is given its own group.

For example,

SELECT pno FROM sp GROUP BY pno

pno |
---|

P1 |

P2 |

Using the Group By example from above, grouping the *sp* table on the
*pno* column:

sno | pno | qty | |
---|---|---|---|

S1 | P1 | NULL | 'P1' Group |

S2 | P1 | 200 | |

S3 | P1 | 1000 | |

S3 | P2 | 200 | 'P2' Group |

sno | pno | qty | qty total | |
---|---|---|---|---|

S1 | P1 | NULL | 'P1' Group | 1200 |

S2 | P1 | 200 | ||

S3 | P1 | 1000 | ||

S3 | P2 | 200 | 'P2' Group | 200 |

SELECT pno, SUM(qty) FROM sp GROUP BY pno

pno | |
---|---|

P1 | 1200 |

P2 | 200 |

set-function ( [DISTINCT|ALL] column-1 )

- COUNT -- count of rows
- SUM -- arithmetic sum of numeric column
- AVG -- arithmetic average of numeric column; should be SUM()/COUNT().
- MIN -- minimum value found in column
- MAX -- maximum value found in column

The Set Functions skip columns with *nulls*, summarizing *non-null*
values.
COUNT counts rows with non-null values, AVG averages non-null values, and
so on.
COUNT returns 0 when no non-null column values are found; the other functions
return *null* when there are no values to summarize.

A Set Function argument can be a column or an scalar expression.

The DISTINCT and ALL specifiers are optional.
ALL specifies that *all* non-null values are summarized;
it is the default.
DISTINCT specifies that *distinct* column values are summarized;
duplicate values are skipped.
Note: DISTINCT has no effect on MIN and MAX results.

COUNT also has an alternate format:

... which counts the underlying rows regardless of column contents.COUNT(*)

Set Function examples:

SELECT pno, MIN(sno), MAX(qty), AVG(qty), COUNT(DISTINCT sno) FROM sp GROUP BY pno

pno | ||||
---|---|---|---|---|

P1 | S1 | 1000 | 600 | 3 |

P2 | S3 | 200 | 200 | 1 |

SELECT sno, COUNT(*) parts FROM sp GROUP BY sno

sno | parts |
---|---|

S1 | 1 |

S2 | 1 |

S3 | 2 |

The HAVING Clause has the following general format:

Like the WHERE Clause, HAVING filters the query result rows. WHERE filters the rows from the FROM clause. HAVING filters theHAVING predicate

*predicate* is a logical expression referencing grouped columns and
set functions.
It has the same restrictions as the select list for
Grouping Queries and Aggregate Queries.

If the Having predicate evaluates to true for a grouped or aggregate row, the row is included in the query result, otherwise, the row is skipped (not included in the query result).

For example,

SELECT sno, COUNT(*) parts FROM sp GROUP BY sno HAVING COUNT(*) > 1

sno | parts |
---|---|

S3 | 2 |

An Aggregate Query is a query containing Set Functions in the select list but no GROUP BY clause. The Set Functions operate on the columns of the underlying rows of the single aggregate row. Except for outer references, any columns used in the select list must be arguments to Set Functions. See Set Functions above.

An aggregate query may also have a Having clause. The Having clause filters the single aggregate row. If the Having predicate evaluates to true, the query result contains the aggregate row. Otherwise, the query result contains no rows. See HAVING Clause above.

For example,

SELECT COUNT(DISTINCT pno) number_parts, SUM(qty) total_parts FROM sp

number_parts | total_parts |
---|---|

2 | 1400 |

SELECT * FROM p WHERE (SELECT COUNT(*) FROM sp WHERE sp.pno=p.pno) > 0

pno | descr | color |
---|---|---|

P1 | Widget | Blue |

P2 | Widget | Red |

SELECT * FROM p WHERE (SELECT COUNT(DISTINCT sno) FROM sp WHERE sp.pno=p.pno) > 1

pno | descr | color |
---|---|---|

P1 | Widget | Blue |

query-1 UNION [ALL] query-2

By default, UNION eliminates duplicate rows in its composite results. The optional ALL specifier requests that duplicates be retained in the UNION result.

The component queries of a Union Query can also be Union Queries themselves. Parentheses are used for grouping queries.

The select lists from the component queries must be
*union-compatible*.
They must match in degree (number of columns). For Entry Level SQL92,
the column descriptor (data type and precision, scale) for each
corresponding column must match.
The rules for Intermediate Level SQL92 are less restrictive.
See Union-Compatible Queries.

- Character (String) -- fixed/variable length
- Bit String -- fixed/variable length
- Exact Numeric (fixed point) -- integer/decimal
- Approximate Numeric (floating point) -- float/double
- Datetime -- sub-category must be the same,
- Date
- Time
- Timestamp

- Interval -- sub-category must be the same,
- Year-month
- Day-time

SELECT * FROM sp UNION SELECT CAST(' ' AS VARCHAR(5)), pno, CAST(0 AS INT) FROM p WHERE pno NOT IN (SELECT pno FROM sp)

sno | pno | qty |
---|---|---|

S1 | P1 | NULL |

S2 | P1 | 200 |

S3 | P1 | 1000 |

S3 | P2 | 200 |

P3 | 0 |

- INSERT Statement -- add rows to tables
- UPDATE Statement -- modify columns in table rows
- DELETE Statement -- remove rows from tables

SQL-Data Statements | SQL Tutorial Main Page |