Logical Conditions

Description

Logical conditions combine the result of two conditions to produce a single result. All logical conditions are binary operators with a Boolean return type. (Redshift SQL Language reference Logical Conditions).

This grammar is fully supported in Snowflake.

Grammar Syntax

expression
{ AND | OR }
expression
NOT expression 

E1	E2	E1 AND E2	E1 OR E2	NOT E2
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	TRUE
TRUE	UNKNOWN	UNKNOWN	TRUE	UNKNOWN
FALSE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	UNKNOWN	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	TRUE
UNKNOWN	FALSE	FALSE	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

Sample Source Patterns

Setup data

Redshift

CREATE TABLE employee (
    employee_id INT,
    active BOOLEAN,
    department VARCHAR(100),
    hire_date DATE,
    salary INT
);

INSERT INTO employee (employee_id, active, department, hire_date, salary) VALUES
    (1, TRUE, 'Engineering', '2021-01-15', 70000),
    (2, FALSE, 'HR', '2020-03-22', 50000),
    (3, NULL, 'Marketing', '2019-05-10', 60000),
    (4, TRUE, 'Engineering', NULL, 65000),
    (5, TRUE, 'Sales', '2018-11-05', NULL);

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT
    employee_id,
    (active AND department = 'Engineering') AS is_active_engineering,
    (department = 'HR' OR salary > 60000) AS hr_or_high_salary,
    NOT active AS is_inactive,
    (hire_date IS NULL) AS hire_date_missing,
    (salary IS NULL OR salary < 50000) AS low_salary_or_no_salary
FROM employee;

Results

EMPLOYEE_ID	IS_ACTIVE_ENGINEERING	HR_OR_HIGH_SALARY	IS_INACTIVE	HIRE_DATE_MISSING	LOW_SALARY_OR_NO_SALARY
1	TRUE	TRUE	FALSE	FALSE	FALSE
2	FALSE	TRUE	TRUE	FALSE	FALSE
3	FALSE	FALSE	NULL	FALSE	FALSE
4	TRUE	TRUE	FALSE	TRUE	FALSE
5	FALSE	NULL	FALSE	FALSE	TRUE

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
    employee_id,
    (active AND department = 'Engineering') AS is_active_engineering,
    (department = 'HR' OR salary > 60000) AS hr_or_high_salary,
    NOT active AS is_inactive,
    (hire_date IS NULL) AS hire_date_missing,
    (salary IS NULL OR salary < 50000) AS low_salary_or_no_salary
FROM
    employee;

Results

EMPLOYEE_ID	IS_ACTIVE_ENGINEERING	HR_OR_HIGH_SALARY	IS_INACTIVE	HIRE_DATE_MISSING	LOW_SALARY_OR_NO_SALARY
1	TRUE	TRUE	FALSE	FALSE	FALSE
2	FALSE	TRUE	TRUE	FALSE	FALSE
3	FALSE	FALSE	NULL	FALSE	FALSE
4	TRUE	TRUE	FALSE	TRUE	FALSE
5	FALSE	NULL	FALSE	FALSE	TRUE

Known Issues

No issues were found.

Related EWIs

There are no known issues.