Access to the expression generator where clause in sas

WHERE clause in detail

(Introduction to SQL: WHERE Clause in Detail)

This chapter covers the details of the WHERE clause in more detail. This information is particularly important for the SELECT command, but also for UPDATE and DELETE.

The examples refer to the initial inventory of the sample database; the selected data records are not output. Please try all of the examples and make various changes to see the effects.

General notes [edit]

The WHEREClause is the most important part of the SELECT command (in addition to linking several tables): The more carefully the selection conditions are formulated, the more precise the result of the query.

In addition to the variants explained here, each DBMS also offers others, e.g. B. STARTING WITH or SIMILAR.

Instead of constant values, suitable expressions can also be specified, e.g. B. functions or subqueries.

Always use the reverse of the selection with or without NOT in the examples. As stated in the chapter "Detailed SELECT structure" for the WHERE clause, the NOT may appear immediately in front of the parameter name.

A single condition [edit]

Size comparison of two values ​​[edit]

The simplest way is to directly compare two values, namely the content of a column with a constant value. This is possible with the following Comparison operators, for all data types that can be compared - numbers, strings, dates.

= < > <= >= <>

Examples:

Find a record for which the value in the column ID is equal to a given value.

select * fromVersichernehmerwhereID = 10;

Search for data records where the name is smaller than 'B', i.e. starts with 'A'.

select * fromVersichernehmerwhereName <'B';

Looking for new drivers.

select * fromVersicherungsnehmerwhereFuehrerschein> = '01.01.2007';

Search vehicle types with a short description.

select * fromVahrzeugtypwhereChar_Length (description) <= 3;

In these comparisons, NOT is also possible; but another suitable operator is easier to understand.

BETWEEN AND - values ​​between two limits [edit]

Under the condition BETWEEN <wert1> AND is compared directly with a range; the limit values ​​mostly belong to the range (depending on the DBMS). This is also possible for numbers, strings, dates.

Search for records in which the postcode is outside the range 45000 ... 45999.

select * fromVersichernehmerwherePLZNOTBETWEEN'45000'AND'45999 ';

LIKE - similarities (1) [edit]

The LIKE-Condition compares strings "imprecisely": The searched text should be contained as a value in a column; "Wildcards" are used for this: The underscore '_' stands for any single character that can appear in the relevant position. The percent sign '%' stands for any character string with 0 or more characters.

This condition is particularly popular in two situations:

  • The search term is very long; it should suffice for the user to enter the beginning.
  • The search term is not exactly known (e.g. not legible).

Examples:

The place name does not begin with 'B'; the content behind it is arbitrary.

select * fromVersichernehmerwhereOrtNOTLIKE'B% ';

The place name contains 'old' somewhere with any content before and after it.

select * fromVersichernehmerwhereOrtLIKE '% alt%';

The first letter of the name is unclear, but it is followed by the letters 'ei' and a little more.

select * from PolicyholderwhereNameLIKE'_ei% ';

We have a problem when one of the wildcard characters should be part of the search term. Then the LIKE parameter must be told that '%' or '_' is to be understood as a "real" character. This is done by placing a special character in front of it and specifying this character as an "ESCAPE character":

The character string '10% 'occurs within the description.

select * fromSchadensfallwhereDeschbeschreibung LIKE '% 10 \ %%' ESCAPE '\';

The first and last percent signs indicate that before and after any content is possible. The middle percent sign is connected with the escape character '\' and is therefore part of the search string. This specification '\%' is as a Understand signs.

Compare the query result if the ESCAPE parameter is omitted or if one or more of the special characters in the LIKE parameter are missing.

CONTAINS et al. - Similarities (2) [edit]

One problem with the LIKE parameter is the use of the wildcard characters '%' and '_', which are easily forgotten or (as in the last example) not paid enough attention to. Therefore there are various simplifications.

CONTAINS - in Firebird CONTAINING - Checks whether a character string is contained in the field content.

select * fromSchadensfallwhereDescriptionCONTAINS'10% ';
Part of the description is the string '10% '

Please check in the description of your DBMS which options are also offered for the search for similarities.

IS NULL - check null values ​​[edit]

As already discussed with the relational databases, NULL values ​​have a special meaning. The following two queries cannot find all records:

