SELECT

Syntax:: SELECT [ predicate ]
{ * | table.* | [ table.]field1 [ AS alias1 ] [ , table.]field2 [ AS alias2 ] [ , ... ] }
FROM table [ , ... ]
[ WHERE criteria ] [ NOT ] [ IN ] [ ( value1, [ value2, [ ... ] ] ) ]
[ GROUP BY grouping_expression ]
[ HAVING criteria ]
[ ORDER BY order_expression [ ASC | DESC ] ]
[ subqueryclause [ subqueryclause [ ... ] ] ]
predicate: Keywords (ALL, DISTINCT, TOP) that can follow the SELECT statement used to limit the number of records returned.
table: Is the name of the table(s) that the information is being extracted from. It is used to indicate which field comes from which table during JOIN operations.
field1: Specifies the name of the fields to be displayed.
AS: The keyword that is used to signify that an alias is to be used in the place of the field name.
alias: Is an alternative name used to replace the field name in the query result set.
criteria: The condition(s) that determines which of the rows are to be retrieved.
NOT: Keyword used, as part of the criteria or along with the IN keyword, to select values other than the ones listed in the value list.
IN: Keyword used to determine if the values of a specified expression match any from a specified value list.
value1: Is a parameter used with the IN clause to designate a list of values to select from.
grouping_expression: The parameter that specifies a set of columns on which to group matching data.
order_expression: The parameter that indicates which field(s) and in which order the result set is to be sorted by.
ASC | DESC: Specifies whether the results of the query should be sorted in ascending or descending order.
subqueryclause: A SELECT statement nested within another SELECT statement.

The SELECT statement returns information from a database as a set of records without altering the database. It searches the database, extracts the chosen columns and selects those records that meet the criteria, sorting and grouping the results into a specified order.

A SELECT statement can be nested inside of another SELECT statement which is nested inside of another SELECT and so on.

Examples

Code:


				SELECT * FROM MusicArtists;

Output:


				MusicianID FirstName LastName Instrument Style
					
					1 Elvis Presley Guitar Rock and Roll
					2 B. B. King Guitar Blues
					3 Charlie Daniels Fiddle Country
					4 Johnny Cash Guitar Country		
					5 Henry Erickson Violin Classical
					6 Jonny Lang Guitar Blues
					7 Jerry Jamison Flute Classical
					8 Bobby Lee Fiddle Classical
					 
				
				(8 row(s) affected)

MusicianID	FirstName	LastName	Instrument	Style
1	Elvis	Presley	Guitar	Rock and Roll
2	B. B.	King	Guitar	Blues
3	Charlie	Daniels	Fiddle	Country
4	Johnny	Cash	Guitar	Country
5	Henry	Erickson	Violin	Classical
6	Jonny	Lang	Guitar	Blues
7	Jerry	Jamison	Flute	Classical
8	Bobby	Lee	Fiddle	Classical

Explanation:

This example demonstrates a very simple SELECT query, which uses an asterisk ( * ) to select all of the fields in a table (in this case the table 'MusicArtists').

Language(s): MS SQL Server

Code:


				SELECT FirstName, LastName, Instrument FROM MusicArtists;

Output:


				FirstName LastName Instrument
					
					Elvis Presley Guitar
					B. B. King Guitar
					Charlie Daniels Fiddle
					Johnny Cash Guitar
					Henry Erickson Violin
					Jonny Lang Guitar
					Jerry Jamison Flute
					Bobby Lee Fiddle
					 
						
				(8 row(s) affected)

FirstName	LastName	Instrument
Elvis	Presley	Guitar
B. B.	King	Guitar
Charlie	Daniels	Fiddle
Johnny	Cash	Guitar
Henry	Erickson	Violin
Jonny	Lang	Guitar
Jerry	Jamison	Flute
Bobby	Lee	Fiddle

Explanation:

This code shows how to be more selective and choose just one or a few of the fields in a table. They will be returned in the order listed.

Language(s): MS SQL Server

Code:


				SELECT FirstName AS First, LastName AS Last, Instrument 

				FROM MusicArtists;

Output:


				First Last Instrument
				
					Elvis Presley Guitar
					B. B. King Guitar
					Charlie Daniels Fiddle
					Johnny Cash Guitar
					Henry Erickson Violin
					Jonny Lang Guitar
					Jerry Jamison Flute
					Bobby Lee Fiddle
					 
								
				(8 row(s) affected)

First	Last	Instrument
Elvis	Presley	Guitar
B. B.	King	Guitar
Charlie	Daniels	Fiddle
Johnny	Cash	Guitar
Henry	Erickson	Violin
Jonny	Lang	Guitar
Jerry	Jamison	Flute
Bobby	Lee	Fiddle

