SELECT

The SELECT statement specifies the columns of the final result table. You can issue SELECT directly from the Run SQL Scripts window. By default, the result table is another tab on the Run SQL Scripts window, but you can launch the result set as a separate window by changing your Display Results from the Options menu.

You can use SQL Assist to build your SELECT statement. See SQL Assist for more information.

You must specify a FROM table when issuing a SELECT statement.

Examples:

To select all rows in a table:

SELECT *
  FROM CORPDATA.EMPLOYEE

To select specific rows from a single table:

SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME 
  FROM CORPDATA.EMPLOYEE

To select specific rows from multiple tables:

SELECT EMPNO, LASTNAME, PROJNO
  FROM CORPDATA.EMPLOYEE, CORPDATA.PROJECT
  WHERE EMPLOYEE.EMPNO = PROJECT.RESEMP

There are several clauses that you can use with SELECT. Some of the most common are:

Use WHERE to select data:

The WHERE clause specifies a search condition that is used for selecting the desired rows. Conditions allow you to make your queries more selective: rather than returning all of the rows in your table, you can select only the ones that you want. Conditions usually contain a constant, a variable, and an operator between. The WHERE clause does not work with aggregate functions; you should use the HAVING clause for these cases.

Example: WHERE

Find all of the employees that are managers.

SELECT EMPNO, LASTNAME, FIRSTNME, WORKDEPT 
  FROM CORPDATA.EMPLOYEE
  WHERE JOB = 'MANAGER'  

Use GROUP BY to summarize data:

When you specify a GROUP BY clause, SQL divides the selected rows into groups such that the rows of each group have matching values in one or more columns or expressions. Next, SQL processes each group to produce a single-row result for the group. You can specify one or more columns or expressions in the GROUP BY clause to group the rows. The items you specify in the SELECT statement are properties of each group of rows, not properties of individual rows in a table or view.

Example: GROUP BY

The CORPDATA.EMPLOYEE table has several sets of rows, and each set consists of rows describing members of a specific department. To find the average salary of people in each department, you could issue:

SELECT WORKDEPT, DECIMAL (AVG(SALARY),5,0)
  FROM CORPDATA.EMPLOYEE
  GROUP BY WORKDEPT

Use HAVING to select summarized data:

You can use the HAVING clause to specify a search condition for the groups selected based on a GROUP BY clause. The HAVING clause says that you want only those groups that satisfy the condition in that clause. Therefore, the search condition you specify in the HAVING clause must test properties of each group rather than properties of individual rows in the group.

The HAVING clause follows the GROUP BY clause and can contain a search condition similar to the one that you can specify in a WHERE clause. In addition, you can specify column functions in a HAVING clause.

Example: HAVING

Retrieve the average salary of women in each department. To do this, you would use the AVG column function and group the resulting rows by WORKDEPT and specify a WHERE clause of SEX = 'F'.

To specify that you want this data only when all the female employees in the selected department have an education level equal to or greater than 16 (a college graduate), use the HAVING clause. The HAVING clause tests a property of the group. In this case, the test is on MIN(EDLEVEL), which is a group property:

SELECT WORKDEPT, DECIMAL(AVG(SALARY),5,0), MIN(EDLEVEL)
  FROM CORPDATA.EMPLOYEE
  WHERE SEX='F'
  GROUP BY WORKDEPT
  HAVING MIN(EDLEVEL)>=16

Use ORDER BY to order data:

The ORDER BY clause orders the data returned, sorting by ascending or descending collating sequence of a column's or expression's value.

Example: ORDER BY

Find all of the employees that are managers and order them alphabetically by last name.

SELECT EMPNO, LASTNAME, FIRSTNME, WORKDEPT 
  FROM CORPDATA.EMPLOYEE
  WHERE JOB = 'MANAGER'  
  ORDER BY LASTNAME

Join data

You can join data from more than one table by using the JOIN clause. The types of joins you can perform are:

INNER JOIN - Returns only the rows from each table that have matching values in the join columns.
LEFT OUTER JOIN - Returns values for all of the rows from the left table and the values from the right table for the rows that match.
RIGHT OUTER JOIN - Returns values for all of the rows from the right table and the values from the left table for the rows that match.
EXCEPTION JOIN - Returns only the rows from the left table that do not have a match in the right table.
RIGHT EXCEPTION JOIN - Returns only the rows from the right table that do not have a match in the left table.
CROSS JOIN - Returns a row in the result table for each combination of rows from the tables being joined (a Cartesian Product).

Example: Joins

Return the EMPNO, LASTNAME, and PROJNO of all those employees who are responsible for a project (RESPEMP).

SELECT EMPNO, LASTNAME, PROJNO
  FROM CORPDATA.EMPLOYEE INNER JOIN CORPDATA.PROJECT
    ON EMPNO = RESPEMP

UNION

You can combine two or more subselects to form a single select-statement using the UNION keyword.  When UNION is included,  SQL processes each subselect to form a temporary table, then it combines the tables and deletes duplicate rows to form a single result table.

Example: UNION

Return a list of employee numbers that includes people in department D11 and people whose assignments include project MA2112, MA2113, and AD3111.

SELECT EMPNO
  FROM CORPDATA.EMPLOYEE
  WHERE WORKDEPT = 'D11'
UNION
SELECT EMPNO
  FROM CORPDATA.EMP_ACT
  WHERE PROJNO = 'MA2112' OR
        PROJNO = 'MA2113' OR
        PROJNO = 'AD3111'
ORDER BY EMPNO

For more information about using SELECT, see the Queries in the SQL Reference or the Retrieving data using the SELECT statement in the SQL Programming topic in the Information CenterLink to Information center.