Db2 SELECT

Summary: in this tutorial, you will learn how to query data from one or more column of a table by using the Db2 SELECT statement.

Introduction to Db2 SELECT statement

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;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify a list of comma-separated columns or expressions in the SELECT clause.
• Then, specify the table from which you want to query data in the FROM clause.

When evaluating the SELECT statement, Db2 evaluates the FROM clause first and then the SELECT clause:

Db2 SELECT statement examples

Let’s take the books table from the sample database for the demonstration.

1) Using Db2 SELECT statement to query data from one column example

The following statement returns titles of all rows in the books table:

SELECT 
    title
FROM 
    books;
Code language: SQL (Structured Query Language) (sql)

Here is the partial result set:

In this statement, we specified the books table in the FROM clause and the title column in the SELECT clause.

2) Using Db2 SELECT statement to query data from multiple columns example

This example returns the title and ISBN of all books from the books table:

SELECT 
    title, 
    isbn
FROM 
    books;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

In this example, the select list has two columns: title and ISBN, therefore, we use a comma as a separator between them.

3) Using Db2 SELECT statement to query data from all columns of a table example

To query data from all columns of a table, you list all columns in the SELECT clause:

SELECT 
    book_id, 
    title, 
    total_pages, 
    rating, 
    isbn, 
    published_date, 
    publisher_id
FROM 
    books;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

Another way to query data from all columns of a table is to use an asterisk (*) shorthand as shown in the following query:

SELECT 
  * 
FROM 
    books;
Code language: SQL (Structured Query Language) (sql)

The (*) is a shorthand for all columns.

4) Using SELECT statement without referencing a table

If you want to call a function or evaluate an expression using the SELECT statement, you can use the sysibm.sysdummy1 table:

SELECT 
   expression
FROM 
   sysibm.sysdummy1;Code language: SQL (Structured Query Language) (sql)

The sysibm.sysdummy1 is a special in-memory table which you can use to evaluate expressions or discover Db2 registers.

For example, this statement returns the current date:

SELECT 
   CURRENT_DATE 
FROM 
   sysibm.sysdummy1;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 SELECT statement to query data from a single table.