Explanation:

This example shows how the reserved word AS is used to display an alias in the place of the field name used in the table.

Language(s): MS SQL Server

Code:

SELECT Name, City + ', ' + Country AS Location 
FROM Customers;

Output:


				Name Location
					
					Margarett Anderson Knoxville, USA
					Ronnie Jones Hong Kong, China
					Mara Clark Paris, France
					William Locke Copperhill, USA
					Nicole O'Neal Memphis, USA
					 
						
				(5 row(s) affected)

Name	Location
Margarett Anderson	Knoxville, USA
Ronnie Jones	Hong Kong, China
Mara Clark	Paris, France
William Locke	Copperhill, USA
Nicole O'Neal	Memphis, USA

Explanation:

It is also possible to combine two or more fields from a table into a single field in the result set. This is accomplished with the use of the ampersand ( & ) character to concatenate a set of fields and/or strings together.

Language(s): MS SQL Server

Code:


				SELECT FirstName + ' ' + LastName AS Musician FROM MusicArtists 

				WHERE Instrument = 'flute';

Output:


				Musician
					
					Jerry Jamison
					
										
				(1 row(s) affected)

Musician
Jerry Jamison

Explanation:

By using the WHERE clause, you can focus your selection by specifying certain criteria to be met by the values. The above example returns the names of all musicians who play the flute.

Language(s): MS SQL Server

Code:


				SELECT CharacterName AS Name, CreatedBy AS Creator FROM Toons 

				WHERE ToonType = 'Duck';

Output:


				Name Creator
					
					Donald Duck Disney
					Daisy Duck Disney
					Daffy Duck Warner Bros.
					 
				
				(3 row(s) affected)

Name	Creator
Donald Duck	Disney
Daisy Duck	Disney
Daffy Duck	Warner Bros.

Explanation:

This code returns the names of all cartoon characters who are ducks and their creators from the 'Toons' table.

Language(s): MS SQL Server

Code:


				SELECT Item, UnitPrice AS Price FROM Products 

				WHERE Color = 'blue' AND UnitPrice < 1800;

Output:


				Item Price
					
					Dell Inspiron 300m 1600.00
					Dell Axim X30 279.00
					Intellisense Mouse 20.00
					 
				
				(3 row(s) affected)

Item	Price
Dell Inspiron 300m	1600.00
Dell Axim X30	279.00
Intellisense Mouse	20.00

Explanation:

Multiple criterion in a WHERE clause can be combined using any of the logical operators. Here the query returns a list of all items which are blue and cost less than $1800.

Language(s): MS SQL Server

Code:


				SELECT * FROM PianoTuners

				WHERE State='TN' AND City IN ( 'Knoxville', 'Nashville', 'Memphis' );

Output:


				Name Address State City Phone
					
					
						Harry Ford
						22nd Avenue
						TN
						Memphis
						(555) 555-2287
					
					
						Henry Smith
						2287 Walker Rd.
						TN
						Knoxville
						(555) 555-5998
					
					
						James Galloway
						1818 Vista Dr.
						TN
						Nashville
						(555) 555-1234
					
					 
								
				(3 row(s) affected)

Name	Address	State	City	Phone
Harry Ford	22nd Avenue	TN	Memphis	(555) 555-2287
Henry Smith	2287 Walker Rd.	TN	Knoxville	(555) 555-5998
James Galloway	1818 Vista Dr.	TN	Nashville	(555) 555-1234

Explanation:

The IN operator can determine if the values of a specified expression matches any values in a specified list. This example determines if any piano tuners live in 'Knoxville', 'Nashville', or 'Memphis'. A pair of single quotes must enclose each value and commas must separate the values.

Language(s): MS SQL Server

Code:


				SELECT * FROM PianoTuners

				WHERE State='TN' AND City NOT IN ( 'Knoxville', 'Nashville', 'Memphis' );

Output:


				Name Address State City Phone
					
					
						Kenny O'Neal
						1st Street
						TN
						Copperhill
						(555) 555-5155
					
					Jerry Allison Broad St. TN Cleveland (555) 555-3528
					 
								
				(2 row(s) affected)

Name	Address	State	City	Phone
Kenny O'Neal	1st Street	TN	Copperhill	(555) 555-5155
Jerry Allison	Broad St.	TN	Cleveland	(555) 555-3528

Explanation:

The addition of a NOT clause retrieves all records whose value is not in the list.

Language(s): MS SQL Server

Code:


				SELECT *

				FROM RockAndRoll

				WHERE Artist = 'Elvis'; 


 
				SELECT ALL *

				FROM RockAndRoll

				WHERE Artist = 'Elvis';

