WHERE City LIKE ‘da%' OR City NOT LIKE ‘dal%' The following statement uses OR instead of AND. Suppose you placed an OR operator instead of an AND in your statement. 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. With the AND operator, you tell SQL to only give results that return true for both phrases. If you place an OR in your statement instead, you would again get all customers located anywhere. Notice the AND operator in the above statement. WHERE City LIKE ‘da%' AND City NOT LIKE ‘dal%' You can combine the NOT operator with other phrases.įor 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. In this case, the parameters are any record that starts with the characters "da." SQL then sees the NOT operator, and eliminates the results. When you use the IN operator, the SQL language first gets all of the values that match. Think of the NOT operator as a cancellation phrase. The NOT operator works with all of your SQL keywords to negate results. The result is the following data results. The following SQL statement uses the NOT keyword with the LIKE keyword.
For instance, instead of searching for customers in cities that start with "Da," you can exclude customers that are located in those cities. 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. Since AND is used, the result set must match both conditions with true to be included in the results. Since "Joe" matched the NOT statement, it's excluded from the results. The one difference is that the "Joe" record is excluded. Notice how the results look similar to the query for the IN statement. 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. WHERE City IN (‘Miami', ‘Atlanta') AND First_name NOT IN (‘joe') The following query gives you an example of the NOT operator. SQL has a NOT operator that excludes those records while still including the others that match the original IN query. 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. You sometimes need to exclude records from your results. So far, we've only created SQL statements where you want to include records. You might return 1000 records but want to exclude some records within the data set. The following result is displayed by SQL. The result of both statements is the same.
The following SQL statement does the same as the above statement. You can combine these OR statements into an IN statement. Imagine you have 10 of these cities you need to find. You could write an OR statement that looks like the following. Suppose you want to return values for customers that are only in Atlanta and Miami. The IN condition lists values in parenthesis, and it's better than working with multiple OR conditions. The IN condition lets you set a list of values that must match values in your tables. LIKE uses wildcards, which are used to query similar values, but IN and NOT return precise record sets based on specific values. Some SQL keywords that help you build complex statements include IN, NOT, and LIKE. These statements get complex when you have several business requirements that must be used to return a data set that helps businesses make decisions.
#Sql not equal to multiple values how to
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. SQL gets complex when you have multiple business rules that run how you return record sets.
0 kommentar(er)
