15 using boolean expressions - mergen-sergio/DBest GitHub Wiki

A boolean expression is a structure composed of smaller expressions that evaluate to either true or false.

Boolean expressions can be combined using logical operators such as: AND and OR.

One common use of boolean expressions is within the Filter Operator, which returns only the rows that satisfy the boolean expression. The boolean expression is defined by editing the properties of the filter operator.

The Boolean Expression Editor allows users to create and modify expressions, including:

• AND Expressions: Combine multiple conditions, all of which must be true.
• OR Expressions: Combine multiple conditions, where at least one must be true.
• Atomic Expressions: Involve a single comparison between two elements, such as:
  • Constant values (e.g., 5, 'John')
  • Dynamic values (e.g., values from columns)

The example below demonstrates how to create a boolean expression for a filter operator. The expression includes:

• An OR expression that combines two atomic expressions.

Additional atomic expressions or sub-expressions can be added by clicking the appropriate button in the editor.

The example also shows how to edit an existing atomic expression. The user changes the compared elements, such as modifying constant values or selecting different columns for comparison.

A boolean expression can also be created using specialized boolean operators such as AND, OR, XOR, and Condition. These operators evaluate to true or false based on their inputs.

1. AND, OR, and XOR Operators

   • These are binary operators that combine the boolean return values of their connected operators.
   • Evaluation logic:
     • Boolean Operators: If a connected operator is itself a boolean operator, its true or false value is used.
     • Non-Boolean Operators: If a connected operator returns any tuples, it evaluates to true; if no tuples are returned, it evaluates to false.

2. Condition Operator

   • This is a nullary operator, meaning it does not connect to any other operator.
   • It performs a direct comparison between two elements, which can be:
     • Constant values (e.g., 5, 'John')
     • Column values (must reference existing columns).

The example below demonstrates how to create the same boolean expression as in the earlier example, but this time using boolean operators instead of editing the properties of a filter operator. Key points:

• OR operator (||): Combines two condition operators.
• Condition operators (?): Use referenced columns for comparisons.

The filter operator is still present, but now it contains a simpler expression that directly tests the result of the AND operator.

Using boolean operators provides a more powerful and flexible representation of boolean expressions:

• Visual Construction: Operators can be added by dragging and dropping, enabling intuitive manipulation of expressions.
• Complex Expressions: Any operator can be connected to the evaluation tree, allowing more advanced constructs.

The example below demonstrates how to solve the following SQL query using boolean operators:

SELECT * 
FROM movie 
WHERE movie_id IN (SELECT movie_id FROM movie_cast) 
   OR movie_id IN (SELECT movie_id FROM movie_crew)

In this query tree:

• OR operator: Combines results from two subqueries.
• Subqueries: Represented by operators returning tuples from movie_cast and movie_crew.
• The filter operator evaluates the result of the OR operator.

This approach showcases the flexibility of boolean operators in constructing complex expressions.