Output:


				Artist Song Status ReleaseDate
					
					Elvis Heartbreak Hotel Double Platinum 1956
					Elvis Blue Suede Shoes Gold 1956
					Elvis Jailhouse Rock Double Platinum 1957
					Elvis Teddy Bear Double Platinum 1957
					Elvis My Wish Came True Platinum 1959
					 
				
				(5 row(s) affected)
		
				

				

				

				
				
					Artist Song Status ReleaseDate
				
					Elvis Heartbreak Hotel Double Platinum 1956
					Elvis Blue Suede Shoes Gold 1956
					Elvis Jailhouse Rock Double Platinum 1957
					Elvis Teddy Bear Double Platinum 1957
					Elvis My Wish Came True Platinum 1959
					 
				
				(5 row(s) affected)

Artist	Song	Status	ReleaseDate
Elvis	Heartbreak Hotel	Double Platinum	1956
Elvis	Blue Suede Shoes	Gold	1956
Elvis	Jailhouse Rock	Double Platinum	1957
Elvis	Teddy Bear	Double Platinum	1957
Elvis	My Wish Came True	Platinum	1959

Artist	Song	Status	ReleaseDate
Elvis	Heartbreak Hotel	Double Platinum	1956
Elvis	Blue Suede Shoes	Gold	1956
Elvis	Jailhouse Rock	Double Platinum	1957
Elvis	Teddy Bear	Double Platinum	1957
Elvis	My Wish Came True	Platinum	1959

Explanation:

The SELECT statement can optionally be followed by one of these three predicates: ALL, DISTINCT and TOP. These limit the number of records returned. The ALL predicate is the default, but it is rarely used.

Note: These two code examples yield the exact same results.

Language(s): MS SQL Server

Code:


				SELECT DISTINCT LastName 

				FROM SongWriters;

Output:


				LastName
					
					Osbourne
					Presley
					Jones
					Smith
					Jamison
					King
					Daniels
					
				
				(7 row(s) affected)

LastName
Osbourne
Presley
Jones
Smith
Jamison
King
Daniels

Explanation:

The DISTINCT predicate is used to omit duplicate values in a field. Consider a table of names, where you have the last name, 'Jones', repeated numerous times. This code returns only one 'Jones'.

Language(s): MS SQL Server

Code:


				SELECT TOP 3 FirstName + ' ' + LastName AS Musician, Instrument 

				FROM MusicArtists;

Output:


				Musician Instrument
					
					Elvis Presley Guitar
					B. B. King Guitar
					Charlie Daniels Fiddle
					 
				
				(3 row(s) affected)

Musician	Instrument
Elvis Presley	Guitar
B. B. King	Guitar
Charlie Daniels	Fiddle

Explanation:

The TOP predicate returns the specified number of records from the top of the specified table. This example returns the first 3 records from the 'MusicArtists' table.

Language(s): MS SQL Server

Code:


				SELECT COUNT(SalePrice) AS ReducedCount
 
				FROM Products;

Output:


				ReducedCount
					
					4
					
				
				(1 row(s) affected)

ReducedCount
4

Explanation:

This example uses the COUNT function to count the number of items that have an entry in the 'SalePrice' field (i.e. they are on sale) and returns that number under the heading 'ReducedCount'. You can also carry out calculations on fields containing numeric values using the aggregate functions:

Aggregate Function	Description
AVG	Average of values in a column.
COUNT	Counts how many rows.
MAX	Maximum value in a column.
MIN	Minimum value in a column.
STDEV	Sample standard deviation of the values in a column.
STDEVP	Standard deviation of the values in a column.
SUM	Adds the values in a column.
VAR	Sample variance of the values in a column.
VARP	Variance of the values in a column.

Language(s): MS SQL Server

Code:


				SELECT Item, UnitPrice AS CurrentPrice, UnitPrice * 1.1 AS IncreasedPrice 

				FROM Products;

Output:


				Item CurrentPrice IncreasedPrice
						
					Dell Inspiron 2650 1500.00 1650.00
					Dell Flat Panel LCD Monitor 700.00 770.00
					Travelstar 30gb Hard Drive 100.00 110.00
					Dell Inspiron 300m 1600.00 1760.00
					Intellisense Mouse 20.00 22.00
					Dell Axim X30 279.00 306.90
					 	
				
				(6 row(s) affected)

Item	CurrentPrice	IncreasedPrice
Dell Inspiron 2650	1500.00	1650.00
Dell Flat Panel LCD Monitor	700.00	770.00
Travelstar 30gb Hard Drive	100.00	110.00
Dell Inspiron 300m	1600.00	1760.00
Intellisense Mouse	20.00	22.00
Dell Axim X30	279.00	306.90

Explanation:

