What is a Logical Operator in Oracle?

Logical Operator in Oracle

To create a single result from two component conditions or to reverse the result of a single condition, logical operators are utilised in Oracle SQL. The ability to specify multiple criteria for data retrieval, updates, or deletions makes them essential for crafting complex WHERE clauses. Logical conditions can have three possible outcomes: TRUE, FALSE, or UNKNOWN (particularly when NULL values are involved).

The AND Operator

When combining two or more logical expressions, the AND operator is utilised. In order for an action to be executed or a row to be returned, all conditions that are connected by AND must evaluate to TRUE. The entire AND condition turns into FALSE if any of the conditions it links to are FALSE.

Operator Precedence: Compared to OR, AND has a higher precedence. This indicates that when AND and OR are combined in an expression, the AND operations will be assessed first.

Example: Let’s find features in a NEWSPAPER table where the Section is ‘F’ AND the Page is greater than 7.

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Section = 'F' AND Page > 7;

Code Output:

FEATURE         S       PAGE
--------------- - ----------
Classified      F          8

This output only displays the row that met both of the requirements (Section = 'F' and Page > 7).

The OR Operator

The OR operator also combines logical expressions, but it returns a TRUE result if at least one of the conditions it connects is TRUE. If both conditions are FALSE, then the OR condition evaluates to FALSE.

Operator Precedence: AND has a higher precedence than the OR operator.

Example: To retrieve features where the Section is ‘F’ OR the Page is greater than 7:

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Section = 'F' OR Page > 7;

Code Output:

FEATURE         S       PAGE
--------------- - ----------
Television      B          7
Births          F          7
Classified      F          8
Obituaries      F          6
Doctor Is In    F          6

This result returns rows where the page is greater than 7 (TV, Classified) and rows where the section is ‘F’ (Births, Obituaries, Doctor Is In, Classified). ‘Television’ only matches Page > 7, and ‘Births’ matches Section = 'F'. In contrast, ‘Classified’ appears because both conditions are true.

The NOT Operator

The NOT operator is a logical negation operator that reverses the logical state of a condition. If a condition is TRUE, NOT TRUE is FALSE. If a condition is FALSE, NOT FALSE is TRUE. If a condition is UNKNOWN, NOT UNKNOWN remains UNKNOWN.

The NOT operator can be used in various contexts, such as NOT IN, NOT BETWEEN, or IS NOT NULL.

Example: To find features where the Section is NOT ‘A’, ‘C’, or ‘F’:

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Section NOT IN ('A','C','F');

Code Output:

FEATURE         S       PAGE
--------------- - ----------
Sports          D          1
Business        E          1
Television      B          7
Modern Life     B          1
Movies          B          4
Bridge          B          2

All rows with sections B, D, and E that are not A, C, or F are returned by this query.

Precedence and Parentheses

Combining several logical operators requires an understanding of operator precedence. AND has a higher precedence than OR, as was previously mentioned. Oracle examines criteria with equal precedence from left to right, with higher precedence circumstances being assessed first.

To make sure your conditions are evaluated in a certain sequence and to override the default operator precedence, you should use brackets (). Parenthetical expressions are usually evaluated before those outside of them.

Illustrative Example of Precedence: Consider a query to find features past page 2 of section A or B. Without parentheses, the query WHERE Section = 'A' OR Section = 'B' AND Page > 2 is interpreted as “where Section = ‘A’, or where Section = ‘B’ AND Page > 2” because AND binds more strongly.

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Section = 'A' OR Section = 'B' AND Page > 2;

Code Output (without parentheses):

FEATURE         S       PAGE
--------------- - ----------
National News   A          1
Editorials      A         12
Television      B          7
Movies          B          4

Here, “National News” (Page 1, Section A) and “Editorials” (Page 12, Section A) are included because Section = 'A' is true for them, and OR causes them to be returned regardless of the Page > 2 condition.

To achieve the intended logic (features past page 2 of section A or B), you would use parentheses to group the OR condition:

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE (Section = 'A' OR Section = 'B') AND Page > 2;

Code Output (with parentheses):

FEATURE         S       PAGE
--------------- - ----------
Editorials      A         12
Television      B          7
Movies          B          4

Only features that are from Section A or B and have a page number higher than two are now returned. This exemplifies how brackets enforce the correct order of actions and delineate your meaning.

Truth Tables

Logical conditions often come with truth tables that define how TRUE, FALSE, and UNKNOWN values combine. For example, the AND truth table shows that the result is TRUE only if both component conditions are TRUE. If either is FALSE, the result is FALSE. If any is UNKNOWN and no FALSE exists, the result is UNKNOWN. Similarly, OR returns TRUE if at least one component condition is TRUE, and FALSE only if both are FALSE. NOT simply inverts TRUE to FALSE, FALSE to TRUE, and UNKNOWN remains UNKNOWN.

Null Values and Logical Operators

Data that is missing or unknown is represented by NULL values. UNKNOWN is frequently the result of logical procedures involving NULL values. For example, 10!= NULL and 10 = NULL both produce UNKNOWN.

You must use the IS NULL or IS NOT NULL operators to specifically check for the existence or absence of NULL values. For this purpose, NULL does not operate well with the standard comparison operators (=, !=, <, >).

Example with IS NULL:

SELECT last_name
FROM employees
WHERE commission_pct IS NULL;

Example with IS NOT NULL:

SELECT last_name
FROM employees
WHERE commission_pct IS NOT NULL;

The use of NOT IN with subqueries that may return NULL data is a typical mistake. Even if there are matching rows, no rows will be returned if a subquery with NOT IN returns even one NULL. This is because the entire NOT IN condition will always evaluate to UNKNOWN, which is essentially FALSE for row selection.

Logical Operators in PL/SQL

In PL/SQL, logical operators are extensively used in conditional control statements like IF-THEN-ELSE and CASE statements. These constructs allow programs to execute different blocks of code based on whether a condition or combination of conditions is TRUE, FALSE, or UNKNOWN. For example, in an IF statement, IF condition1 AND condition2 THEN ... will only execute the THEN block if both are TRUE. Similarly, IF condition1 OR condition2 THEN ... will execute if at least one is TRUE.

Additionally, PL/SQL allows AND and OR operators in IF statements to be evaluated short-circuitly. In AND, the second condition is not evaluated if the first condition is FALSE or NULL since the final outcome is already known. The second condition is not assessed for OR if the first condition is TRUE. This may be crucial for performance or if there are adverse repercussions from the second criterion.

Logical operators can be thought of as the traffic lights and road signs of your programs and database queries. They control the flow, making sure that only the data that satisfies your exact requirements is sent or that the right code paths are taken. Writing correct and reliable Oracle SQL and PL/SQL code requires making appropriate use of them, particularly when you understand precedence and NULL handling.