- Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- An Outline for Development
- Database Services
- Database Objects: Databases
- Database Objects: Tables
- Database Objects: Table Relationships
- Database Objects: Keys
- Database Objects: Constraints
- Database Objects: Data Types
- Database Objects: Views
- Database Objects: Stored Procedures
- Database Objects: Indexes
- Database Objects: User Defined Functions
- Database Objects: Triggers
- Database Design: Requirements, Entities, and Attributes
- Business Process Model Notation (BPMN) and the Data Professional
- Business Questions for Database Design, Part One
- Business Questions for Database Design, Part Two
- Database Design: Finalizing Requirements and Defining Relationships
- Database Design: Creating an Entity Relationship Diagram
- Database Design: The Logical ERD
- Database Design: Adjusting The Model
- Database Design: Normalizing the Model
- Creating The Physical Model
- Database Design: Changing Attributes to Columns
- Database Design: Creating The Physical Database
- Database Design Example: Curriculum Vitae
- The SQL Server Sample Databases
- The SQL Server Sample Databases: pubs
- The SQL Server Sample Databases: NorthWind
- The SQL Server Sample Databases: AdventureWorks
- The SQL Server Sample Databases: Adventureworks Derivatives
- UniversalDB: The Demo and Testing Database, Part 1
- UniversalDB: The Demo and Testing Database, Part 2
- UniversalDB: The Demo and Testing Database, Part 3
- UniversalDB: The Demo and Testing Database, Part 4
- Getting Started with Transact-SQL
- Transact-SQL: Data Definition Language (DDL) Basics
- Transact-SQL: Limiting Results
- Transact-SQL: More Operators
- Transact-SQL: Ordering and Aggregating Data
- Transact-SQL: Subqueries
- Transact-SQL: Joins
- Transact-SQL: Complex Joins - Building a View with Multiple JOINs
- Transact-SQL: Inserts, Updates, and Deletes
- An Introduction to the CLR in SQL Server 2005
- Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
- Design Elements Part 2: Controlling SQL's Scope
- Design Elements Part 3: Error Handling
- Design Elements Part 4: Variables
- Design Elements Part 5: Where Does The Code Live?
- Design Elements Part 6: Math Operators and Functions
- Design Elements Part 7: Statistical Functions
- Design Elements Part 8: Summarization Statistical Algorithms
- Design Elements Part 9:Representing Data with Statistical Algorithms
- Design Elements Part 10: Interpreting the Data—Regression
- Design Elements Part 11: String Manipulation
- Design Elements Part 12: Loops
- Design Elements Part 13: Recursion
- Design Elements Part 14: Arrays
- Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
- Design Elements Part 16: Event-Driven Programming
- Design Elements Part 17: Program Flow
- Forming Queries Part 1: Design
- Forming Queries Part 2: Query Basics
- Forming Queries Part 3: Query Optimization
- Forming Queries Part 4: SET Options
- Forming Queries Part 5: Table Optimization Hints
- Using SQL Server Templates
- Transact-SQL Unit Testing
- Index Tuning Wizard
- Unicode and SQL Server
- SQL Server Development Tools
- The SQL Server Transact-SQL Debugger
- The Transact-SQL Debugger, Part 2
- Basic Troubleshooting for Transact-SQL Code
- An Introduction to Spatial Data in SQL Server 2008
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Transact-SQL: More Operators
Last updated Jan 1, 2004.
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.