This code returns current prices along with what the prices would be after a 10% increase.

Language(s): MS SQL Server

Code:


				SELECT Item, SalePrice AS Price, UnitPrice - SalePrice AS Reduction 

				FROM Products WHERE SalePrice IS NOT Null;

Output:


				Item Price Reduction
					
					Dell Flat Panel LCD Monitor 600.00 100.00
					Dell Inspiron 300m 1439.00 161.00
					Intellisense Mouse 10.00 10.00
					Dell Axim X30 251.00 28.00
					 	
				
				(4 row(s) affected)

Item	Price	Reduction
Dell Flat Panel LCD Monitor	600.00	100.00
Dell Inspiron 300m	1439.00	161.00
Intellisense Mouse	10.00	10.00
Dell Axim X30	251.00	28.00

Explanation:

This example lists all items that are reduced along with the price and the amount of the reduction.

Language(s): MS SQL Server

Code:


				SELECT Task.Name, Task.TaskID 

				FROM Task INNER JOIN Assignment 

				ON Task.TaskID = Assignment.TaskID 

				WHERE Assignment.CompletionDate Is Null;

Output:


				Name TaskID
					
					Paint House 2312
					Complete Kitchen 2348
					 
				
				(2 row(s) affected)

Name	TaskID
Paint House	2312
Complete Kitchen	2348

Explanation:

It is possible to select fields from more than one table. In this case it is best to precede a field name with the name of the table from which it comes, followed by the dot operator ( . ). This must be done for fields of the same name, but from different tables that are used in the SELECT statement. The preceding example uses two tables, 'Task' and 'Assignment', and returns the names of all tasks belonging to assignments that are incomplete.

Language(s): MS SQL Server

Code:


				SELECT Task.Name, Task.TaskID 

				FROM Task, Assignment 

				WHERE Task.TaskID = Assignment.TaskID 

				AND Assignment.CompletionDate Is Null;

Output:


				Name TaskID
					
					Paint House 2312
					Complete Kitchen 2348
					 
				
				(2 row(s) affected)

Name	TaskID
Paint House	2312
Complete Kitchen	2348

Explanation:

As an alternative to using the explicit INNER JOIN syntax, columns from multiple tables can be combined in a single query by specifying the appropriate table list, and applying the filter condition in the WHERE clause. This is illustrated in this query, which returns the same recordset as the previous example.

Language(s): MS SQL Server

Code:


				SELECT Product FROM Suppliers 

				GROUP BY Product;

Output:


				Product
					
					Desktop
					Laptop
					Mouse
					Network Card
					Hard Drive
					Software
					Book
					Accessory
					
				
				(8 row(s) affected)

Product
Desktop
Laptop
Mouse
Network Card
Hard Drive
Software
Book
Accessory

Explanation:

The optional GROUP BY clause groups into a single record all records that have identical values in a particular field or combination of fields. This code returns a list of the different products in the 'Product' field of the 'Suppliers' table.

Language(s): MS SQL Server

Code:


				SELECT Product, Count(Supplier) AS Tally FROM Suppliers 

				GROUP BY Product HAVING Count(Supplier) > 1;

Output:


				Product Tally
					
					Desktop 25
					Laptop 19
					Mouse 33
					Network Card 3
					Software 4
					Book 6
					Accessory 7
					 
						
				(7 row(s) affected)

Product	Tally
Desktop	25
Laptop	19
Mouse	33
Network Card	3
Software	4
Book	6
Accessory	7

Explanation:

The HAVING clause is optional and qualifies a GROUP BY clause. It is similar to the WHERE clause but determines which records are displayed after they have been grouped. This example displays a list of different products, along with the number of suppliers available for each, but only where there are multiple suppliers available for the product.

Language(s): MS SQL Server

Code:


				SELECT ToonType AS Type, CharacterName AS Name FROM Toons 

				WHERE ToonType = 'Duck' OR ToonType = 'Mouse' 

				ORDER BY ToonType, CharacterName;

Output:


				Type Name
					
					Duck Daffy Duck		
					Duck Daisy Duck
					Duck Donald Duck
					Mouse Jerry
					Mouse Mickey Mouse
					Mouse Minnie Mouse
					 
				
				(6 row(s) affected)

Type	Name
Duck	Daffy Duck
Duck	Daisy Duck
Duck	Donald Duck
Mouse	Jerry
Mouse	Mickey Mouse
Mouse	Minnie Mouse

Explanation:

The ORDER BY clause can be used to dictate the order of the records returned. The preceding example returns records listed primarily in order of toon type (duck then mouse), and then for each type the relevant names are also listed in alphabetical order.

Primary links

T-SQL » Statements » SELECT

Examples

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code: