Today, I will continue the discussion of SQL queries by discussing the the logical operators available to narrow or broaden a search. The SQL in this post is based on the “TeamMembers” database. For instructions on setting up the database in LibreOffice and a short description of the database and tables, see the post at http://wp.me/pvwht-a4.
Rehashing AND and OR
In the post on Updating and Deleting Records, I wrote about using AND and OR to combine multiple conditions for a WHERE clause. This allows us to answer questions based on more than one field in the table. As I showed in the post, this allows us to narrow or broaden our target data closer to what we want.
Order of Precedence
Just like in mathematics, the logical operators in SQL have an order of precedence. What that means is that conditions related to certain operators are evaluated before the others regardless of their order. Here is the operator order
- Brackets ()
- ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
Note that AND has greater precedence than OR. This means that AND conditions are evaluated before OR conditions. Let say we need a list of all the married members in New Town and New York. On first instinct, you might try the following SQL:
SELECT “FirstName”, “LastName”, “Married” FROM “TeamMembers” WHERE “City” = ‘New Town’ OR “City” = ‘New York’ AND “Married” = True;
However, because of the order of precedence, this evaluates to a list of team members in New Town and married members in New York. I added the married field here so you will see the unmarried team member that shows up in this query.
To correct this query so we get what we wanted, we just need to add brackets [()].
SELECT “FirstName”, “LastName”, “Married” FROM “TeamMembers” WHERE (“City” = ‘New Town’ OR “City” = ‘New York’) AND “Married” = True;
This groups the two city conditions together, giving us the correct results.
Other Logical Operators
There are four other logical operators to consider today. We will cover ALL, ANY, and SOME at a later time. The four we will go over today are NOT, BETWEEN, LIKE, and IN.
NOT negates or reverses the condition of the statement. The truth table for the NOT operator is
SELECT “FirstName”, “LastName” FROM “TeamMembers” WHERE NOT “City” = ‘New York’;
This would give us a list of team members who are not in New York. Be aware that this query produces the same results as
SELECT “FirstName”, “LastName” FROM “TeamMembers” WHERE “City” <> ‘New York’;
which just demonstrates there is more than one way to do something in SQL.
BETWEEN allows you to pull from a range of values. This is the same as asking whether something is greater than or equal to (>=) one value, and less than or equal to (<=) another value. In fact, you could use these two conditional operators and get the same results as BETWEEN, but as you will see, BETWEEN is shorter and more compact.
SELECT “FirstName”, “LastName” FROM “TeamMembers” WHERE “DateOfHire” BETWEEN ‘1990-01-01’ AND ‘2010-01-01’;
This would give use the names of all team members hired between January 1990 and January 2010. BETWEEN is inclusive, which means that it includes the start and end values. So, if someone was born January 1, 1990, their name is included in the list. You can also negate BETWEEN with a NOT.
SELECT “FirstName”, “LastName” FROM “TeamMembers” WHERE “DateOfHire” NOT BETWEEN ‘1990-01-01’ AND ‘2010-01-01’;
LIKE is used to match patterns in strings of characters by using the wild card characters underscore (_) and percent (%). Underscore is a place holder for any single character, but the percent is a place holder for one or more characters. Let’s do some examples.
SELECT “FirstName”, “LastName” FROM “TeamMembers” WHERE “LastName” LIKE ‘_enson’;
This query includes the two last names Benson and Jenson. We can create a broader search with the percent wild card.
SELECT “FirstName”, “LastName” FROM “TeamMembers” WHERE “LastName” LIKE ‘%on’;
This query includes not only Benson and Jenson but Hamelton and Benton. LIKE is handy when you are not exactly sure just what it is you are looking for or how it is spelled.
We can use not as well to get a list of names who don’t end in “on.”
SELECT “FirstName”, “LastName” FROM “TeamMembers” WHERE “LastName” NOT LIKE ‘%on’;
This query gives us a list of names like Walker, Smith, and Donovan that do not end in “on.”
IN checks to see if the value is in a set of possible values. We can manually create the search set or use an internal query to create the set. For today, we will create a manual set, but later we will discuss internal queries.
SELECT “FirstName”, “LastName” FROM “TeamMembers” WHERE “LastName” IN (‘Walker’, ‘Smith’, ‘Donovan’);
This query gives you all the Walkers, Smiths, and Donovans. Note the set in brackets. Of course, we can negate this with a NOT to get a list of everyone except the Walkers, Smiths, and Donovans.
SELECT “FirstName”, “LastName” FROM “TeamMembers” WHERE “LastName” NOT IN (‘Walker’, ‘Smith’, ‘Donovan’);
Logical operators help us to asks better questions when creating a query. Just like in mathematics, the operators are controlled by an order of precedence. AND and OR allow us to join more than one condition to the query. NOT negates any condition it is associated with. To select from a range of values, we can use BETWEEN. LIKE allows us to search the database using a pattern. Selecting only records matching a set of values is done using IN.
In my next post, we will discuss arranging the result set in a certain order.
Written with StackEdit.