A predicate is used in either the WHERE or HAVING clauses of a SQL statement. It specifies a condition that true, false, or unknown about a row or a group.
As a rule, a query will return the same result regardless of the sequence in which the various predicates are specified. However, note the following:
A basic predicate compares two values. If either value is null, the result is unknown. Otherwise the result is either true or false.
Basic Predcate syntax, 1 of 2
Basic Predicate eample
A variation of this predicate type can be used to compare sets of columns/values. Everything on both sides must equal in order for the expressions to match:
Basic Predicate syntax, 2 of 2
Below is the same query written the old fashioned way
Same query prior, using individual predicates
A quantified predicate compares one or more values with a collection of values.
Quantified Predicate syntax
Quantified Predicate example, two single-value sub-queries10 Mgr
Quantified Predicate example, multi-value sub-query
The BETWEEN predicate compares a value within a range of values.
BETWEEN predicate syntax
The between check always assumes that the first value in the expression is the low value and the second value is the high value. For example, BETWEEN 10 AND 12 may find data, but
BETWEEN Predicate examples
An EXISTS predicate tests for the existence of matching rows.
EXISTS Predicate syntax
EXISTS Predicate example
The IN predicate compares one or more values with a list of values.
IN Predicate syntax
The list of values being compared in the IN statement can either be a set of in-line expressions (e.g. ID in (10,20,30)), or a set rows returned from a sub-query. Either way, DB2 simply goes through the list until it finds a match.
IN Predicate example, single values
The IN statement can also be used to compare multiple fields against a set of rows returned from a sub-query. A match exists when all fields equal. This type of statement is especially useful when doing a search against a table with a multi-columns key.
WARNING: Be careful when using the NOT IN expression against a sub-query result. If any one row in the sub-query returns null, the result will be no match.
IN Predicate example, multi-valueLIKE Predicate
The LIKE predicate does partial checks on character strings.
LIKE Predicate syntax
The percent and underscore characters have special meanings. The first means skip a string of any length (including zero) and the second means skip one byte. For example:
LIKE Predicate example
The ESCAPE Phrase
The escape character in a LIKE statement enables one to check for percent signs and/or underscores in the search string. When used, it precedes the '%' or '_' in the search string indicating that it is the actual value and not the special character which is to be checked for. When processing the LIKE pattern, DB2 works thus: Any pair of escape characters is treated as the literal value (e.g. "++" means the string "+"). Any single occurrence of an escape character followed by either a "%" or a "_" means the literal "%" or "_" (e.g. "+%" means the string "%"). Any other "%" or "_" is used as in a normal LIKE pattern.
LIKE and ESCAPE exampleLIKE STATEMENT TEXT WHAT VALUES MATCH
Now for sample SQL:
LIKE and ESCAPE example
The NULL predicate checks for null values. The result of this predicate cannot be unknown. If the value of the expression is null,the result is true. If the value of the expression is not null, the result is false.
NULL Predicate syntax
NULL Predicate example
Special Character Usage
To refer to a special character in a predicate, or anywhere else in a SQL statement, use the "X" notation to substitute with the ASCII hex value. For example, the following query will list all names in the STAFF table that have an "a" followed by a semi-colon:
Refer to semi-colon in SQL text
Expressions within parentheses are done first, then prefix operators (e.g. -1), then multiplication and division, then addition and subtraction. When two operations of equal precedence are together (e.g. 1 * 5 / 4) they are done from left to right.
Precedence rules exampleExample: 555 + -22 / (12 - 3) * 66 ANSWER
Be aware that the result that you get depends very much on whether you are doing integer or decimal arithmetic. Below is the above done using integer numbers:
Precedence rules, integer eampleANSWER
DB2 truncates, not rounds, when doing integer arithmetic.
Here is the same done using decimal numbers:
Precedence rules, decimal exampleANSWER
AND operations are done before OR operations. This means that one side of an OR is fully processed before the other side is begun. To illustrate:
The various parts of a SQL statement are always executed in a specific sequence in order to avoid semantic ambiguity:FROM clause
Query Processing Sequence
Observe that ON predicates (e.g. in an outer join) are always processed before any WHERE predicates (in the same join) are applied. Ignoring this processing sequence can cause what looks like an outer join to run as an inner join . Likewise, a function that is referenced in the SELECT section of a query (e.g. row-number) is applied after the set of matching rows has been identified, but before the data has been ordered.
DB2 Using SQL Related Interview Questions
|PL/SQL Interview Questions||IBM DB2 Interview Questions|
|Oracle Interview Questions||COBOL Interview Questions|
|DB2 Using SQL Interview Questions||IBM Mainframe Interview Questions|
|MYSQL DBA Interview Questions||DB2 SQL Programming Interview Questions|
|IMS/DB Interview Questions||Mainframe DB2 Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.