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 Column1 [ASC | DESC][, Column2 [ASC | DESC]][etc]]
The SELECT command retrieves columns specified by the
SelectList, from rows that come from
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 table1might yield 9, but
SELECT DISTINCT Name FROM table1might yield 6.
SELECT COUNT(Name) FROM table1might yield 9 rows, but
SELECT COUNT(DISTINCT Name) FROM table1might 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
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 TIESoption returns a bit more than the top if some of them have the same value, based on the
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
*, then all columns from
TableSourcesare 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
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
- The column number as determined by the
SelectList. This may be easier than re-entering expressions used in the
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
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'
A scalar subquery can be used in a select list but it must return one column and one row. This is commonly used for look ups.
SELECT O.ID , O.Size , (SELECT C.Color FROM Color_LU C WHERE C.ID = O.ColorID) FROM Order O