selectID, surname, first name, mobile fromMitarbeiterwhereMobil <> '';
8 employees with mobile numbers
selectID, Surname, First Name, MobilfromMitarbeiterwhereMobil = '';
10 employees without a mobile number

Well, there are 28 employees; where are the rest of them? For these cases there is with IS NULL a special query:

selectID, surname, first name, mobile fromMitarbeiterwhereMobilisnull;
10 employees without information

For the sake of completeness, it should be noted that the following query actually provides the correct cross-check.

selectID, surname, first name, mobile fromMitarbeiterwhereMobilisnotnull;
18 employees with any information (also with "empty" information)

The following query returns an empty result set because it is NULL no value is.

selectID, surname, first name, mobile fromMitarbeiterwhereMobil = null;

There are no single condition, which shows all data records without explicit mobile information at once. There is only the possibility of combining the two conditions "IS NULL" and "is empty":

selectID, Surname, First Name, MobilfromMitarbeiterwhere (Mobilisnull) or (Mobil = '');
20 employees without express information

Also note the meaning of NULL for "WHERE ... IS [NOT] NULL":

  • In the case of character strings, a distinction must be made between the "empty" string and the NULL value.
  • In the case of numbers, a distinction must be made between the number '0' (zero) and the NULL value.
  • When specifying dates, a distinction must be made between an existing date and the NULL value; there is no date that corresponds to the number 0. (At best, you could use the smallest possible date such as '01.01.0100 ', but this is already a date.)

IN - exact comparison with a list [edit]

The INParameter compares whether the content of a column is contained in the specified list. The list can work with any data type.

Get the list of all vehicles whose types are registered as "VW small cars".

select * fromVahrzeugwhereFahrzeugtyp_IDin (1,2);

Look for an accident vehicle with one of several possible colors.

select * fromVahrzeugwhereFarbein ('ocher', 'yellow');

The first example in particular is often provided with a subquery; compare also the following section on EXISTS.

Get the list of all vehicles of the type "Volkswagen".

select * fromFahrzeugwhereFahrzeugtyp_IDin (selectIDfromFahrzeugtypwhereHersteller_ID = 1);

First, a list of all vehicle type IDs for manufacturer 1 (= Volkswagen) is compiled with the sub-query; this is then used for the comparison via the IN parameter.

EXISTS - quick comparison with a list [edit]

In contrast to the other parameters of the WHERE clause, the EXISTS-Parameters not with fixed values, but only with the result of a query, i.e. a subquery. The last example for the IN parameter can also be formulated like this:

List of all vehicles of the type 'Volkswagen'
select * fromFahrzeugfzwhereEXISTS (select * fromVahrzeugtypftwhereft.Hersteller_ID = 1andfz.Fahrzeugtyp_ID = ft.ID);

For each data record from the table vehicle a sub-query is created from the vehicle types for this vehicle type_ID: If there is a data record with a matching ID and manufacturer ID 1 (= Volkswagen), the vehicle data record is available for selection, otherwise not.

Since subqueries are carried out first, an EXISTS check is usually completed faster than the corresponding IN check: EXISTS is a determination of “something is there at all”; with IN, however, an exact comparison must be made with all values ​​in a list. With our small sample database, of course, it doesn't matter, but with a "real" database with millions of entries it does.

Link several conditions [edit]

The WHERE clause is about determining whether or not a record is part of the query result; The is therefore a Boolean expression, i. H. an expression that has one of the Boolean values ​​TRUE or FALSE - TRUE or FALSE - as a result. A single condition is only sufficient for simple queries; usually several conditions have to be linked (as in the last example under IS NULL).

There are Boolean operators for this NOT, AND, OR.

NOT as negation

This operator reverses the result: TRUE becomes FALSE, FALSE becomes TRUE.

