SELECT by George Hernandez

The SQL command SELECT retrieves rows. The basic syntax is as follows:

SELECT [ALL | DISTINCT] [TOP(n) [PERCENT]] SelectList
[INTO NewTable]
[FROM TableSources]
[WHERE RowSearchCondition]
[ORDER BY OrderList [ASC | DESC]]

The SELECT command retrieves columns specified by the SelectList, from rows that come from TableSources .

The ALL keyword is the default and therefore optional. It returns all rows, even if there are duplicate rows.

The DISTINCT keyword can be used to eliminate duplicate rows from the results of a SELECT command. Here is a classic example of its usage:

SELECT Name FROM table1 might yield 9, but SELECT DISTINCT Name FROM table1 might yield 6.
SELECT COUNT(Name) FROM table1 might yield 9 rows, but SELECT COUNT(DISTINCT Name) FROM table1 might yield 6 rows.

The TOP keyword makes the SELECT command take the result set it would normally return, and return either the first n rows or the first n PERCENT. You can always get the "bottom" by having them in descending order. Note that this concept is implemented in different ways:

SELECT TOP(3) * FROM table1. Microsoft SQL Server.
- SELECT TOP (expression) [PERCENT] [WITH TIES]. As SQL Server 2005. The WITH TIES option returns a bit more than the top if some of them have the same value, based on the ORDER BY clause.
SELECT * FROM table1 WHERE ROWNUM <= 3. Oracle. Note if you use ORDER BY, then watch out:
- SELECT * FROM table1 WHERE ROWNUM <= 3 ORDER BY id. This gets the first three, and then does orders.
- SELECT * FROM (SELECT * FROM table1 ORDER BY id) WHERE ROWNUM <= 3. This orders, and then gets the top 3.
SELECT * FROM table1 LIMIT 3. MySQL. The first 3 rows,
- SELECT * FROM table1 LIMIT 3,5. Rows 4-8 since the initial row is 0.

The SelectList is a comma-separated list of expressions that yield columns in the results of a SELECT command. Here are a few particular about the SelectList:

If SelectList = *, then all columns from TableSources are returned by the SELECT command.
The expressions in the list should be arranged in the order desired for the results of a SELECT command. This order can can be referenced with the ORDER BY clause.
Each expression intrinsically defines the format (data type and size) as well as table source of the data returned in the results of a SELECT command.
Although each expression is commonly a reference to a column from the TableSources, it may also be made up of literals, SQL functions, and even a sub-query that returns a column equivalent.
The column heading returned by the the results of a SELECT command can be changed or set (especially with calculated expressions) using this syntax:

SELECT expression [AS] NewColumnName [, ...]
--It is customary to use the AS keyword.

SELECT NewColumnName = expression [, ...]
--This is particular to Transact-SQL.

The INTO clause uses the results of a SELECT command to make a new table NewTable, or a new local temporary table #NewTempTable (available only for the current connection) or a new global temporary table ##NewTempTable (available until the last connection using it closed).

The FROM clause specifies other table or view sources from which rows are pulled to make the results of a SELECT command. If a SELECT command is pulling data from tables or views, then it requires at least one table or view to be named in the FROM clause, otherwise it can skip the FROM clause.

The WHERE clause makes the SELECT command output only the rows that meet the RowSearchCondition.

The ORDER BY clause orders the rows of the results of a SELECT command according to each column specified in the comma-separated OrderList. Each column sorts by ASC (ascending, the default) or DESC (descending). Each column must be one of the columns in the SelectList. Each column in the list may be:

A column
An alias
An expression, equivalent to an expression in the SelectList.
The column number as determined by the SelectList. This may be easier than re-entering expressions used in the SelectList.

EGs

All rows

These SELECT commands are equivalent and return all rows and all columns from a table.

SELECT *
FROM tblA

SELECT ALL tblA.*

SELECT without using FROM

These SELECT commands don't need a FROM clause since they don't reference a table.

SELECT PI(), 5
--These are constants

SELECT DB_ID('pubs')
--This is a system function.

Returning a calculated column

This SELECT command returns a result set with some columns from a table and a calculated column.

SELECT Title, Rtrim(FName) + ' ' + Rtrim(LName) AS [Full Name]
FROM tblA
--The above returns results from all rows.

SELECT Title, Rtrim(FName) + ' ' + Rtrim(LName) AS [Full Name]
FROM tblA
WHERE Salary >= 75000
--The above returns results from some rows.

ORDER BY the results

This SELECT command sorts out its result set.

SELECT *
FROM tblCustomers
ORDER BY CustomerID

Count rows

This SELECT command returns a count of the number of rows that meet the condition. SELECT commands of this format are frequently used as sub-queries, since it returns a single value.

SELECT Count(*)
FROM tblCustomers
WHERE Name = 'Fred'

Page Modified: (Hand noted: 2007-10-12 20:18:48Z) (Auto noted: 2007-11-17 06:45:19Z)