How to query a single DB2 table

Below syntax shows how to use a SELECT statement to get data from a single DB2 table.

SELECT column-list
FROM table-name
[WHERE selection-condition]
[ORDER BY sort-column [DESC]  [, sort-column [DESC]] ...]

Example is given below to retrieve and sort selected columns and rows from an INVOICE table :

SELECT INVNO, INVDATE, INVTOTAL
FROM MM01.INVOICE
WHERE INVTOTAL > 100
ORDER BY INVDATE DESC

Here query will select rows from INVOICE table only if it has INVTOTAL greater than 100. And it sorts the returned rows by the invoice date in descending sequence.

We can also have a query like below :

SELECT INVNO, INVDATE, INVTOTAL,
       INVTOTAL - PAYTOTAL - CRTOTAL AS BALANCE 
FROM MM01.INVOICE
WHERE INVTOTAL - PAYTOTAL - CRTOTAL > 0
ORDER BY BALANCE DESC

Please note that in this SELECT statement that the last column in the query is BALANCE which is calculated by subtracting the payment total(PAYTOTAL) and the credit total (CRTOTAL) from the invoice total(INVTOTAL). That is, BALANCE doesn't actually exist as column in INVOICE table. This column is called calculated column and only exist in the results of the query.

Inorder to select all the columns from the table, we can have below query:

SELECT * FROM MM01.INVOICE

But for performance stuffs, we should only select the columns that we need for our requirements.