The SELECT reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database as recordsets. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. All reserved words that I introduce below will be in CAPS below.
Today, I would like to review simple SELECT queries using the AdventureWorks Sample database supplied by Microsoft.
The most basic use of the SELECT statement is to return a constant as a record set.
1 2 3 4 5 6 7 8 9 |
<span style="color: #008000;">-- Return string constant SELECT 'Harry Potter and the Deathly Hollows Part 2' AS NewMovie -- Return integer constant SELECT 712 AS NewInteger -- Return decimal constant SELECT 5.234 AS NewReal </span> |
To really use this statement to retrieve data, we need to supply the name of an existing table. This is known as the FROM clause. The example below uses a three part notation to describe the database name, schema owner, and table name to SELECT data from.
1 2 3 |
<span style="color: #008000;">-- Three part notation for table name SELECT * FROM [AdventureWorks2008R2].[Person].[AddressType] </span> |
Instead of supply this three part notation, we can specify which database we want to USE up front. The GO reserved word executes and commits prior statements. The example below uses the Adventure Works database and returns the TOP 10 persons from the table.
1 2 3 4 5 6 7 |
<span style="color: #008000;">-- Switch to correct database to drop 1 part USE AdventureWorks2008R2; GO -- Show top 10 persons (predicate) SELECT TOP 10 * FROM Person.Person </span> |
This query is not to helpful since it does not specify the rows we want. The WHERE clause is used to supply an expression that filters the data. The first query looks for any relatives of Bill Gates.
1 2 3 4 |
<span style="color: #008000;">-- Any relatives of Bill Gates? (where clause) SELECT * FROM Person.Person WHERE LastName = 'Gates' </span> |
An table alias, AS reserved word, can be used to allow for shorter notation when referencing fields. This is most useful when tables with the same field name are joined and a ambigous reference is made.
The examples below show the use of standard comparison operators with the unique ID on the person table to return various rows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<span style="color: #008000;">-- Equal SELECT * FROM Person.Person AS P WHERE P.BusinessEntityID = 7 -- Greater Than; Less Than SELECT * FROM Person.Person AS P WHERE P.BusinessEntityID > 6 AND P.BusinessEntityID < 8 -- Greater Than or Equal; Less Than or Equal SELECT * FROM Person.Person AS P WHERE P.BusinessEntityID >= 6 AND P.BusinessEntityID <= 8 -- Not Equal To SELECT TOP 10 * FROM Person.Person AS P WHERE P.BusinessEntityID <> 5 </span> |
So far, we have been returning every column in the table using the * reserved word. This is extremely wasteful in network traffic, bandwidth, if we were only interested in the city name and postal code of Seattle, Washington. The example below returns the extact information we want.
1 2 3 4 5 |
<span style="color: #008000;">-- Selecting just a few fields (141) SELECT A.[City], A.[PostalCode] FROM [AdventureWorks2008R2].[Person].[Address] AS A WHERE A.[City] = 'Seattle' </span> |
Sometimes, we want to find information for two cities. The IN reserved word uses set notation to allow for multiple filters in a shortened format.
1 2 3 4 5 |
<span style="color: #008000;">-- In search condition (343) SELECT A.[City], A.[PostalCode] FROM [AdventureWorks2008R2].[Person].[Address] AS A WHERE A.[City] IN ('Seattle', 'Berkeley') </span> |
Another simple query is to return all the Cities that start with y. The LIKE reserved work uses a regular expression pattern to filter rows. Therefore, we specify a pattern to search for all cities that start with y.
1 2 3 4 5 |
<span style="color: #008000;">-- Like search condition (213) SELECT A.[City], A.[PostalCode] FROM [AdventureWorks2008R2].[Person].[Address] AS A WHERE A.[City] LIKE 'y%' </span> |
As you can see, each of the queries that we execute are returning multiple rows from the sample database. Instead we want to return all DISTINCT rows for cities that are IN Seatle, Berkeley, Yakima, and York. The query below does just that.
1 2 3 4 5 |
<span style="color: #008000;">-- Get distinct answers SELECT DISTINCT A.[City], A.[PostalCode] FROM [AdventureWorks2008R2].[Person].[Address] AS A WHERE A.[City] IN ('Seattle', 'Berkeley', 'Yakima', 'York') </span> |
Regular expressions can be very powerful. The following statement returns all cities that start with “Sea”.
1 2 3 4 5 |
<span style="color: #008000;">-- Regular expressions, cities that start with SEA (221) SELECT A.[City], A.[PostalCode] FROM [AdventureWorks2008R2].[Person].[Address] AS A WHERE A.[City] LIKE '[S][e][a]%' </span> |
Boolean expressions (AND, OR, NOT) can be used to join multiple expressions to get the desired result. The query to find postal codes for Seattle and Redmond can be rewritten with the OR reserve word.
1 2 3 4 5 |
<span style="color: #008000;">-- Using OR (262) SELECT A.[City], A.[PostalCode] FROM [AdventureWorks2008R2].[Person].[Address] AS A WHERE A.[City] = 'Seattle' OR A.[City] = 'Redmond' </span> |
Using the AND reserve work on the same query returns no data since the condition can not be meet.
1 2 3 4 5 |
<span style="color: #008000;">-- Using AND (0) SELECT A.[City], A.[PostalCode] FROM [AdventureWorks2008R2].[Person].[Address] AS A WHERE A.[City] = 'Seattle' AND A.[City] = 'Redmond' </span> |
The NOT reserve word can be used to find all matches that do not meet a criteria.
1 2 3 4 5 |
<span style="color: #008000;">-- Using NOT (19473) SELECT A.[City], A.[PostalCode] FROM [AdventureWorks2008R2].[Person].[Address] AS A WHERE NOT (A.[City] = 'Seattle') </span> |
Last but not least, there are times that we want to look at the TOP n rows. All other data is not of interest at the current time. The following query returns the three lowest cost items by using the ORDER BY clause and the TOP keyword.
1 2 3 4 5 6 |
<span style="color: #008000;">-- Returns three records SELECT TOP 3 * FROM [AdventureWorks2008R2].[Production].[Product] p WHERE P.ListPrice <> 0 ORDER BY P.ListPrice ASC </span> |
However, this query is not truely accurate since there are three products (records) in the database at $4.99. Using the WITH TIES reserved word allows these identical rows to show up in the result set.
1 2 3 4 5 6 |
<span style="color: #008000;">-- Returns five records (3 @ 4.99) SELECT TOP 3 WITH TIES * FROM [AdventureWorks2008R2].[Production].[Product] p WHERE P.ListPrice <> 0 ORDER BY P.ListPrice </span> |
To recap this discussion, the SELECT statement can be very complex equation laced with multiple levels of SYNTAX. We have just started looking at the tip of the iceberg with this statement. Next time I talk about the SELECT statement, I will be introducing how to join multiple tables or use sub-queries to get the results that you want.
There is evidently a bunch to know about this. I consider you made various nice points in features also.
Hey, wonderful website. I came across this on the search engines, and I m extremely happy that I would. I will without a doubt be re-occurring here more frequently. Wish I could increase the info in addition to bring a tad bit more to the submit, but was just looking at as much details as I can easily at the moment.
I needed to write you one bit of note just to say thanks once again with the unique ideas you have documented here. This has been quite shockingly open-handed with people like you to supply publicly all that some people would have sold for an e book to help with making some bucks for their own end, chiefly seeing that you could have tried it in the event you considered necessary. The smart ideas likewise acted like a good way to recognize that other people online have the identical dream like my own to learn significantly more with regards to this issue. I’m sure there are lots of more pleasant instances in the future for many who scan your website.
As a Newbie, I am continuously browsing online for articles that can help me. Thank you
Well I really liked reading it. This post provided by you is very constructive for good planning.
Hi! This is my first visit to your blog! We are a group of volunteers and starting a new project in a community in the same niche. Your blog provided us useful information to work on. You have done a wonderful job!
Wow! This could be one particular of the most helpful blogs We have ever arrive across on this subject. Basically Excellent. I am also a specialist in this topic therefore I can understand your hard work.
I’d constantly want to be update on new articles on this web site , bookmarked ! .
You got a very excellent website, I discovered it through yahoo.
so much great info on here, : D.
thanks to the author for taking his clock time on this one.
I appreciate all the work you have put into your website! I’m going to Tweet this out to my followers… Definitely worth repeating!