DB2 - SELECT Statement




DB2 SELECT Statement

The SELECT statement is used to query the database and retrieve selected data that match the criteria that you specify. The SELECT statement has five main clauses to choose from, although, FROM is the only required clause. Each of the clauses have a vast selection of options, parameters, etc. The clauses will be listed below, but each of them will be covered in more detail later in the tutorial.

Here is the format of the SELECT statement -

 SELECT [ALL | DISTINCT] column1[,column2] FROM table1[,table2] [WHERE "conditions"] [GROUP BY "column-list"] [HAVING "conditions] [ORDER BY "column-list" [ASC | DESC] ]

Example

SELECT name, age, salary
 
FROM employee
 
WHERE age > 40;

The language used by DB2 to access data consists of various SQL statements for manipulating or defining data in the database, including the UPDATE or SELECT statement. The article provides information on how to perform a DB2 SQL UPDATE and SELECT statement with examples.

The SELECT statement queries data from one or more tables in a database. It is one of the most complex SQL statements in Db2. In this tutorial, we will focus on using the SELECT statement to query data from a single table. Here is the simplest form of the SELECT statement -

SELECT 
    select_list
FROM
    table_name;

What is DB2 SELECT Statement ?

The DB2 SQL SELECT follows the standard SQL SELECT clause, where we specify columns from the table. The SELECT clause can also be followed by the FROM or WHERE clause. The following is the DB2 SQL syntax when using the SELECT statement to retrieve data from the student table, along with certain conditions.

SELECT name
FROM student
WHERE studentID <=3;
name
========
Adrian A
Brad B
Chad C

The result is as follows, To get the list of values of students and score, we can use the SELECT JOIN clause as follows -

SELECT A.studentID, A.name, B.score
FROM student A
LEFT JOIN exam B ON A.studentID=B.studentID;

Select all the rows from DSN8B10.EMP.

SELECT * FROM DSN8B10.EMP;

Select all the rows from DSN8B10.EMP, arranging the result table in chronological order by date of hiring.

SELECT * FROM DSN8B10.EMP
      ORDER BY HIREDATE;

Select the department number (WORKDEPT) and average departmental salary (SALARY) for all departments in the table DSN8B10.EMP. Arrange the result table in ascending order by average departmental salary.

SELECT WORKDEPT, AVG(SALARY)
     FROM DSN8B10.EMP
     GROUP BY WORKDEPT
     ORDER BY 2;

Change various salaries, bonuses, and commissions in the table DSN8B10.EMP. Confine the changes to employees in departments D11 and D21. Use positioned updates to do this with a cursor named UP_CUR. Use a FOR UPDATE clause in the cursor declaration to indicate that all updatable columns are updated. Below is the declaration for a PL/I program.

EXEC SQL DECLARE UP_CUR CURSOR FOR
     SELECT WORKDEPT, EMPNO, SALARY, BONUS, COMM
       FROM DSN8B10.EMP
       WHERE WORKDEPT IN ('D11','D21')
       FOR UPDATE;

Beginning where the cursor is declared, all updatable columns would be updated. If only specific columns needed to be updated, such as only the salary column, the FOR UPDATE clause could be used to specify the salary column (FOR UPDATE OF SALARY).

Let's get that straight from the beginning: select without from is not standard conforming SQL. Full stop. Nevertheless it works in many databases—also in standard conforming ones. That's no contradiction: the standard explicitly allows conforming databases to “provide user options to process non-conforming SQL statements”.0 The behavior of such statements is completely up to the vendor, of course.

So what alternative does the standard offer to select without from? A surprisingly simple and yet powerful one: values without insert. The following select statement can thus be implemented as a standard-conforming values without insert:

Instead of a non-conforming select without from -

SELECT CURRENT_DATE

the standard allows the use of values without insert -

VALUES (CURRENT_DATE)

Too bad the stand-alone use of values is still not part of Core SQL. Consequently, only three out of the six tested databases support it. select without from, on the other hand, works on four of them.

By now you might wonder why stand-alone values might be useful at all. As I implied above, it is more powerful than select without from because it is not limited to produce a single row.

The following values statement returns today’s and yesterday's dates (use-case) in two rows—not two columns -

VALUES (CURRENT_DATE)
     , (CURRENT_DATE - INTERVAL '1' DAY)

With select without from, you'd need to use union. That can quickly become bulky.