Select Statements

Part:   1   2  3  4 

This chapter helps you understand:

• Select Statements
• FROM Clause
• Join Tables
• JointTable.sql - Example of Join Tables
• WHERE Clause
• GROUP BY Clause
• ORDER BY Clause

Select Statements

A select statement is also called a query statement. It is normally used to retrieve rows of data selected from specified tables. The generic syntax of a select statement is:

SELECT select_expression_list [FROM clause]

where "expression_list" defines a list of select expressions, and "FROM clause" defines a select table with rows and columns of values. Column names of the select table can be used as variables in select expressions to represent column values in each row.

When a select statement is executed, a nested loop logic will be performed to generate rows of output data:

Loop on each row of the select table, do:
   Loop on each select expression, do:
      Evaluate this expression with possible column values in current 
         row of the select table.
   End of loop
   Return the results of all select expressions as a row of output data
End of loop

Note:

• The number of columns of an output row equals to the number of select expressions.
• The number of rows of the output data equals to the number of rows of the select table.
• If the select table has no rows, no data will be returned.
• The select table is optional. If it is not specified, only one row of data will be returned.

Sample select statements without FROM clause:

SELECT 'Hello world!';
SELECT 'Apple', 'Orange';
SELECT CHAR_LENGTH('Hello world!');
SELECT 1, 4, 9, 16, 25;
SELECT PI();
SELECT CURRENT_DATE(), CURRENT_TIME();

FROM Clause

The goal of the FROM clause is to define a table of rows and columns of data. Its generic syntax is:

FROM table_reference [WHERE clause] [GROUP BY clause] [ORDER BY clause]

where:

• "table_reference" refers to a real table, a query table, or a join table of two real, query or join tables. It defines a base table of rows and columns of data. This base table will be returned as the final select table as is, or modified by the WHERE clause, GROUP BY clause, and/or GROUP BY clause.
• "WHERE clause" modifies the base table by filtering out rows of data that do not satisfy the specified conditions.
• "GROUP BY clause" modifies the base table by grouping original rows into group rows based on the specified group columns. Original columns are also reduced to the specified group columns only. Group rows can also be filtered out by specified conditions.
• "ORDER BY clause" modifies the base table by sorting rows according the specified order.

The syntax for "table_reference" has different forms depending how the logical table is defined.

1. A real table - the base table is the specified real table.

FROM table_name

2. A query table - the base table is the output table of a select statement:

FROM (SELECT statement)

3. A join table - the base table is the output table of a join operation of two real, query or join tables.

FROM table_reference join_operation table_reference [join_condition]

where:

• "table_reference" refers to a real table, a query table, or a join table of two real, query or join tables.
• "join_operation" defines the type of join operation to be performed.
• "join_condition" defines an optional join condition.

Sample select statements without FROM clause:

SELECT ID, Login, Email FROM User;
SELECT Price, Quantity, ROUND(Price*Quantity) FROM Sales;

(Continued on next part...)

Part:   1   2  3  4