Logical operators (and - or - not)

 So far, we have learned how to set a "where" condition using relational operators. We can establish more than one condition with the "where" clause, for this we will learn the logical operators.

They are as follows:

• and, means "and",
• or, means "and / or",
• not, means "no", invert the result
• (), parentheses

Logical operators are used to combine conditions.

If we want to recover all the books whose author is equal to "Borges" and whose price does not exceed 20 pesos, we need 2 conditions:

 select * from books

  where (author = 'Borges') and

  (price <= 20);

The records retrieved in a statement that joins two conditions with the "and" operator, meet the 2 conditions.

We want to see the books whose author is "Borges" and / or whose publisher is "Planeta":

 select * from books

  where author = 'Borges' or

  editorial = 'Planet';

In the previous statement we use the "or" operator; We indicate that it retrieves the books in which the value of the "author" field is "Borges" and / or the value of the "publisher" field is "Planet", that is, it will select the records that meet the first condition, with the second condition and with both conditions.

Records retrieved with a statement that joins two conditions with the "or" operator, meet one or both of the conditions.

We want to recover the books that DO NOT meet the given condition, for example, those whose publisher is NOT "Planet":

 select * from books

  where not editorial = 'Planet';

The "not" operator reverses the result of the condition it precedes.

The records retrieved in a statement in which the "not" operator appears, do not meet the condition affected by the "NOT".

Parentheses are used to enclose conditions, so that they are evaluated as a single expression.

When we make several conditions explicit with different logical operators (we combine "and", "or") it allows us to establish the order of priority of the evaluation; it also allows to differentiate the expressions more clearly.

For example, the following expressions return a different result:

 select * from books

  where (author = 'Borges') or

  (editorial = 'Paidos' and price <20);

 select * from books

  where (author = 'Borges' or publisher = 'Paidos') and

  (price <20);

Although parentheses are not required in all cases, it is recommended to use them to avoid confusion.

The order of precedence of the logical operators is as follows: "not" is applied before "and" and "and" before "or", if an evaluation order is not specified using parentheses. The order in which operators with the same precedence level are evaluated is undefined, therefore it is recommended to use parentheses.

So, to establish more than one condition in a "where" it is necessary to use logical operators. "and" means "and", indicates that both conditions are met; "or" means "and / or", it indicates that one or the other condition (or both) is met; "not" means "no.", indicates that the specified condition is not met.