SQL gets complex when you have multiple business rules that run how you return record sets. As a coder, you gather business rules that then decide how to structure your SQL statements to ensure that returned results are accurate for reports and applications. These statements get complex when you have several business requirements that must be used to return a data set that helps businesses make decisions. Some SQL keywords that help you build complex statements include IN, NOT, and LIKE. LIKE uses wildcards, which are used to query similar values, but IN and NOT return precise record sets based on specific values. 

The IN Condition

The IN condition lets you set a list of values that must match values in your tables. The IN condition lists values in parenthesis, and it's better than working with multiple OR conditions. Here is sample table. 

CustomerId	First_name	Last_name	City	State
321	Frank	Loe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX

Suppose you want to return values for customers that are only in Atlanta and Miami. You could write an OR statement that looks like the following.

SELECT * FROM Customer

WHERE City = ‘Miami' OR City = ‘Atlanta' 

Imagine you have 10 of these cities you need to find. The OR statement gets long and complex. You can combine these OR statements into an IN statement. The following SQL statement does the same as the above statement. 

SELECT * FROM Customer

WHERE City IN (‘Miami', ‘Atlanta') 

The result of both statements is the same. The following result is displayed by SQL. 

CustomerId	First_name	Last_name	City	State
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL

The NOT Clause

You might return 1000 records but want to exclude some records within the data set. So far, we've only created SQL statements where you want to include records. You sometimes need to exclude records from your results. Suppose you want to include records with city values of Atlanta and Miami, but you want to exclude records where the user's first name is Joe. SQL has a NOT operator that excludes those records while still including the others that match the original IN query. The following query gives you an example of the NOT operator. 

SELECT * FROM Customer

WHERE City IN (‘Miami', ‘Atlanta') AND First_name NOT IN (‘joe') 

The above statement says "give me all records that have city values of Miami and Atlanta but exclude any of these records that have a first name of Joe." The result record set is the following. 

CustomerId	First_name	Last_name	City	State
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA

Notice how the results look similar to the query for the IN statement. The one difference is that the "Joe" record is excluded. Since "Joe" matched the NOT statement, it's excluded from the results. This statement builds on the AND logic. Since AND is used, the result set must match both conditions with true to be included in the results. 

The SQL language lets you combine NOT and LIKE to eliminate search results using the same type of logic except records are removed from a data set instead of adding them. For instance, instead of searching for customers in cities that start with "Da," you can exclude customers that are located in those cities. The following SQL statement uses the NOT keyword with the LIKE keyword. 

SELECT * FROM Customer

WHERE City NOT LIKE ‘da%' 

The result is the following data results. 

CustomerId	First_name	Last_name	City	State
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL

The NOT operator works with all of your SQL keywords to negate results. Think of the NOT operator as a cancellation phrase. When you use the IN operator, the SQL language first gets all of the values that match. In this case, the parameters are any record that starts with the characters "da." SQL then sees the NOT operator, and eliminates the results. You can combine the NOT operator with other phrases. 

For instance, suppose you want to get all records that start with "da," but you want to eliminate any records that start with "dal." The following SQL statement would eliminate "dal" cities from your results. 

SELECT * FROM Customer

WHERE City LIKE ‘da%' AND City NOT LIKE ‘dal%' 

Notice the AND operator in the above statement. If you place an OR in your statement instead, you would again get all customers located anywhere. With the AND operator, you tell SQL to only give results that return true for both phrases. Therefore, the values must have "da" as the first two characters, but SQL also eliminates any cities that start with "dal." In this example, no records return, because all records do not match both phrases. 

Suppose you placed an OR operator instead of an AND in your statement. The following statement uses OR instead of AND. 

SELECT * FROM Customer

WHERE City LIKE ‘da%' OR City NOT LIKE ‘dal%' 

The OR condition would change your SQL statement to "return all records that either start with ‘da' OR records that do not start with ‘dal'." This logic is a bit more complex. The results are that all records with "da" are returned, which means that your "dallas" customers are returned. Next, the second phrase tells SQL to return all records that do not start with "dal." In your example table, all records do not start with the characters "dal," so SQL returns all records. The first phrase doesn't matter, because OR is used and all of your records match the second phrase. 

As a result, SQL returns the following data set. 

CustomerId	First_name	Last_name	City	State
321	Frank	Loe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX

Understanding the difference between the OR and AND operators and using these with the NOT operator will help you create complex SQL statements that give you the exact data set you need to display in your applications and run reports. 

The LIKE Operator

The LIKE operator has already been discussed a bit, but the LIKE operator has several other syntax options that you can use. The LIKE operator uses the wildcard % character. The % character can be placed at the beginning, end or within your string value. Note that the % operator is for string or varchar values. 

The above examples use the percent character to return values that start with a specific string (in the above examples, the string was "da"). What if you need to find values that just contain a value? For instance, you could want to return all customers that just contain the string "ia." Again, we start off with the Customer database table. 

CustomerId	First_name	Last_name	City	State
321	Frank	Loe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX

The following SQL statement looks for any customer in a city that contains the value "ia."

SELECT * FROM Customer

WHERE City LIKE ‘%ia%'

The above statement translates to "give me all records where the city value contains the "ia" value anywhere in the string. Notice the percent symbol at the beginning and the end. This is different than the previous examples that just used "da%." The "da%" tells SQL that the string must start with "da" and can't include any prefixed characters. The "%ia%" statement tells SQL that the "ia" characters can be anywhere in the string.

The data set result is the following. 

CustomerId	First_name	Last_name	City	State
457	Joe	Smith	Miami	FL

The underscore character ( _ ) is another wildcard character used to tell SQL that only one character can be prefixed or end with a particular string sequence. For instance, the following SQL statement returns only records that have one character before "iami." 

SELECT * FROM Customer

WHERE City LIKE ‘_iami' 

The difference between the percent character and the underscore is that the percent character tells SQL that any number of characters can precede or follow the given sequence but the underscore means only one character can precede or follow the sequence. The same record set is returned as the above. However, let's say you had a city with the value of "Hmiami" in the Customer table. This record would be excluded, because more than one character precedes the given sequence. 

Using the first table of Customer data listed in this article, you again have the following result set from the above SQL statement. 

CustomerId	First_name	Last_name	City	State
457	Joe	Smith	Miami	FL

The NOT, LIKE and IN operators are powerful ways to query records with more complexity in your SQL statements. These operators can help you return a more precise record set than more simple WHERE clause phrases.