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: More Operators

Last updated Mar 28, 2003.

Transact-SQL: More Operators

We've been learning about the Transact-SQL language, beginning with the SELECT command. The SELECT command doesn't filter the results we receive, so we continued our study with the equality operators. From there, we picked up the LIKE construct, which lets us begin to use wildcards in our searches, giving us a great deal of power in our queries.

This week, we continue this limitation thread with the BETWEEN, IN, EXISTS, and the AND and OR conditions. There are several other advanced operators, which we'll explore those in future articles.

Sticking with the pubs database in SQL Server, we'll open Query Analyzer. We've already learned how to find all the data in a table by using SELECT with no limiters, and we've also learned how to use equalities (such as = and <>) and the LIKE construct to limit the results. What we'll learn about next are a couple of the logical operators.

We've already seen one logical operator: the word NOT. Recall that NOT simply negates what it's in front of. If we have this query:

  USE pubs
  GO
  SELECT * 
  FROM authors
  WHERE au_lname LIKE 'B%'  

We get all author information whose last names start with B. If we want everything but that, we use the NOT logical qualifier:

  SELECT * 
  FROM authors
  WHERE au_lname NOT LIKE 'B%'  

The next two logical operators are AND and OR. (My spell-check really hates it when I use two and's, but with computers, sometimes it's unavoidable!)

With SQL engines, we normally work with sets of data, but in the AND and OR cases, we're working with conditions of a query. In other words, we're not really combining two sets of data, but two sets of conditions. Let's take a look at a practical example.

The earlier query returns author information where the last name starts with the letter B. In this example, we want the information where the last names start with the letter B and where that same set of last names end with the letter t. Here's how that query looks:

  SELECT au_fname, au_lname 
  FROM authors
  WHERE au_lname LIKE 'B%' 
   AND au_lname LIKE '%t'

Did you see that? The reason we're focusing on what appears to be a simple concept is that many new SQL coders think that the AND condition combines sets of data, such that the above query would return not only "Bennet" as a last name, but all the "B" authors along with all authors whose names end in "t". But, in actuality, we're filtering the first set of results, even before we get them!

The important thing to remember is that the AND statement is a combination of the condition, not data sets. Another construct, which we'll learn about later, deals with combining data sets.

It's also important to remember that the AND condition can be used with as many columns as needed.

Let's use our statement-layering technique to form a complex query. Here's what we want:

The city of the author or authors, whose last name starts with an R, ends with an r, whose first name starts with an A, but whose id number does not begin with the number 9.

Sound difficult? It isn't really, when we layer the conditions. This complexity is also unraveled further if we type our SQL statement with the proper returns and indentation:

  SELECT city
  FROM authors
  WHERE au_lname LIKE 'R%' 
   AND au_lname LIKE '%r'
   AND au_fname LIKE 'A%'
   AND au_id NOT LIKE '9%'

The OR condition works just like the AND operator in that it works on the condition, but this time it combines the conditions rather than limiting them. In the complex query we just typed, we received one matching row. If we change all those AND's to OR's, we receive several more rows:

  SELECT city
  FROM authors
  WHERE au_lname LIKE 'R%' 
   OR au_lname LIKE '%r'
   OR au_fname LIKE 'A%'
   OR au_id NOT LIKE '9%'

Twenty-three rows, as a matter of fact! The reason is that with the OR condition we're not limiting the results so much as opening them up. Here's that query reversed into the same sort of sentence we saw a moment ago:

The city of the author or authors, whose last name starts with an R, or whose last name ends with an r, or whose first name starts with an A, or whose id number does not begin with the number 9.

Even with the few search conditions we've learned so far, we can create very complicated queries by layering the conditions, one at a time. It's important to practice with these conditions until we're comfortable with them. Just remember that by layering we're operating on a result set obtained by the previous condition.

Let's move on to the next condition. We've learned that we can take the OR clause to find information where the clause meets several conditions, but if we have several conditions to meet, it can become quite cumbersome. For instance, to find the names of authors that live in California, Kansas or Utah, we could write this query:

  SELECT au_fname, au_lname 
  FROM authors
  WHERE state = 'CA' 
   OR state = 'KS'
   OR state = 'UT'

While this works, if there are a lot of states to add it gets kind of difficult to type. T-SQL includes another condition, called IN, that will help us out. With the IN clause, we can include all the conditions in one list, like this:

  SELECT au_fname, au_lname 
  FROM authors
  WHERE state IN ('CA', 'KS', 'UT')

While this syntax is useful, we'll see the real power of IN when we learn about subqueries. (We'll also see a similar clause to IN, the EXISTS clause.) Before we leave IN, remember that we can apply NOT to any of our search conditions, so NOT IN would restrict the data to those rows that aren't in the states we specified.

Now let's move on to the BETWEEN operator. The BETWEEN operator works much like it sounds: it limits the return set of data to values that match the conditions between the data you specify. It sounds harder than it is. Here's an example:

  SELECT au_fname, au_lname, state 
  FROM authors
  WHERE state BETWEEN 'CA' AND 'IN'

This all seems pretty straightforward. But wait, couldn't we do the same thing with the > and < operators that we already learned about?

Not really. The reason is that the BETWEEN operator is inclusive. It includes the items on either side of the AND it contains. The >< operators are exclusive – they don't include the items on the left and right.

In the query above, we get 16 rows, but with the query rewritten this way, we get no rows at all:

  SELECT au_fname, au_lname, state 
  FROM authors
  WHERE state > 'CA' AND state < 'IN'

Quite the difference!

Well, that's enough for this week; we've got plenty to practice for now. We'll use these operators, and more, in future articles. Next week, we'll begin looking at subqueries.

Online Resources – Transact-SQL

The w3schools folks have a good article regarding BETWEEN. It's important to know that various engines implement BETWEEN differently!

InformIT Articles and Sample Chapters – Transact-SQL

In the Safari offering by by F. Scott Barker called Database Programming with Visual Basic .NET and ADO.NET: Tips, Tutorials, and Code, you can read more about the various operators I've been covering. It's good stuff.