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:
1 2 3 4 | SELECT select_list FROM table_name; |
In this syntax:
- First, specify a list of comma-separated columns or expressions in the
SELECTclause. - Then, specify the table from which you want to query data in the
FROMclause.
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:
1 2 3 4 | SELECT title FROM books; |
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:
1 2 3 4 5 | 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:
1 2 3 4 5 6 7 8 9 10 | 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:
1 2 3 4 | 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:
1 2 3 4 | 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:
1 2 3 4 | 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.