SQL WHERE

Internal

• SQL SELECT
• SQL UPDATE
• SQL DELETE

TODO

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html

Overview

The WHERE clause is the mechanism for filtering out unwanted data from the result set. The WHERE clause can be used with SELECT, UPDATE and DELETE, but not with INSERT. The WHERE clause may contain an arbitrary number of filter conditions separated by AND, OR and NOT operators. The filter conditions may be optionally grouped together with parentheses.

[...] WHERE <filter_condition> AND|OR <filter_condition> ...

SELECT * FROM person WHERE person.name = 'Alice' AND (person.eye_color = 'blue' OR person.eye_color = 'black');

Using Parentheses

If the WHERE clause includes three or more conditions combined with AND, OR or NOT, you should use parentheses to make your intent clear.

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Using%20Parentheses

Filter Conditions

A condition is made up of one or more expressions, combined with one or more operators. An expression can be any of the following:

• A number
• A column in a table or a view
• A string literal
• A built-in function such as CONCAT()
• A subquery
• A list of expressions such as ('A', 'B', 'C')

The operators used within conditions include:

• Comparison operators: =, !=, <, >, <>, LIKE, IN, BETWEEN.
• Arithmetic operators: +, -, /, *

Equality Conditions

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Equality%20Conditions

Inequality Conditions

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Inequality%20conditions,an%20inequality%20condition%3A

Existence Conditions

EXISTS

Range Conditions

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Range%20Conditions

The BETWEEN Operator

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=The%20between%20operator

String Ranges

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=String%20ranges

Membership Conditions

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Membership%20Conditions

Using Subqueries

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Using%20subqueries

Matching Conditions

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Matching%20Conditions

Using Regular Expressions

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Using%20regular%20expressions

NULL in Conditions

To test whether a value is NULL use the IS operator, not =:

SELECT * FROM person where name IS NULL;

The following syntax is incorrect, and while will not generate a syntax or runtime error, it will produce invalid results, it will return no rows:

SELECT * FROM person where name = NULL;

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Null%3A%20That%20Four-Letter%20Word

Also see:

NULL

SQL Conditional Logic

SQL conditional logic is the ability of SQL statements to take one of several paths during execution, depending on the value of an expression. The SQL has syntax that provides IF/ELSE conditional logic, except that it does not use the IF keyword but CASE. These expressions can be used in the SELECT ... WHERE clause, but also with conditional SELECT, INSERT, UPDATE and DELETE.

All major databases have built-in function that mimic IF/ELSE statements. However, the CASE expressions should be preferred instead, because CASE is part of the SQL92 standard and thus available on all standard-compliant servers. CASE has been built into the grammar of the SELECT, INSERT, UPDATE and DELETE statements, and in the WHERE clause.

There are two different types of CASE syntax: searched CASE expressions and simple CASE expressions.

Searched CASE Expressions

CASE
    WHEN <conditional_expression_1> THEN <expression_1>
    WHEN <conditional_expression_2> THEN <expression_2>
    ...
    [ELSE <default_expression>]
END

When the CASE expression is evaluated, the WHEN conditional expressions are evaluated in order from top to bottom. As soon as one of the conditional expressions is evaluated to TRUE, the corresponding expression is evaluated and returned. If none of the WHEN conditional expressions evaluate to true, then expression introduced by ELSE is evaluated and returned. If the CASE does not have an ELSE, then NULL is returned.

All returned expressions (<expression_1>, <expression_2>, ... <default_expression>) must evaluate to the same type.

<expression_1>, <expression_2>, ... <default_expression> can all be subqueries.

This is an example of using CASE in SELECT:

SELECT name, 
  CASE
    WHEN active = 1 THEN 'ACTIVE'
    ELSE 'INACTIVE'
  END activity_level
FROM person

This is an example of using CASE in SELECT ... WHERE:

SELECT name FROM person 
  WHERE 
    CASE 
      WHEN active IS NULL THEN FALSE
      ELSE active = 1
  END;

CASE can also be used in:

• Conditional INSERT
• Conditional UPDATE
• Conditional DELETE

CASE

CASE <expression>
    WHEN <result_1> THEN <expression_1>
    WHEN <result_2> THEN <expression_2>
    ...
    [ELSE <default_expression>]
END

The <expression> is evaluated and the result compared from top to bottom with result_1, result_2, etc. Upon the first match, the corresponding expression is evaluated and returned. If there is no match, the <default_expression> is evaluated and returned. If there is ELSE, NULL is returned.

A simple IF/ELSE:

CASE <expression_that_evaluates_to_true_or_false> WHEN TRUE THEN <if_expression>
ELSE <else_expression>
END

Usage example:

CASE array_length(person.skills, 1) > 0 WHEN TRUE THEN ('skill_we_search_for' = ANY(person.skills)) 
ELSE 'key_we_search_for' = ANY(general.skills)
END

TO PROCESS: https://www.geeksforgeeks.org/sql-conditional-expressions/