SQL BASIC

DB2 SQL 103 DML : SELECT


SQL SELECT Statement is a form of SQL QUERY which gives access to a result set(Temporary table). We can use SQL SELECT to look inside table or create a new table based on the result set of query.

There are 3 components of SQL SELECT:

  1. SubSelect
  2. FullSelect
  3. Select-Statement

 

SubSelect

SubSelect is most basic form of SQL SELECT. Just simple select statement with minimum options.Here is how IBM describes SubSelect:
>>-select-clause--from-clause--+--------------+----------------->
                               '-where-clause-'   

>--+-----------------+--+---------------+----------------------->
   '-group-by-clause-'  '-having-clause-'   

>--+-----------------+--+--------------------+------------------>
   '-order-by-clause-'  '-fetch-first-clause-'   

>--+------------------+----------------------------------------><
   '-isolation-clause-'

 

This is a how a SubSelect looks like:

select [all|distinct] expression {, expression}
from tablename [corr_name] {, tablename [corr_name]}
[where search_condition]
[group by column {, column}]
[having search_condition]

FullSelect

  1. FullSelect =  SubSelect + SET OPERATION(If a set operator is not used, the result of the Fullselect is the result of the specified Subselect.)
    1. UNION
    2. EXCEPT
    3. INTERSECT
  2. A FullSelect does not allow any of the following clauses:
    1. FOR READ ONLY
    2. FOR FETCH ONLY
    3. FOR UPDATE OF
    4. OPTIMIZE FOR
    5. WITH (for CTE)
    6. QUERYNO

This is how a FullSelect looks like:

Subselect
{union [all] Subselect}
[order by result_column [asc|desc] 
          {, result_column [asc|desc]}]

Select-Statement

Select-Statement is highest level. It is like
” FullSelect +Every_Thing_Else_Which_is_Not_ALLOWED_in_FullSelect “.

Here is IBM’s Dot&Hyphen again:


>>-+-----------------------------------+--fullselect--●--------->
   |       .-,-----------------------. |                  
   |       V                         | |                  
   '-WITH----common-table-expression-+-'                  

>--+------------------+--●--+---------------------+--●---------->
   +-read-only-clause-+     '-optimize-for-clause-'      
   '-update-clause----'                                  

>--+------------------+--●-------------------------------------->
   '-isolation-clause-'      

>--+-------------------------------------+--●------------------><
   '-concurrent-access-resolution-clause-'    

 

Here is how we can look into SQL SELECT:
select


IBM i developer.

View Comments
There are currently no comments.