Join Adam Wilbert for an in-depth discussion in this video Combining conditions with AND and OR, part of Database Fundamentals: Creating and Manipulating Data.
- When filtering out our data to a specific item, we can specify multiple criteria using the logical operators AND and OR. Let's see how to use those in a new Query. I'll come up here and start a New Query, and just verify that the H+Active Database is the active Database. Let's come up here and we'll start a SELECT statement. I'll say SELECT *, just like everything. FROM the ProductListing table, and then I'm going to come down here to the next line, and type WHERE and I want to find all of the products that include the word t-shirt. So we can do that by searching through the Item field, and then I want to find all the Items that are LIKE, and then in ' we'll type a %, the letters t-shirt with a hyphen between it, another %, which is our wildcard character, and then a closing '.
Let's go ahead and Execute that Query, and you'll see that we have three different t-shirts, but what if I only want to see the t-shirts that are specified for men? Well, we can come back up here and modify this, and we could just specify another criteria with AND, so I'll say AND Who = and then in quotes, 'men', in single quotes. Let's go ahead and Execute that, and you'll see we just get the two lines now instead. So using AND will allow us to specify criteria that are in two different lines. In this case, we're looking at the Item field AND the Who field.
But what if we want to specify two criteria on the same line? Does it work the same way? Let's go ahead and try it out. I'm going to take this here, where it says ProductListing, and just delete everything from there to the end. So this time, I want to SELECT * FROM Customers and I want to find the Customers that are both from Florida and California, so I'm going to try that. Let's type in WHERE State = and then in single quotes, 'FL' and I'll type in the word AND and I'll type in State again = 'CA'. Let's go ahead and run that.
This time I don't get any Results back, but I know for a fact that I've got some Customers in Florida and I know that I have Customers in California. So what's going on here? Well, actually, what SQL Server is telling us is that it's looking in the State field and it's trying to find all the Customers who are both from the State of Florida and the State of California at the same time. Now we know that actually can't exist, so what we're actually looking for is not AND but OR. We want to find all the Customers that are from Florida OR California. Let's go ahead and Execute this Query. Now we can see the Customers that I was expecting in the first place.
So leveraging the AND and OR operators helps us filter down to the specific information that we're looking for based off of multiple criteria.
Note: This course will also prepare certification candidates for the Microsoft Technology Associate Exam 98-364, Database Administration Fundamentals.
- Storing dates, times, and text
- Converting data types
- Creating tables
- Writing T-SQL commands
- Selecting records with queries
- Combining and sorting data
- Creating views
- Creating stored procedures and functions
- Inserting and updating data in a table
- Deleting records and tables