- SELECT Overview and Syntax
- Choosing Columns: The SELECT Clause
- Specifying Tables: The FROM Clause
- Selecting Rows: The WHERE Clause
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Selecting Rows: The WHERE Clause
The WHERE clause is the part of the SELECT statement that specifies the search conditions. These conditions determine exactly which rows are retrieved. The general format is this:
SYNTAX
SELECT select_list FROM table_list WHERE search_conditions
When you run a SELECT statement with a WHERE clause, your system searches for the rows in the table that meet your conditions (also called qualifications).
SQL provides a variety of operators and keywords for expressing the search conditions, including these:
Comparison operators
(=, <, >, and so on) select title from titles where advance * 2 > ytd_sales * price
Combinations or logical negations of conditions
(AND, OR, NOT) select title from titles where advance < 5000 or ytd_sales > 2000
Ranges (BETWEEN and NOT BETWEEN)
select title from titles where ytd_sales between 4095 and 12000
Lists (IN, NOT IN)
select pub_name from publishers where state in ('CA', 'IN', 'MD')Unknown values
(IS NULL and IS NOT NULL) select title from titles where advance is null
Character matches
(LIKE and NOT LIKE) select au_lname from authors where phone not like '415%'
Each of these keywords and operators is explained and illustrated in this chapter. In addition, the WHERE clause can include join conditions (see Chapter 7) and subqueries (see Chapter 8).
Comparison Operators
You often want to look at values in relation to one another to find out which is "larger" or "smaller" or "lower" in the alphabet sort or "equal" to some other database value or to a constant. SQL provides a set of comparison operators for these purposes. In most dialects, the comparison operators are these:
|
Operator |
Meaning |
|
= |
equal to |
|
> |
greater than |
|
< |
less than |
|
>= |
greater than or equal to |
|
<= |
less than or equal to |
|
< > |
not equal to |
The operators are used in the syntax:
SYNTAX
WHERE expression comparison_operator expression
An expression can be a plain column name or something more complex—a character string, a function or calculation (usually involving a column name), or any combination of these elements connected by arithmetic operators. When evaluated, an expression produces a single value per row.
In contexts other than SQL, the comparison operators are usually used with numeric values. In SQL, they are also used with char and varchar data (< means earlier in the dictionary order and > means later) and with dates (< means earlier in chronological order and > means later). When you use character and date values in a SQL statement, be sure to put quotes around them.
TIP
The order in which uppercase and lowercase characters and special characters are evaluated depends on the character-sorting sequence you are using, imposed by your database system or by the machine you are using. (There are more details on sort order in "Character Sets and Sort Orders"). Check your system to see how it handles trailing blanks in comparisons. Is "Dirk" considered the same as "Dirk "?
Comparing Numbers
The following SELECT statements and their results should give you a good sense of how the comparison operators are used. The first query finds the books that cost more than $25.00.
SQL
select title, price from titles where price > $25.00 title price =============================================================== ===== Secrets of Silicon Valley 40.00 The Busy Executive's Database Guide 29.99 Prolonged Data Deprivation: Four Case Studies 29.99 Silicon Valley Gastronomic Treats 29.99 Sushi, Anyone? 29.99 But Is It User Friendly? 42.95 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 40.95 Straight Talk About Computers 29.99 Computer Phobic and Non-Phobic Individuals: Behavior Variations 41.59 [9 rows]
SQL Variants
Check your system to see if it allows dollar signs with money values. Most do not. Transact-SQL is an exception, and so is Adaptive Server Anywhere.
Comparing Character Values
The next SELECT statement finds the authors whose last names follow McBadden in the alphabet. Notice the name is in single quotes. (Some systems allow both single and double quotes around character and date constants in the WHERE clause, but most allow single quotes only.)
SQL
select au_lname, au_fname from authors where au_lname >'McBadden' au_lname au_fname ======================================== =============== O'Leary Michael Panteley Sylvia Ringer Albert Ringer Anne Smith Meander Straight Dick Stringer Dirk White Johnson Yokomoto Akiko [9 rows]
(Your results may differ, depending on the sort order your system uses. See Chapter 5 for more on this issue.)
Comparing Imaginary Values
The next query displays hypothetical information—it calculates double the price of all books for which advances over $10,000 were paid and displays the title identification numbers and calculated prices:
SQL
select title_id, price * 2 from titles where advance > 10000 title_id titles.price*2 ======== ============== BU2075 25.98 MC3021 25.98 [2 rows]
Finding Values Not Equal to Some Value
Following is a query that finds the telephone numbers of authors who don't live in California, using the not equal comparison operator (in some SQL dialects, you can use != as the not equal operator).
SQL
select au_id, phone from authors where state <> 'CA' au_id phone =========== ============ 998-72-3567 801 826-0752 899-46-2035 801 826-0752 722-51-5454 219 547-9982 807-91-6654 301 946-8853 527-72-3246 615 297-2723 712-45-1867 615 996-8275 648-92-1872 503 745-6402 341-22-1782 913 843-0462 [8 rows]
Connecting Conditions with Logical Operators
Use the logical operators AND, OR, and NOT when you're dealing with more than one condition in a WHERE clause. The logical operators are also called Boolean operators.
AND
AND joins two or more conditions and returns results only when all of the conditions are true. For example, the following query will find only the rows in which the author's last name is Ringer and the author's first name is Anne. It will not find the row for Albert Ringer.
SQL
select au_id, au_lname, au_fname from authors where au_lname = 'Ringer' and au_fname = 'Anne' au_id au_lname au_fname =========== =================================== ======== 899-46-2035 Ringer Anne [1 row]
The next example finds business books with a price higher than $20.00 and for which an advance of less than $20,000 was paid:
SQL
select title, type, price, advance from titles where type = 'business' and price > 20.00 and advance < 20000 title type price advance ======================================= ======== ====== ======== The Busy Executive's Database Guide business 29.99 5000.00 Cooking with Computers: Surreptitious Balance Sheets business 21.95 5000.00 Straight Talk About Computers business 29.99 5000.00 [3 rows]
OR
OR also connects two or more conditions, but it returns results when any of the conditions is true. The following query searches for rows containing Anne or Ann in the au_fname column:
SQL
select au_id, au_lname, au_fname from authors where au_fname = 'Anne' or au_fname = 'Ann' au_id au_lname au_fname =========== ================================= ========== 899-46-2035 Ringer Anne 427-17-2319 Dull Ann [2 rows]
The following query searches for books with a price higher than $20.00 or an advance less than $5,000:
SQL
select title, type, price, advance from titles where price > $30.00 or advance < $5000 title type price advance ========================================== ============ ===== ======= Secrets of Silicon Valley popular_comp 40.00 8000.00 Emotional Security: A New Algorithm psychology 17.99 4000.00 Prolonged Data Deprivation: Four Case Studies psychology 29.99 2000.00 Silicon Valley Gastronomic Treats mod_cook 29.99 0.00 Fifty Years in Buckingham Palace Kitchens trad_cook 21.95 4000.00 But Is It User Friendly? popular_comp 42.95 7000.00 Is Anger the Enemy? psychology 21.95 2275.00 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean trad_cook 40.95 7000.00 Computer Phobic and Non-Phobic Individuals: Behavior Variations psychology 41.59 7000.00 [9 rows]
Semantic Issues with OR and AND
One more example using OR will demonstrate a potential for confusion. Let's say you want to find all the business books, as well as any books with a price higher than $10 and any books with an advance less than $20,000. The English phrasing of this problem suggests the use of the operator AND, but the logical meaning dictates the use of OR because you want to find all the books in all three categories, not just books that meet all three characteristics at once. Here's the SQL statement that finds what you're looking for:
SQL
select title, type, price, advance from titles where type = 'business' or price > $20.00 or advance < $20000 title type price advance ========================================= ============ ===== ======= Secrets of Silicon Valley popular_comp 40.00 8000.00 The Busy Executive's Database Guide business 29.99 5000.00 Emotional Security: A New Algorithm psychology 17.99 4000.00 Prolonged Data Deprivation: Four Case Studies psychology 29.99 2000.00 Cooking with Computers: Surreptitious Balance Sheets business 21.95 5000.00 Silicon Valley Gastronomic Treats mod_cook 29.99 0.00 Sushi, Anyone? trad_cook 29.99 8000.00 Fifty Years in Buckingham Palace Kitchens trad_cook 21.95 4000.00 But Is It User Friendly? popular_comp 42.95 7000.00 You Can Combat Computer Stress! business 12.99 10125.00 Is Anger the Enemy? psychology 21.95 2275.00 Life Without Fear psychology 17.00 6000.00 The Gourmet Microwave mod_cook 12.99 15000.00 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean trad_cook 40.95 7000.00 Straight Talk About Computers business 29.99 5000.00 Computer Phobic and Non-Phobic Individuals: Behavior Variations psychology 41.59 7000.00 [16 rows]
Compare this query and its results to the earlier example that is identical except for the use of AND instead of OR.
NOT
The logical operator NOT negates an expression. When you use it with comparison operators, put it before the expression rather than before the comparison operator. The following two queries are equivalent:
SQL
select au_lname, au_fname, state from authors where state <> 'CA'
SQL
select au_lname, au_fname, state from authors where not state = 'CA'
Here are the results:
Results
au_lname au_fname state ================================== =============== ===== Ringer Albert UT Ringer Anne UT DeFrance Michel IN Panteley Sylvia MD Greene Morningstar TN del Castillo Innes MI Blotchet-Halls Reginald OR Smith Meander KS [8 rows]
Logical Operator Precedence
Like the arithmetic operators, logical operators are handled according to precedence rules. When both kinds of operators occur in the same statement, arithmetic operators are handled before logical operators. When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Figure 4.5 shows the hierarchy.
){kind=link}
Figure 4.5 Precedence Hierarchy for Logical Operators
Some examples will clarify the situation. The following query finds all the business books in the titles table, no matter what their advances are, as well as all psychology books that have an advance greater than $5,500. The advance condition pertains to psychology books and not to business books because the AND is handled before the OR.
SQL
select title_id, type, advance from titles where type = 'business' or type = 'psychology' and advance > 5500 title_id type advance ======== ============ ============ BU1032 business 5000.00 BU1111 business 5000.00 BU2075 business 10125.00 PS2106 psychology 6000.00 BU7832 business 5000.00 PS1372 psychology 7000.00 [6 rows]
The results include three business books with advances less than $5,500 because the query was evaluated according to the following precedence rules:
Find all psychology books with advances greater than $5,500.
Find all business books (never mind about advances).
Display both sets of rows in the results.
You can change the meaning of the previous query by adding parentheses to force evaluation of the OR first. With parentheses added, the query executes differently:
Find all business and psychology books.
Locate those that have advances over $5,500.
Display only the final subset.
SQL
select title_id, type, advance from titles where (type = 'business' or type = 'psychology') and advance > 5500 title_id type advance ======== ============ ============ BU2075 business 10125.00 PS2106 psychology 6000.00 PS1372 psychology 7000.00 [3 rows]
The parentheses cause SQL to find all business and psychology books and, from among those, to find those with advances greater than $5,500.
Here's a query that includes arithmetic operators, comparison operators, and logical operators. It searches for books that are not bringing in enough money to offset their advances. Specifically, the query searches for any books with gross revenues (that is, ytd_sales times price) less than twice the advance paid to the author(s). The user who constructed this query has tacked on another condition: She wants to include in the results only books published before October 15, 2000, because those books have had long enough to establish a sales pattern. The last condition is connected with the logical operator AND; according to the rules of precedence, it is evaluated after the arithmetic operations.
SQL
select title_id, type, price, advance, ytd_sales from titles where price * ytd_sales < 2 * advance and pubdate < '10/15/2000' title_id type price advance ytd_sales ======== =========== ========= =========== =========== PS2106 psychology 17.00 6000.00 111 [1 row]
SQL VariantS
If you run this query on a system with a different date format, you may need to change the pubdate value to correspond to that format. For example, if your SQL engine expects dates to look like DD-MON-YYYY, you could write the query like this:
Oracle
SQL> select title_id, type, price, advance, ytd_sales 2 from titles 3 where price * ytd_sales < 2 * advance 4 and pubdate < '21 OCT 2000'; TITLE_ TYPE PRICE ADVANCE YTD_SALES ------ ------------ --------- --------- --------- PS2106 psychology 17 6000 111
Ranges (BETWEEN and NOT BETWEEN)
Another common search condition is a range. There are two different ways to specify ranges:
With the comparison operators > and <
With the keyword BETWEEN
Use BETWEEN to specify an inclusive range, in which you search for the lower value and the upper value as well as the values they bracket. For example, to find all the books with sales between (and including) 4,095 and 12,000, you could write this query:
SQL
select title_id, ytd_sales from titles where ytd_sales between 4095 and 12000 title_id ytd_sales ======== =========== PC8888 4095 BU1032 4095 TC7777 4095 PC1035 8780 BU7832 4095 [5 rows]
Notice that books with sales of 4,095 are included in the results. If there were any with sales of 12,000, they would be included too. In this way, the BETWEEN range is different from the greater-than/less-than (> <) range. The same query using the greater-than and less-than operators returns different results because the range is not inclusive:
SQL
select title_id, ytd_sales from titles where ytd_sales > 4095 and ytd_sales < 12000 title_id ytd_sales ======== =========== PC1035 8780 [1 row]
NOT BETWEEN
The phrase NOT BETWEEN finds all the rows that are not inside the range. To find all the books with sales outside the range of 4,095 to 12,000, type this:
SQL
select title_id, ytd_sales from titles where ytd_sales not between 4095 and 12000 title_id ytd_sales ======== =========== PS7777 3336 PS3333 4072 BU1111 3876 MC2222 2032 TC4203 15096 BU2075 18722 PS2091 2045 PS2106 111 MC3021 22246 TC3218 375 PS1372 375 [11 rows]
You can get the same results with comparison operators, but notice in this query that you use OR between the two ytd_sales comparisons rather than AND.
SQL
select title_id, ytd_sales from titles where ytd_sales < 4095 or ytd_sales > 12000 title_id ytd_sales ======== =========== PS7777 3336 PS3333 4072 BU1111 3876 MC2222 2032 TC4203 15096 BU2075 18722 PS2091 2045 PS2106 111 MC3021 22246 TC3218 375 PS1372 375 [11 rows]
This is another case where it's easy to get confused because of the way the question can be phrased in English. You might ask to see all books whose sales are less than 4,095 and all books whose sales are greater than 12,000. The logical meaning, however, calls for the use of the Boolean operator OR. If you substitute AND, you'll get no results at all because no book can have sales that are simultaneously less than 4,095 and greater than 12,000.
Lists (IN and NOT IN)
The IN keyword allows you to select values that match any one of a list of values. For example, without IN, if you want a list of the names and states of all the authors who live in California, Indiana, or Maryland, you can type this query:
SQL
select au_lname, state from authors where state = 'CA' or state = 'IN' or state = 'MD'
However, you get the same results with less typing if you use IN. The items following the IN keyword must be
inside parentheses
separated by commas
enclosed in quotes, if they are character or date values
SQL
select au_lname, state
from authors
where state in ('CA', 'IN', 'MD')
Following is what results from either query:
Results
au_lname state ======================================== ===== Bennet CA Green CA Carson CA DeFrance IN Panteley MD McBadden CA Stringer CA Straight CA Karsen CA MacFeather CA Dull CA Yokomoto CA O'Leary CA Gringlesby CA White CA Hunter CA Locksley CA [17 rows]
The more items in the list, the greater the savings in typing by using IN rather than specifying each condition separately.
An important use for the IN keyword is in nested queries, also referred to as subqueries. For a full discussion of subqueries, see Chapter 8.
Selecting Null Values
From earlier chapters ("NULLs" in Chapter 1), you may recall that NULL is a placeholder for unknown information. It does not mean zero or blank.
To clarify this NULL–zero difference, take a look at the following listing showing title and advance amount for books belonging to one particular publisher.
SQL
select title, advance from titles where pub_id = '0877' title advance ============================================================= ======= Silicon Valley Gastronomic Treats 0.00 Sushi, Anyone? 8000.00 Fifty Years in Buckingham Palace Kitchens 4000.00 The Gourmet Microwave 15000.00 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 7000.00 The Psychology of Computer Cooking (NULL) [6 rows]
A cursory perusal shows that one book (Silicon Valley Gastronomic Treats) has an advance of $0.00, probably due to extremely poor negotiating skills on the author's part. This author will receive no money until the royalties start coming in. Another book (The Psychology of Computer Cooking) has a NULL advance: Perhaps the author and the publisher are still working out the details of their deal, or perhaps the data entry clerk hasn't made the entry yet. Eventually, in this case, an amount will be known and recorded. Maybe it will be zero, maybe millions, maybe a couple of thousand dollars. The point is that right now the data does not disclose what the advance for this book is, so the advance value in the table is NULL.
What happens in the case of comparisons involving NULLs? Since a NULL represents the unknown, it doesn't match anything, even another NULL. For example, a query that finds all the title identification numbers and advances for books with moderate advances (under $5,000) will not find the row for MC3026, The Psychology of Computer Cooking.
SQL
select title_id, advance from titles where advance < $5000 title_id advance ======== ============ PS7777 4000.00 PS3333 2000.00 MC2222 0.00 TC4203 4000.00 PS2091 2275.00 [5 rows]
Neither will a query for all books with an advance over $5,000:
SQL
select title_id, advance from titles where advance > $5000 title_id advance ======== ============ PC8888 8000.00 TC7777 8000.00 PC1035 7000.00 BU2075 10125.00 PS2106 6000.00 MC3021 15000.00 TC3218 7000.00 PS1372 7000.00 [8 rows]
TIP
NULL is neither above nor below (nor equal to) $5,000 because NULL is unknown.
IS NULL
But don't despair! You can retrieve rows on the basis of their NULL/NOT NULL status with the following special pattern:
SYNTAX
WHERE column_name IS [NOT] NULL
Use it to find the row for books with null advances like this:
SQL
select title_id, advance from titles where advance is null title_id advance ======== ============ MC3026 (NULL) PC9999 (NULL) [2 rows]
SQL Variants
Some systems allow the equal sign, in addition to "is".
Adaptive Server Enterprise
select title_id, advance from titles where advance = null
Since IS NULL is specified in the ANSI standard, it makes sense to use it, rather than use the less common = NULL.
IS NULL and Other Comparison Operators
You can use the IS NULL pattern in combination with other comparison operators. Here's how a query for books with an advance under $5,000 or a null advance would look:
SQL
select title_id, advance from titles where advance < $5000 or advance is null title_id advance ======== ============ PS7777 4000.00 PS3333 2000.00 MC2222 0.00 TC4203 4000.00 PS2091 2275.00 MC3026 (NULL) PC9999 (NULL) [7rows]
Matching Character Strings: LIKE
Some problems can't be solved with comparisons. Here are a few examples:
"His name begins with 'Mc' or 'Mac'—I can't remember the rest."
"We need a list of all the 415 area code phone numbers."
"I forget the name of the book, but it has a mention of exercise in the notes."
"Well, it's Carson, or maybe Karsen—something like that."
"His first name is 'Dirk' or 'Dick.' Four letters, starts with a D and ends with a k."
In each of these cases, you know a pattern embedded somewhere in a column, and you need to use the pattern to retrieve all or part of the row. The LIKE keyword is designed to solve this problem. You can use it with character fields (and on some systems, with date fields). It doesn't work with numeric fields defined as integer, money, and decimal or float. The syntax is this:
SYNTAX
WHERE column_name [NOT] LIKE 'pattern' [ESCAPE escape_char]
The pattern must be enclosed in quotes and must include one or more wildcards (symbols that take the place of missing letters or strings in the pattern). You use the ESCAPE keyword when your pattern includes one of the wildcards and you need to treat it as a literal.
ANSI SQL provides two wildcard characters for use with LIKE, the percent sign (%) and the underscore or underbar (_).
|
Wildcard |
Meaning |
|
% |
any string of zero or more characters |
|
_ |
any single character |
SQL Variants
Many systems offer variations (notations for single characters that fall within a range or set, for example). Check your system's reference guide to see what's available.
LIKE Examples
Following are answers to the questions just posed and the queries that generated them. First, the search for Scottish or Irish surnames:
SQL
select au_lname, city from authors where au_lname like 'Mc%' or au_lname like 'Mac%' au_lname city ====================================== ================= McBadden Vacaville MacFeather Oakland [2 rows]
The LIKE pattern instructs the system to search for a name that begins with "Mc" and is followed by a string of any number of characters (%) or that begins with "Mac" and is followed by any number of characters. Notice that the wildcard is inside the quotes.
Now the 415 area code list:
SQL
select au_lname, phone from authors where phone like '415%' au_lname phone ======================================== ============ Bennet 415 658-9932 Green 415 986-7020 Carson 415 548-7723 Stringer 415 843-2991 Straight 415 834-2919 Karsen 415 534-9219 MacFeather 415 354-7128 Dull 415 836-7128 Yokomoto 415 935-4228 Hunter 415 836-7128 Locksley 415 585-4620 (11 rows affected)
Here again, you're looking for some known initial characters followed by a string of unknown characters.
The book with "exercise" somewhere in its notes is a little trickier. You don't know if it's at the beginning or end of the column, and you don't know whether the first letter of the word is capitalized. You can cover all these possibilities by leaving the first letter out of the pattern and using the same "string of zero or more characters" wildcard at the beginning and end of the pattern.
SQL
select title_id, notes
from titles
where notes like '%xercise%'
title_id notes
======== ======================================================
PS2106 New exercise, meditation, and nutritional techniques
that can reduce the shock of daily interactions.
Popular audience. Sample menus included, exercise
video available separately.
[1 row]When you know the number of characters missing, you can use the single-character wildcard, (_). In the next example, the first letter is either K or C and the next to the last is either e or o. If the authors table contained the last name Karson, it would also be included in the results. Starson or Karstin would not.
SQL
select au_lname, city from authors where au_lname like '_ars_n' au_lname city ======================================== ==================== Carson Berkeley Karsen Oakland (2 rows affected)
The next example is similar to the previous one. It looks for four-letter first names starting with D and ending with k.
SQL
select au_lname, au_fname, city from authors where au_fname like 'D_ _k' au_lname au_fname city =================================== ================== ============== Stringer Dirk Oakland Straight Dick Oakland [2 rows]
NOT LIKE
You can also use NOT LIKE with wildcards. To find all the phone numbers in the authors table that do not have 415 as the area code, you could use either of these queries (they are equivalent):
SQL
select phone from authors where phone not like '415%' select phone from authors where not phone like '415%'
Escaping
Wildcard characters are almost always used together with the LIKE keyword. Without LIKE, the wildcard characters are interpreted literally and represent exactly their own values. The query that follows finds any phone numbers that consist of the four characters "415%" only. It will not find phone numbers that start with 415:
SQL
select phone from authors where phone = '415%'
What if you want to search for a value that contains one of the wildcard characters? For example, in one row in the titles table, the notes column contains a claim to increase readers' friends by some percentage. You can search for the percent mark by using ESCAPE to appoint a character to strip the percent sign of its magic meaning and convert it to an ordinary character. A wildcard directly after the escape character has only its literal meaning. Other wildcards continue to have their special significance. In the following LIKE expression, you are looking for a literal percent sign somewhere in the notes column. Since it's probably not the first or last character, you use wildcard percent signs at the beginning and end of the expression and a percent sign preceded by the escape character in the middle.
SQL
select title_id, notes
from titles
where notes like '%@%%' escape '@'
title_id notes
====== =======================================================
TC7777 Detailed instructions on improving your position in
life by learning how to make authentic Japanese sushi
in your spare time. 5-10% increase in number of
friends per recipe reported from beta test.
[1 row]
Following are some examples of LIKE with escaped and unescaped wildcard character searches (the @ sign is the designated escape character):
|
Symbol |
Meaning |
|
LIKE '27%' |
27 followed by any string of 0 or more characters |
|
LIKE '27@%' |
27% |
|
LIKE '_n' |
an, in, on, etc. |
|
LIKE '@_n' |
_n |
Like, Is IN LIKE Equals . . . ?
Don't get confused by the similarities of equal, IN, and LIKE.
Equals
Use the equal comparison operator when you want all data that exactly matches a single value—you know just what you are looking for. You can use the equal comparison operator with any kind of data—character, date, or numeric. Put quotes around character and date data. In this query, you are looking for authors named "Meander."
SQL
select au_lname, au_fname, phone from authors where au_fname = 'Meander' au_lname au_fname phone ============================= ============= ============ Smith Meander 913 843-0462 [1 row]
IN
Use IN when you have two or more values and are looking for data that exactly matches any one of these values. IN works with any kind of data—character, date, or numeric. Put quotes around character and date data. Here, you are trying to find any writers called "Meander," "Malcolm," or "Stearns."
SQL
select au_lname, au_fname, phone from authors where au_fname in ( 'Meander', 'Malcolm', 'Stearns') au_lname au_fname phone ============================= ============= ============ MacFeather Stearns 415 354-7128 Smith Meander 913 843-0462 [2 rows]
LIKE
Use LIKE when you want to find data that matches a pattern. For example, if you are trying to locate all the people with the letters "ea" in their names, you could write code like this:
SQL
select au_lname, au_fname, phone from authors where au_fname like '%ea%' au_lname au_fname phone ============================= ============= ============ McBadden Heather 707 448-4982 MacFeather Stearns 415 354-7128 Smith Meander 913 843-0462 [3 rows]
In most cases, LIKE works with character and date data only.
SQL Variants
Some systems support autoconvert capabilities that allow you to use LIKE with numeric data. Notice that you have to put quotes around the pattern, just as if it were character:
Oracle
SQL> select title_id, price 2 from titles 3 where price like '%.99' TITLE_ PRICE ------ --------- BU1032 29.99 PS7777 17.99 PS3333 29.99 MC2222 29.99 TC7777 24.99 BU2075 12.99 MC3021 12.99 BU7832 29.99 8 rows selected.
Other systems give an error for the same code:
SQL Server
select title_id, price from titles where price like '%.99' Server: Msg 257, Level 16, State 3, Line 1 Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.
Comparing the Three
The guidelines for differentiating among equal, IN, and LIKE are compared and summarized in Figure 4.6.
){kind=link}
Figure 4.6 Equal, IN, LIKE