DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 SELECT

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;

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

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;

Here is the partial result set:

Db2 Select single column

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;

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;

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;

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;

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;

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

  • Was this tutorial helpful ?
  • YesNo
Next Db2 ORDER BY

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.