Topic: T-SQL SELECT (This article is about MS SQL Server)
If you haven’t installed SQL Server yet then you’ll need to get it here.
Data in your database is stored in a structure called a TABLE. Tables have columns (hopefully with useful names). On the surface, a table looks alot like an Excel spreadsheet and that can be a useful way to think about them when you’re just starting out. The best way to learn about the SELECT command is by doing so let’s jump right into some examples.
Example
Let suppose we have a database called AdventureWorks2012 (an example database available from Microsoft for free here) and a table called Person.Person. It looks like this
- Person.Person Table Definition
You can imagine the table looking something like this under the covers…
| BusinessEntityID |
FirstName |
MiddleName |
LastName |
Suffix |
EmailPromotion |
Demographics |
rowguid |
ModifiedDate |
| 1 |
Billy |
The |
Kid |
Jr. |
Business |
18-25 |
92C4279F-1207-48A3-8448-4636514EB7E2 |
2013-08-16 00:00:00.000 |
| 2 |
Gertude |
J |
Miller |
NULL |
Consumer |
65+ |
F2D7CE06-38B3-4357-805B-F4B6B71C01FF |
2012-11-23 00:00:00.000 |
As you can see it has the following columns: BusinessEntityID, PersonType, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion, AdditionalContactInfo, Demographics, rowguid, ModifiedDate. In the image, you’ll see after the column name is some additional information in parenthesis. This extra information is describing the type of data that particular column can hold. I won’t get into it in this post but basically it is good database design to ensure that your data is consistent. Therefore, you only put numbers in columns that take numbers and you only put dates in columns that accept dates and so on and so forth.
The quickest and dirtiest way to get information from a table is to execute a SELECT query like this:
SELECT * FROM AdventureWorks2008.Person.Person
The asterisk (star) basically says to grab all columns from the table.
Results:
BusinessEntityID PersonType NameStyle Title FirstName MiddleName LastName Suffix EmailPromotion AdditionalContactInfo Demographics rowguid ModifiedDate
1 EM 0 NULL Ken J Sánchez NULL 0 NULL <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey> 92C4279F-1207-48A3-8448-4636514EB7E2 1999-02-08 00:00:00.000
2 EM 0 NULL Terri Lee Duffy NULL 1 NULL <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey> D8763459-8AA8-47CC-AFF7-C9079AF79033 1998-02-24 00:00:00.000
3 EM 0 NULL Roberto NULL Tamburello NULL 0 NULL <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey> E1A2555E-0828-434B-A33B-6F38136A37DE 1997-12-05 00:00:00.000
4 EM 0 NULL Rob NULL Walters NULL 0 NULL <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey> F2D7CE06-38B3-4357-805B-F4B6B71C01FF 1997-12-29 00:00:00.000
5 EM 0 Ms. Gail A Erickson NULL 0 NULL <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey> F3A3F6B4-AE3B-430C-A754-9F2231BA6FEF 1998-01-30 00:00:00.000
I’ve only shown the first five rows of data that were returned from the database but there are actually 19972 rows that are returned if you run the query as it is written. While the query if fast to write, it isn’t always the best choice since it causes a lot of data to have to be processed and unless you actually need every column you should choose to select only the required columns for your application. For example, suppose you only wanted the first and last name of the people in your table. Here is the SELECT query:
SELECT FirstName, LastName FROM AdventureWorks2008.Person.Person
Results
FirstName LastName
Syed Abbas
Catherine Abel
Kim Abercrombie
Kim Abercrombie
Kim Abercrombie
Hazem Abolrous
Sam Abolrous
Humberto Acevedo
Gustavo Achong
Pilar Ackerman
Pilar Ackerman
Aaron Adams
Adam Adams
Alex Adams
Alexandra Adams
I’ve only shown the first 15 names but this query returns the same number of rows as the previous query 19972. All we’ve done is reduce the columns to the necessary ones. What if we only wanted the first 100 rows? Simple:
SELECT TOP 100 FirstName, LastName FROM AdventureWorks2008.Person.Person
Notice the TOP 100 right after the SELECT command. That tells SQL Server to only pull the first 100 records. What if we wanted to sort by FirstName? This requires some new keywords ORDER BY.
SELECT TOP 15 FirstName, LastName FROM AdventureWorks2008.Person.Person ORDER BY FirstName;
I’m selecting the first 15 rows. The default sort order is ascending. Here are the results:
FirstName LastName
A. Leonetti
A. Wright
A. Scott Wright
Aaron Adams
Aaron Alexander
Aaron Allen
Aaron Baker
Aaron Bryant
Aaron Butler
Aaron Campbell
Aaron Carter
Aaron Chen
Aaron Coleman
Aaron Collins
Aaron Con
What if you want to sort descending? Use the keyword DESC (which stands for descending). There is a keyword for ascending as well…ASC.
SELECT TOP 15 FirstName, LastName FROM AdventureWorks2008.Person.Person ORDER BY FirstName DESC;
Ok folks, those are the essentials of the SQL Server SELECT command. I’ve really only touched the surface of the capabilities of the SELECT command but the goal of this post was to expose you to the basics. I’ll be writing more on the deeper details later.
Valid for SQL Server 2005, 2008, R2, 2012, 2014.
