The following clause list shows the logical order of clauses in a statement:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
Subselects are processed from the innermost to the outermost subselect. A
subselect in a WHERE clause or a HAVING clause of another SQL statement is
called a subquery.
If you use an AS clause to define a name in the outermost SELECT clause,
the ORDER BY clause can refer to that name. If you use an AS clause in a
subselect, you can refer to the name that it defines outside the subselect.
An aggregate function is an operation that derives its result by using values from
one or more rows. An aggregate function is also known as a column function. The
argument of an aggregate function is a set of values that are derived from an
expression.
A search condition consists of one or more predicates that are combined through the
use of the logical operators AND, OR, and NOT.
An individual predicate specifies
a test that you want DB2 to apply to each row,
The percent sign (%) means “any string or no string.”
The underscore (_) means “any single character.”
Use GROUP BY to group rows by the values of one or more columns. You can
then apply aggregate functions to each group. You can use an expression in the
GROUP BY clause to specify how to group the rows.
Except for the columns that are named in the GROUP BY clause, the SELECT
statement must specify any other selected columns as an operand of one of the
aggregate functions.
Use HAVING to specify a search condition that each retrieved group must satisfy.
The HAVING clause acts like a WHERE clause for groups, and it can contain the
same kind of search conditions that you can specify in a WHERE clause. The
search condition in the HAVING clause tests properties of each group rather than
properties of individual rows in the group.
Ways to join data from more than one table
Ways to join data from more than one table
A joined-table specifies an intermediate result table that is the result of either an
inner join or an outer join. The table is derived by applying one of the join
operators—INNER, FULL OUTER, LEFT OUTER, or RIGHT OUTER—to its
operands.
DB2 supports inner joins and outer joins (left, right, and full).
Inner join
Combines each row of the left table with each row of the right
keeping only the rows in which the join condition is true.
Outer join
Includes the rows that are produced by the inner join, plus the missing
rows, depending on the type of outer join:
Left outer join
Includes the rows from the left table that were missing from the
inner join.
Right outer join
Includes the rows from the right table that were missing from the
inner join.
Full outer join
Includes the rows from both tables that were missing from the
inner join
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
FROM PARTS, PRODUCTS
WHERE PARTS.PROD# = PRODUCTS.PROD#;
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
FROM PARTS INNER JOIN PRODUCTS
ON PARTS.PROD# = PRODUCTS.PROD#;
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
FROM PARTS INNER JOIN PRODUCTS
ON 1=1;
The number of rows in the result table is the product of the number of rows in
each table:
