Differences: DB2 SELECT Vs. Sub-SELECT Vs. Full SELECT

SELECT -The select-statement is the form of a query that can be directly specified in a DECLARE CURSOR statement or FOR statement, prepared and then referenced in a DECLARE CURSOR statement, or directly specified in an SQLJ assignment clause. It can also be issued using SPUFI or the command line processor which causes a result table to be displayed at your terminal. In any case, the result table specified by a select-statement is the result of the fullselect.

Example:

SELECT * FROM DSN8A10.EMP;

FULLSELECT – The fullselect is a component of the select-statement, ALTER TABLE statement for the definition of a materialized query table, CREATE TABLE statement, CREATE VIEW statement, DECLARE GLOBAL TEMPORARY TABLE statement, and INSERT statement.

Example:

SELECT EMPNO
FROM EMPLOYEE
WHERE WORKDEPT LIKE ‘E%’
UNION
SELECT EMPNO
FROM EMP_ACT
WHERE PROJNO IN(‘MA2100’, ‘MA2110’, ‘MA2112’)

SUBSELECT – The subselect is a component of the fullselect. A subselect specifies a result table that is derived from the tables or views that are identified in the FROM clause.

Example:

SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
FROM EMPLOYEE E LEFT OUTER JOIN
DEPARTMENT INNER JOIN EMPLOYEE M
ON MGRNO = M.EMPNO
ON E.WORKDEPT = DEPTNO

Ref:IBM