SELECT

Edit online

The SELECT statement made up a series of clauses that are defined by SQL as being executed in a logical order. SELECT statements allow users to definite and organize information that is retrieved from a specified table.


Select Statement
Note:
  • The read-only-clause must not be specified if an update-clause is specified.
  • The same clause must not be specified more than one time.

The tables and the view identified in a select statement can be at the current server or any subsystem with which the current server can establish a connection.

For local queries or remote queries, if a table is encoded as ASCII or Unicode, the retrieved data is encoded in EBCDIC.

A select statement can implicitly or explicitly invoke user-defined functions or implicitly invoke stored procedures. This technique is known as nesting of SQL statements. A function or procedure is implicitly invoked in a select statement when it is invoked at a lower level. For instance, if you invoke a user-defined function from a select statement and the user-defined function invokes a stored procedure, you are implicitly invoking the stored procedure.

  • common-table-expression

    A common table expression defines a result table with table-identifier that can be referenced in any FROM clause of the fullselect that follows.

  • read-only-clause

    The read-only clause specifies that the result table is read-only. Therefore, the cursor cannot be referred to in positioned UPDATE or DELETE statements.

Example

SELECT - All Rows

The following example selects all rows from the table EMPLOYEE.


SELECT * FROM EMPLOYEE;

Result

Table 1. SELECT ALL
EMPL_NAME AGE DEPT INDUSTRY SALARY DATEOFJOIN
BUMRAH 53 SALES RETAIL 50000 2019-11-25
CEASAR 43 MARKETING RETAIL 20000
ELICA 23 CUST SUPR RETAIL 5000 2019-10-10
DEV 33 ADMIN RETAIL 25000 2019-11-27
NEWTON 53 DATA RETAIL 75000 2019-11-25
SAMUEL 43 IT BANKING 75000 2019-11-25
ELICA

SELECT - Limited Columns and Row

The following example selects only the columns EMPL_NAME, AGE, and limits the records to 3.


SELECT EMPL_NAME, AGE
FROM EMPLOYEE LIMIT 3;

Result

Table 2. SELECT - Limited Columns and Row
EMPL_NAME AGE
BUMRAH 53
CEASAR 43
ELICA 23

SELECT - Condition

The following example applies the condition of selecting only the record where the EMPL_NAME has LOCATION = 'TX' in the table EMPL_COMP.


SELECT EMPL_NAME, AGE, DEPT, INDUSTRY, SALARY, DATEOFJOIN
FROM EMPLOYEE 
WHERE EMPL_NAME = (SELECT EMPL_NAME FROM EMPL_COMP WHERE LOCATION = 'TX');

Result

Table 3. SELECT - Condition
EMPL_NAME AGE DEPT INDUSTRY SALARY DATEOFJOIN
ELICA 23 CUST SUPR RETAIL 5000 2019-10-10
ELICA

Sub Select

The following example executes the sub select function first and selects EMPL_NAME, AGE, and INDUSTRY columns from the table EMPLOYEE and limits the record to 10. And then the outer SELECT function selects only the EMPL_NAME column from the sub select result and limits to 5 records.


SELECT EMPL_NAME FROM (SELECT EMPL_NAME, AGE, INDUSTRY, FROM EMPLOYEE LIMIT 10) LIMIT 5

Result

Table 4. SUB SELECT
EMPL_NAME
BUMRAH
CEASAR
ELICA
DEV
NEWTON
SAMUEL