|
|
(14 intermediate revisions by the same user not shown) |
Line 38: |
Line 38: |
|
| |
|
| <font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Inequality%20conditions,an%20inequality%20condition%3A</font> | | <font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Inequality%20conditions,an%20inequality%20condition%3A</font> |
| | |
| | ==Existence Conditions== |
| | |
| | <code>EXISTS</code> |
|
| |
|
| ==Range Conditions== | | ==Range Conditions== |
Line 54: |
Line 58: |
| <font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Using%20regular%20expressions</font> | | <font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Using%20regular%20expressions</font> |
| ==<tt>NULL</tt> in Conditions== | | ==<tt>NULL</tt> in Conditions== |
| <font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Null%3A%20That%20Four-Letter%20Word</font>
| | To test whether a value is <code>NULL</code> use the <code>IS</code> operator, not <code>=</code>: |
| Also see: {{Internal|SQL#SQL_NULL|<tt>NULL</tt>}}
| |
| | |
| =<span id='CASE'></span>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 <code>IF</code> keyword but <code>CASE</code>. These expressions can be used in the <code>SELECT ... WHERE</code> clause, but also with conditional <code>SELECT</code>, <code>[[SQL_INSERT#Conditional_INSERT|INSERT]]</code>, <code>[[SQL_UPDATE#Conditional_UPDATE|UPDATE]]</code> and <code>[[SQL_DELETE#Conditional_DELETE|DELETE]]</code>.
| |
| | |
| All major databases have built-in function that mimic IF/ELSE statements. However, the <code>CASE</code> expressions should be preferred instead, because <code>CASE</code> is part of the SQL92 standard and thus available on all standard-compliant servers. <code>CASE</code> has been built into the grammar of the <code>SELECT</code>, <code>[[SQL_INSERT#Conditional_INSERT|INSERT]]</code>, <code>[[SQL_UPDATE#Conditional_UPDATE|UPDATE]]</code> and <code>[[SQL_DELETE#Conditional_DELETE|DELETE]]</code> statements, and in the <code>WHERE</code> clause.
| |
| | |
| There are two different types of <code>CASE</code> syntax: [[#Searched_CASE_Expressions|searched CASE expressions]] and [[#Simple_CASE_Expressions|simple CASE expressions]].
| |
|
| |
|
| ==Searched <tt>CASE</tt> Expressions==
| | <syntaxhighlight lang='sql'> |
| <syntaxhighlight lang=sql> | | SELECT * FROM person where name IS NULL; |
| CASE
| |
| WHEN <conditional_expression_1> THEN <expression_1>
| |
| WHEN <conditional_expression_2> THEN <expression_2>
| |
| ...
| |
| [ELSE <default_expression>]
| |
| END
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| When the <CODE>CASE</CODE> expression is evaluated, the <code>WHEN</code> conditional expressions are evaluate 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 <code>WHEN</code> conditional expressions evaluate to true, then expression introduced by <code>ELSE</code> is evaluated and returned. If the <code>CASE</code> does not have an <code>ELSE</code>, <font color=darkkhaki>then <code>NULL</code> is returned</font>.
| | 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: |
| | | <syntaxhighlight lang='sql'> |
| All returned expressions (<code><expression_1></code>, <code><expression_2></code>, ... <code><default_expression></code>) must evaluate to the same type.
| | SELECT * FROM person where name = NULL; |
| | |
| ==Simple <tt>CASE</tt> Expressions==
| |
| | |
| ==<tt>CASE</tt>==
| |
| | |
| | |
| | |
| <code>CASE</code> provides an IF/ELSE conditional logic that can be used in <code>WHERE</code> clauses.
| |
| | |
| <syntaxhighlight lang=sql> | |
| CASE <expression>
| |
| WHEN <result_1> THEN <expression_1>
| |
| WHEN <result_2> THEN <expression_2>
| |
| ...
| |
| [ELSE <default_expression>]
| |
| END
| |
| </syntaxhighlight> | | </syntaxhighlight> |
|
| |
|
| A simple IF/ELSE:
| | <font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Null%3A%20That%20Four-Letter%20Word</font> |
| | Also see: {{Internal|SQL#SQL_NULL|<tt>NULL</tt>}} |
|
| |
|
| <syntaxhighlight lang=sql> | | =<span id='CASE'></span>SQL Conditional Logic= |
| CASE <expression_that_evaluates_to_true_or_false> WHEN TRUE THEN <if_expression> | | {{Internal|SQL_Conditional_Logic#Overview|SQL Conditional Logic}} |
| ELSE <else_expression>
| |
| END
| |
| </syntaxhighlight> | |
| | |
| Usage example:
| |
| <syntaxhighlight lang=sql>
| |
| 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
| |
| </syntaxhighlight>
| |
| | |
| <font color=darkkhaki>TO PROCESS: https://www.geeksforgeeks.org/sql-conditional-expressions/</font>
| |
Internal
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