INCLUDE and EXCLUDE are used with SELECT to specify criteria for including or excluding certain records in a report.
INCLUDE issues an order to include records that match the specified criteria.
EXCLUDE issues an order to exclude records that match the specified criteria.
A single SELECT statement can contain multiple INCLUDE/EXCLUDE clauses, each specifying a list of fields and values for these fields. The data in the input record is compared against the specified values for each field in the INCLUDE/EXCLUDE list. The record must match all the criteria coded under one INCLUDE or EXCLUDE, for the record to be accordingly included or excluded.
If there are multiple INCLUDE operands in one SELECT statement, the record must match all the INCLUDEs for the record to be included. Similarly, if there are multiple EXCLUDE operands in one SELECT statement, the record must match all the EXCLUDEs for the record to be excluded. If there are both INCLUDEs and EXCLUDEs in one SELECT statement, the final outcome depends on which of the criteria the record matches.
The decision matrix in Table 1 shows which action is taken after examining a single SELECT statement against a record.
SELECT Statement Contains... | Result of Examination Against Record | Outcome |
---|---|---|
INCLUDEs only | All fields matched | Record included |
INCLUDEs only | Not all fields matched | Record passed to next SELECT |
EXCLUDEs only | All fields matched | Record excluded |
EXCLUDEs only | Not all fields matched | Record passed to next SELECT |
INCLUDEs and EXCLUDEs | All INCLUDE fields matched, but not all EXCLUDE fields matched | Record included |
INCLUDEs and EXCLUDEs | All EXCLUDE fields matched | Record excluded |
INCLUDEs and EXCLUDEs | Not all INCLUDE fields matched and not all EXCLUDE fields matched | Record passed to next SELECT |
Within a single SELECT statement, the order of the INCLUDEs and EXCLUDEs and the order of the fields specified within them does not matter, as each is analyzed to determine the outcome. However, the order of the INCLUDEs and EXCLUDEs can make a difference with multiple SELECT statements. For some examples, see Examples: INCLUDE and EXCLUDE sensitivity.