See above: no novice drivers
SELECT * FROM PolicyholderWHERENOT (driver's license> = '01.01.2007');

AND as conjunction

A condition that is formed by an AND link is TRUE if both (or all) components are TRUE.

The first half of the policyholders in a postal code area in alphabetical order
SELECTID, surname, first name, zip code, city FROM Policyholder WHEREPLZBETWEEN'45000'AND'45999'ANDName <'K';

OR as adjunct [edit]

A condition that is formed by an OR link is TRUE if at least one component is TRUE; It does not matter whether the other components are TRUE or FALSE.

The first half of the policyholders in alphabetical order and all of a zip code area
SELECTID, surname, first name, zip code, cityFROM Policyholder WHEREPLZBETWEEN'45000'AND'45999'ORName <'K';

Please note that normal usage says "all ... and all ...". According to logical terms, however, it means that must be fulfilled OR <Bedingung 2> OR BOTH.

XOR as contravalence [edit]

A condition that is formed by an XOR link is TRUE if and only if one component is TRUE, but the other component is FALSE - "exclusive or" or "either - or". This link is rare, e.g. B. MySQL; it is mentioned here for the sake of completeness.

MySQL version: The first half of the policyholders in alphabetical order or all of a postcode area
SELECTID, surname, first name, zip code, city FROM Policyholder WHEREPLZBETWEEN'45000'AND'45999'XORName <'K';

Please note that the normal usage here says “or” and “either - or” is meant.

A combination can always be used instead of XOR:

( AND (NOT )) OR ( AND (NOT ))

Use or omit parentheses? [Edit]

The hierarchy was already mentioned in the chapter "Detailed SELECT structure":

  • NOT is the closest connection and is evaluated with priority.
  • AND is the next strongest connection and is then evaluated.
  • OR is the weakest connection and is evaluated last.
  • What is in brackets is evaluated before everything else.

In the example below, please put parentheses elsewhere or delete parentheses and compare the results.

SELECTID, surname, first name, zip code, placeFROMInsurance holderWHEREnot (PLZBETWEEN'45000'AND'45999'AND (NameLIKE'B% 'ORNameLIKE'K%' ORNOTNameCONTAINING'ei ')) orderbyPLZ, name;

You will see quite different results. It is therefore advisable to put brackets in all sensible places - even where they are not required - and to structure what belongs together with indentations.

Summary [edit]

In this chapter, in addition to comparing values, we learned about many ways to set conditions for queries:

  • BETWEEN AND checks values ​​within a range.
  • LIKE and CONTAINS are used to search for values ​​that partially match the specified values.
  • IS NULL searches for null values.
  • IN and EXISTS are used to compare column values ​​with a list.

Conditions are combined with AND, OR, NOT.

Exercises [edit]

The following tasks only depend on the WHERE clause; You can use a SELECT "with all columns".

Find all policyholders who meet the following conditions:

  • The first letter of the surname is unknown, the second is an 'r'.
  • The first name contains an 'a'.
  • The postcode belongs to the Essen area (postcode 45 ...).

Find all policyholders who turned 18 between 1967 and 1970.

Show all cases of damage for which a percentage is indicated in the description.

Show all company cars that are not personally assigned to any employee.

Note: The “Employee without a company car” test is more complicated; the OUTER JOIN required for this will only be dealt with later.

Show all employees of the departments "Sales" (= 'Vert') and "Training" (= 'Bildung').

Note: First determine the IDs of the departments you are looking for and use the result for the actual query.

We are looking for the insurance contracts for liability (= 'HP') and partially comprehensive (= 'TK') that have existed at least since the end of 1980 and are currently not calculated with the minimum premium rate.

Note: As an exception, only enter the necessary brackets, not all the useful ones.

Solution to exercise 1Selection by character stringsTo practise
select * fromVersicherungsnehmerwhereNamelike'_r% 'andVornamelike'% a% 'andPLZSTARTINGWITH'45' / * or: * / PLZlike'45% ';
Solution to exercise 2Selection by date rangeTo practise
select * fromVersicherungsnehmerwhereDATEADD (YEAR, 18, date of birth) BETWEEN'01.01.1967'AND'31.12.1970 ';
Solution to exercise 3Selection based on similarityTo practise
SELECT * fromSchadensfallwhereDescriptionlike '% \ %%' escape '\';
Solution to exercise 4Selection for unknown valuesTo practise
SELECT * fromDienstwagenwhereMitarbeiter_IDisnull;
Solution to exercise 5Link conditionsTo practise
SELECT * fromMitarbeiterwhereAbteilung_IDin (selectidfromAbteilungwhereKuerzelin ('Vert', 'ISBN'));
Solution to exercise 6Link conditionsTo practise
SELECT * fromVersicherungsvertragwhere (Art = 'HP'orArt =' TK ') andAbschlussdate <=' 31.12.1980'and (notPraemiensatz = 30) / * or * / andPraemiensatz> 30;

See also [edit]

This chapter refers to the following chapters:

There are further technical information on Wikipedia: