Logical Operators
Logical Operators
Logical operators are used, together with the comparison operators =, <, >, <=, >= and LIKE, in the WHERE or HAVING clause of a SELECT query to form a condition that restricts the rows returned from a search.
At National 5 we used the following logical operators:
AND
OR
We will now use the following operators at Advanced Higher:
NOT
BETWEEN
IN
ANY
EXISTS
Logical Operator: NOT
The NOT operator returns a record from the underlying tables when the specified condition is not true.
Example Syntax
Logical Operator: BETWEEN
The between operator selects values that fall within a specified range of values. The results returned are inclusive of the lower and upper values.
Example Syntax
Logical Operator: IN
This allows multiple values to be specified as an alternative to multiple OR conditions and can be used to select values using subquerys.
Example
Sub Query Example
Logical Operator: ANY
The ANY operator returns TRUE if any of the subquery values meet the condition specified in the main query. The operator below could be any operator required such as LIKE, =
Additional Notes: ANY
Query 1: If we look at the graphical representation of the query below. The results of the inner query can be viewed as the coloured balls.
>ANY means greater than at least one value, that is, greater than the minimum.
So >ANY(28,59,89,17,6,12) means greater than 6.
As 90>6 is true, data is returned. Using the ANY operator, generates TRUE and so returns data to the main query.
Query 2: using the ANY operator, the query below would generate FALSE and so does not return data to the main query.
<ANY means less than at least one value, that is, less than the maximum.
So <ANY(28,59,89,17,6,12) means less than 89.
As 90<89 is false, no data is returned.
Query 3: using the ANY operator, the query below would generate FALSE and so does not return data to the main query.
<ANY means less than at least one value, that is, less than the maximum.
So <ANY(28,59,89,17,6,12) means less than 89.
As 4<89 is TRUE, data is returned.
Logical Operator: Exists
EXISTS:
is a comparison operator
is used in the WHERE clause to validate an ‘it exists’ condition
will tell whether a query returned results
returns a Boolean, (TRUE or FALSE)
returns TRUE if a subquery contains any rows
Query 4: general format of an SQL query that uses the EXISTS operator. The subquery would be evaluated first and if there was any results the the EXISTS (subquery) would evaluate as TRUE.
WHERE EXISTS ( subquery );
Query 5: In the example below the inner query would return some values ( as there are values in the COL_1 field that are <=15). Using the EXISTS operator, returns TRUE.
The subquery contains more than one row, so it returns TRUE.
Data is therefore returned from the main query.
Query 6: In the example below the inner query would not return any values ( as there are no values in the COL_1 field that are >=15). Using the EXISTS operator, returns FALSE.
The subquery contains no rows, so it returns FALSE.
No data is therefore returned from the main query.
Subqueries in the WHERE clause
A subquery is a query embedded within the WHERE clause of another SQL query.
A subquery is sometimes referred to as an inner query or a nested query, and an SQL query is sometimes referred to as the outer query or the parent query.
The subquery executes before the main query, so the results can be passed to the main query as a condition to further restrict the data to be retrieved.
Rules for using a subquery
Subqueries must be enclosed within brackets.
Unless the main query has multiple fields in its SELECT clause, a subquery can have only one field in its SELECT clause.
The BETWEEN operator can be used within a subquery but cannot be applied to the results of a subquery returned to the main query.
An ORDER BY clause can be used with the main query, but an ORDER BY clause cannot be used in a subquery; if it is needed, the GROUP BY clause can be used to perform the same function as the ORDER BY within a subquery.
Many subqueries return exactly one record (called single-value subqueries);this must be tested because an error will be generated if a subquery returns more results than expected.
Subqueries that return more than one row (called multiple-value subqueries), can only be used with multiple-value operators such as EXISTS, IN and ANY.