- 20 -
The basic functions of SQL are described in the following sections. Use these sections as a refresher
course; they are not meant to teach SQL syntax or provide in-depth coverage of its use.
Selection and Projection
The selection operation retrieves a specified subset of rows from a DB2 table. You use predicates in a
WHERE clause to specify the search criteria. The SQL implementation for selection is shown in the following
example:
SELECT *
FROM DSN8610.PROJ
WHERE DEPTNO = 'D01';
To retrieve all rows from the PROJ table, simply eliminate the WHERE clause from the statement.
The projection operation retrieves a specified subset of columns from a given DB2 table. A DB2 query
can provide a list of column names to limit the columns that are retrieved. Projection retrieves all of the
rows but only the specified columns. The following statement illustrates the SQL implementation for
projection:
SELECT DEPTNO, PROJNO, PROJNAME
FROM DSN8610.PROJ;
Simply, the selection operation determines which rows are retrieved, and the projection operation
determines which columns are retrieved.
The SQL SELECT statement is used to implement both the selection and projection operations. In most
cases, queries combine selection and projection to retrieve data. The following SQL statement
combines the selection and projection operations of the preceding two examples:
SELECT DEPTNO, PROJNO, PROJNAME
FROM DSN8610.PROJ
WHERE DEPTNO = 'D01';
Joins and Subqueries
The capability to query data from multiple tables using a single SQL statement is one of the nicer features of
DB2. The more tables involved in a SELECT statement, however, the more complex the SQL. Complex SQL
statements sometimes cause confusion. Therefore, a basic understanding of the multiple table capabilities of
SQL is essential for all users.
Joining Tables
The capability of DB2 to combine data from multiple tables is called joining. A standard join, also referred to
as an inner join, matches the data from two or more tables, based on the values of one or more columns in
each table. All matches are combined, creating a resulting row that is the concatenation of the columns from
each table where the specified columns match.
The most common method of joining DB2 tables requires SQL SELECT statements to have the
following:
A string of table names separated by commas in the FROM clause
A WHERE clause comparing the value of a column in one of the joined tables to the
value of a column in the other joined table (this is usually an equality comparison)
For example, to query employees and their department names, the EMP table is joined to the DEPT
table as follows:
SELECT EMPNO, LASTNAME, DEPTNO, DEPTNAME
FROM DSN8610.EMP,
DSN8610.DEPT
WHERE WORKDEPT = DEPTNO;
This method of coding joins, however, has confused many novice SQL programmers. No join verb need
be coded explicitly in the SQL SELECT statement to implement table joining. A join can be specified by
the presence of more than one table in the FROM clause of the SELECT statement. It is sometimes
difficult to grasp the concept of joining tables without a specific JOIN keyword being used in the SQL
join statement.
我的内容管理
展开
