Product Documentation

Appearance

Logical Operators ​

Yellowbrick supports the following logical SQL operators:

  • AND
  • OR
  • NOT

Boolean logic is applied to these operators, returning TRUE, FALSE, or UNKNOWN.

AND and OR are commutative; an expression returns the same result regardless of the order of the operands. For example, the following expressions produce the same results:

where seasonid=1 and season_name='1992-1993'
where season_name='1992-1993' and seasonid=1

You can use parentheses to define how the logical operators should be applied when multiple operators are used in the same complex expression.

The following simple queries use the logical operators in search conditions:

premdb=# select * from season where seasonid=1 or seasonid=2;
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
       1 | 1992-1993   |       22 | Manchester United
       2 | 1993-1994   |       22 | Manchester United
(2 rows)

premdb=# select * from season where seasonid=1 and not seasonid=2;
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
       1 | 1992-1993   |       22 | Manchester United

premdb=# select * from season where seasonid >1 and not numteams=20; 
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
       2 | 1993-1994   |       22 | Manchester United
       3 | 1994-1995   |       22 | Blackburn Rovers
(2 rows)

The following two queries show how a use of parentheses changes the logic of the search condition:

premdb=# select * from season where seasonid<10 or numteams=22 and winners='Manchester United';
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
       1 | 1992-1993   |       22 | Manchester United
       2 | 1993-1994   |       22 | Manchester United
       3 | 1994-1995   |       22 | Blackburn Rovers
       4 | 1995-1996   |       20 | Manchester United
       5 | 1996-1997   |       20 | Manchester United
       6 | 1997-1998   |       20 | Arsenal
       7 | 1998-1999   |       20 | Manchester United
       8 | 1999-2000   |       20 | Manchester United
       9 | 2000-2001   |       20 | Manchester United
(9 rows)

premdb=# select * from season where (seasonid<10 or numteams=22) and winners='Manchester United';
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
       1 | 1992-1993   |       22 | Manchester United
       2 | 1993-1994   |       22 | Manchester United
       4 | 1995-1996   |       20 | Manchester United
       5 | 1996-1997   |       20 | Manchester United
       7 | 1998-1999   |       20 | Manchester United
       8 | 1999-2000   |       20 | Manchester United
       9 | 2000-2001   |       20 | Manchester United
(7 rows)

Order of Evaluation ​

In general, the order of evaluation of conditions with AND and OR is non-deterministic. Yellowbrick does not support a "short circuit" order of evaluation for conditions, except in CASE WHEN expressions.

For example, the following WHERE clause condition:

where col1 !=0 and col1 / col2 > 5

is not evaluated from left to right, which would remove the need to evaluate the right side of the expression when col1=0. Yellowbrick evaluates the right side of this expression first and, depending on the data in the table, may return a divide-by-zero error:

premdb=# create table t1(c1 int, c2 int);
CREATE TABLE
premdb=# insert into t1 values (0,0), (1,0), (0,1), (1,1);
INSERT 0 4
premdb=# select * from t1;
 c1 | c2 
----+----
  0 |  0
  1 |  0
  0 |  1
  1 |  1
(4 rows)

premdb=# select * from t1 where c1 !=0 and c1/c2>5;
ERROR:  division by zero - division by zero

Parent topic:SQL Conditions