by Raj Kumar

Created on: July 31, 2009

The most common SQL command is SELECT statement which falls under data manipulation language (DML) group of statements. Data gets saved and modified in the database table on regular basis. But the data change is only visible on querying the table and checking the desired output in the form of affected records. Either the user is related to database application or involved in pure backend tasks, fetching and retrieving SQL records is often done for reporting purpose. To achieve the data retrieval, SELECT statement always comes into the picture. Though the standard form of ANSI SQL has been little bit customized in different databases viz. Microsoft SQL Server, Oracle etc and resulted into customized forms viz. T-SQL, PL/SQL etc, syntax and clause involved in normal SQL commands are more or less the same. Hence SELECT statement also has similar syntax, clause and same order of each SQL clause in almost every database management system.

With the help of SELECT statement, data can be retrieved from one or more database tables. It can select one or multiple columns from one or multiple tables. Rows can be selected based on search criteria applied using WHERE clause. The actual detailed syntax of SELECT statement is complex and seems to be lengthy. Main clauses which are used in SELECT statement are WITH, SELECT, INTO, FROM, WHERE, GROUP BY, HAVING, UNION, EXCEPT, INTERSECT, ORDER BY, COMPUTE, FOR, and OPTION clause. Many of them are optional but their order or position in SELECT statement is important. If the optional clause is not used, it's not an issue. But their order should be correct with appropriate values, if they are present in SELECT statement.

Depending on their presence, the processing order of each clause differs with different clause combination. Irrespective of the presence or absence of optional SQL clause, the processing order for a typical SELECT statement flows with steps viz. FROM, ON, JOIN, WHERE, GROUP BY, WITH CUBE or WITH ROLLUP, HAVING, SELECT, DISTINCT, ORDER BY, TOP. Knowledge of the processing order can help in optimizing the resources and improving the performance of SELECT statement.

Normally SELECT statement returns the resulting rows within the table specified in FROM clause. But SELECT ...INTO combination works differently and creates a new database table based on the schema of source table specified in FROM clause. This combination inserts the resulting data rows from the query into the new created destination table. Data from

• 1
• 2
• Next Page >>