Wednesday, April 8, 2009

Anatomy of a "Select" Statement

One of the most common DML statements that is used in SQL world is a "Select - From - Where" statements. These statements consists of three different parts - Select Clause, From Clause and Where Clause. Every Select statement has two mandatory parts - Select and From clause and the Where Clause is optional. The Select statement returns a table as a result in the form of Resultset.

Select clause:
SELECT clause is the set of instructions to the SQL Engine pertaining to the Projection operation. The atoms specified here are projected in the resultset. Select clause consists of the Select keyword which marks the start of the Select Clause. The Select keyword can then be followed by DISTINCT or ALL keyword (these keywords are optional and if DISTINCT or ALL is not specified, default ALL is assumed by the SQL engine). DISTINCT specifies that no duplicate row should be returned whereas ALL specifies that even duplicate rows should be returned. After this is the list of selectable atoms and we call it as the "select-list". Any common SQL Expression can be used in place of a selectable atom. For eg., Common SQL expression types are
1. column reference
2. An expression that can contain a sub query
3. A constant value - either numeric or character

Each selection list item can be optionally followed by a "AS" keyword and an alias name for the selection item.
For eg., Select col1 as C1 from table1
In this example, C1 is the alias for the col1.

From Clause:
From clause is the set of table valued expressions from which the data is to be processed. From clause consists of the FROM keyword followed by the list of tables or table expression. The table expression that can be used here are subqueries that return a table as a result or a reference to a table in the database. Again the table expression can be aliased using AS keyword or by simply providing the alias name next to the table reference.
Select A.Col1 from Table_1 as A
The same query can also be written as
Select A.Col1 from Table_1 A

Where Clause:
Where Clause specifies a set of conditions that needs to be met by each of the resulting tuple. Where Clause contains the filtering condition that is a boolean expression which evaluates to either true or false. These boolean operations are typically relational operators like greater than, less than, equal to, etc. In addition to these operations, other clauses like IN clause and BETWEEN clause can also be used in the Where clause section of the SQL statement.

Typically the filter conditions are specified with the field name on the right hand side followed by the operation then followed by the other operand.
For eg.,
col1 != 5 is a valid condition.

There are other ways of writing filter condition too especially for the operations such as LIKE, BETWEEN, IN, etc.
abc BETWEEN '1' and '10'
abc LIKE "%1"

For IN clause, you can provide a list of values inside the values section.
abc IN ('1', '2', '3','4')
These list of values can also come from a subquery.
abc IN (Select a from tbl1 where a != 5)

No comments:

Post a Comment