Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Transact-SQL: Limiting Results

Last updated Mar 28, 2003.

In a previous article, I explained one of the most powerful constructs in the Transact-SQL (T-SQL) language — the SELECT statement. This most basic of statements is used to retrieve data from tables or views. You can also use it query system variables that will show you things like the name of the server, the time on the system, or you can even use it to perform mathematical or statistical functions on numbers or data. But the most common use for SELECT is to pull data from the database.

As I explained, the simplest form of SELECT is:

SELECT something;
GO

A couple of notes here. First, in T-SQL, the SELECT statement doesn't have to be capitalized. I just find that it's easier to read code if the keywords from the language are capitalized. Second, I included a semicolon (;)  at the end of the statement — not because it was on a single line, but because it terminates a statement like SELECT. While this isn't required, it's good form, and as of this writing it's been announced that it will be required in the future. Lastly, I included a GO in there — which means do this group (or block or transaction) as a unit. There are times when that actually is required, as I'll explain further.

While the SELECT something works, it only works when you're not looking for data from a table. So this works well:

SELECT 'Buck';
GO

Which returns the word Buck to the results pane. Notice that I put a "tick" character (') around Buck, because I wanted it treated as text. If I wanted to return a number, I leave that off:

SELECT 1;
GO

So you can see data types matter when you run a SELECT statement. So far, this isn't terribly useful, although you can use it to find out things like the current date:

SELECT GETDATE();
GO

Where GETDATE() is a system function. But again, you're normally after data in a table, so you add the FROM clause, like I explained in the previous article:

SELECT something
FROM somewhere;
GO

I explained earlier that the "something" is normally one or more columns, and that the "somewhere" is normally a table or view. And this is where the first part of the limitation of data comes in. Using the Adventureworks sample database, you can return all of the row (also called a tuple or record) and all of the columns (also called attributes) from the People Table like this:

USE Adventureworks;
GO
SELECT *
FROM Person.Contact;
GO

This is almost always a bad idea. You rarely need all of the columns and all of the rows in a database, and even if you do, it's a far better practice to list the column names out in the order you want them. In fact, this is the first way that you can limit the data you return from the system. Always find out exactly what the application needs, and then go after only that data. For instance, assume I only want the first and last names and the e-mail addresses from this table. For that I'll just select those columns:

SELECT FirstName, LastName, EmailAddress
FROM Person.Contact;
GO

So far this is a refresher of what I've already covered. Selecting only certain columns is a way to limit data vertically, which saves resources on the server, the network, the client and so on. Now I want to limit the number of rows that come back — a horizontal limit. One simple way to do that is to tell the system to only bring back a certain number of rows. You can do that with the TOP modifier:

SELECT TOP 100
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact;
GO

But that isn't in any kind of order, so the system simply pulled back the first 100 rows it found. You could add an ORDER BY statement (more on that later) to bring back the first 100 rows by name or e-mail address, but that isn't a common case either.

What you're really after is a way to compare some value to the data and bring back only those rows that match, don't match, or have some other relationship. The way the SQL language deals with this need is the WHERE modifier. It's similar to asking someone, "Get me all the blue pencils." Here's the format:

SELECT something
FROM somewhere
WHERE something = somevalue;
GO

I'll put this concept into a practical example. Let's say I only want the names of the authors where their last name is "White." Here's the query for that:

SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName = 'White';
GO

And you get back the records where the last name is White. I'll continue this example by finding out the same information (last name is White) but also if another field "EmailPromotion" is "No" or 0. Here's the query:

SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName = 'White' AND EmailPromotion = 0;
GO

Notice that the column I compared on (EMailPromotion), isn't part of the data I want to show. For now, it's important to understand that the items you compare on don't have to be part of the SELECT statement.

So far I've used only one operator so far, the = sign. This operator means that the item on the right must match the item on the left. There are several other operators, most of them obvious, some of them not. Here's a table that explains a few of them:

Operator

Meaning

=

(Equals) Equal to

(Greater Than) Greater than

(Less Than) Less than

>=

(Greater Than or Equal To) Greater than or equal to

<=

(Less Than or Equal To) Less than or equal to

<>

(Not Equal To) Not equal to

!=

(Not Equal To) Not equal to (not SQL-92 standard)

!<

(Not Less Than) Not less than (not SQL-92 standard)

!>

(Not Greater Than) Not greater than (not SQL-92 standard)

ALL

TRUE if all of a set of comparisons are TRUE.

AND

TRUE if both Boolean expressions are TRUE.

ANY

TRUE if any one of a set of comparisons are TRUE.

BETWEEN

TRUE if the operand is within a range.

EXISTS

TRUE if a subquery contains any rows.

IN

TRUE if the operand is equal to one of a list of expressions.

LIKE

TRUE if the operand matches a pattern (Text).

NOT

Reverses the value of any other Boolean operator.

OR

TRUE if either Boolean expression is TRUE.

This is quite the list — and it's not even all of them! Here's where you can get into "deep weeds" very quickly in using the layering technique on the SQL Statements.

I took quite a bit of time explaining the SELECT and WHERE constructs, but now I'll move a bit quicker with the queries using these operators. I won't cover them all in this lesson, but we will by the end of the series.

It's important to note that some operators don't go with certain types of data. Some numeric operators, for instance, don't work with certain data types (such as DATETIME) in ways that you might expect.

That being said, I'll take a few moments to explain a couple of these operators and show you how they work. I'll start by addressing a shortcoming with the = sign (equality) operator.

With the equality operator, both sides of the condition must match for any rows to return. Many times, however, you want only certain parts of the condition to match. For instance, if you only want to match on the first letter of the condition, you can't use the equality operand. For that, you can use the LIKE operator.

The LIKE operator does just what it says. For instance, this query returns rows where the last name is like "White":

SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE 'White';
GO

Of course, this example is no different than what you can get with the equality operator. The real power with LIKE comes with the wildcard characters that it supports. Wildcards simply take the place of any other character. The most widely used wildcard is the % sign, which takes the place of one or more characters. Here's a query that returns all the last names that start with the letter "B":

SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE 'B%';
GO

The % wildcard works in both directions, meaning that you can set the wildcard for the letters in front of another that you want to query on. This query returns all the names where the last name ends in the letter "e":

SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE '%e';
GO

That ending doesn't have to be a single letter, either; you can use multiple letters at the beginning or end. OK, just to overdo this example, there's one more way to use the % wildcard. To find all the last names that start with the letters "Br" and end with the letter "s", the query would look like this:

SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE 'Br%s';
GO

If you wanted the last names that had the letters "ee" somewhere in them:

SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE '%ee%';
GO

There are other wildcards, of course, and they work in much the same way. Here's another table to help with those:

Symbol

Description

%

Any string of zero or more characters

_

(underscore) Any single character

[ ]

Any single character within the specified range ([a-f]) or set ([abcdef])

[^]

Any single character not within the specified range ([^a-f]) or set ([^abcdef])

As you can see, just with the LIKE operator, the complexity and power of the SELECT statement is growing. I'll explain one more layering concept that I'll use in future tutorials.

Going back to that operators chart, you can see that you have a convenient way to negate any of your queries, finding its opposite. That operator, NOT, is used in conjunction with other operators. It negates anything it's in front of. Here's a query that finds all the names where the first two letters of the last name aren't "Wh":

SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName NOT LIKE 'Wh%';
GO

If you're just after negating the entire string, by the way, you don't have to bother with NOT. You can just use the inequality symbol, <>. It's sort of a greater-than and less-than symbol all at once. Here's how that looks in a query:

SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName <> 'White';
GO

As mentioned, however, this is only for complete inequalities, not wildcard matches.

As you can see, simply layering these simple statements together allows you to create very complicated selection criteria. You should practice a great deal with the AdventureWorks sample database, and explore the sample queries for it found in Books Online. It's one of the most important skills you can have as a database developer.