SQL WHERE: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(41 intermediate revisions by the same user not shown)
Line 7: Line 7:


=Overview=
=Overview=
The <code>WHERE</code> clause is the mechanism for filtering out unwanted data from the [[SQL#Result_Set|result set]]. The <code>WHERE</code> clause can be used with <code>[[SQL_SELECT#WHERE|SELECT]]</code>, <code>[[SQL_UPDATE#Overview|UPDATE]]</code> and <code>[[SQL_DELETE#Overview|DELETE]]</code>, but not with <code>[[SQL_INSERT#Overview|INSERT]]</code>. The <code>WHERE</code> clause may contain an arbitrary number of filter conditions separated by <code>AND</code>, <code>OR</code> and <code>NOT</code> operators, and optionally grouped together with parentheses.
The <code>WHERE</code> clause is the mechanism for filtering out unwanted data from the [[SQL#Result_Set|result set]]. The <code>WHERE</code> clause can be used with <code>[[SQL_SELECT#WHERE|SELECT]]</code>, <code>[[SQL_UPDATE#Overview|UPDATE]]</code> and <code>[[SQL_DELETE#Overview|DELETE]]</code>, but not with <code>[[SQL_INSERT#Overview|INSERT]]</code>. The <code>WHERE</code> clause may contain an arbitrary number of filter conditions separated by <code>AND</code>, <code>OR</code> and <code>NOT</code> operators. The filter conditions may be optionally grouped together with [[#Using_Parentheses|parentheses]].
<font size=-1.5>
<font size=-1.5>
  <font color=green><b>FROM</b></font> <filter_condition> <font color=green><b>AND</b></font>|<font color=green><b>OR</b></font> <filter_condition> ...
[...] <font color=green><b>WHERE</b></font> <filter_condition> <font color=green><b>AND</b></font>|<font color=green><b>OR</b></font> <filter_condition> ...
</font>
</font>


Line 15: Line 15:
SELECT * FROM person WHERE person.name = 'Alice' AND (person.eye_color = 'blue' OR person.eye_color = 'black');
SELECT * FROM person WHERE person.name = 'Alice' AND (person.eye_color = 'blue' OR person.eye_color = 'black');
</syntaxhighlight>
</syntaxhighlight>
=Using Parentheses=
If the <code>WHERE</code> clause includes three or more conditions combined with <code>AND</code>, <code>OR</code> or <code>NOT</code>, you should use parentheses to make your intent clear.
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Using%20Parentheses</font>


=Filter Conditions=
=Filter Conditions=
Line 22: Line 26:
* A string literal
* A string literal
* A built-in function such as <code>CONCAT()</code>
* A built-in function such as <code>CONCAT()</code>
* A [[SQL_SELECT#Subqueries_(Querying_a_Derived_Table|subquery]]
* A [[SQL_SELECT#Subqueries_(Querying_a_Derived_Table)|subquery]]
* A list of expressions such as <code>('A', 'B', 'C')</code>
* A list of expressions such as <code>('A', 'B', 'C')</code>


Line 29: Line 33:
* Arithmetic operators: <code>+</code>, <code>-</code>, <code>/</code>, <code>*</code>
* Arithmetic operators: <code>+</code>, <code>-</code>, <code>/</code>, <code>*</code>
==Equality Conditions==
==Equality Conditions==
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Equality%20Conditions</font>
==Inequality Conditions==
==Inequality Conditions==
<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==
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Range%20Conditions</font>
==The <tt>BETWEEN</TT> Operator==
==The <tt>BETWEEN</TT> Operator==
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=The%20between%20operator</font>
==String Ranges==
==String Ranges==
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=String%20ranges</font>
==Membership Conditions==
==Membership Conditions==
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Membership%20Conditions</font>
==Using Subqueries==
==Using Subqueries==
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Using%20subqueries</font>
==Matching Conditions==
==Matching Conditions==
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html#:-:text=Matching%20Conditions</font>
==Using Regular Expressions==
==Using Regular Expressions==
<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==
To test whether a value is <code>NULL</code> use the <code>IS</code> operator, not <code>=</code>:
<syntaxhighlight lang='sql'>
SELECT * FROM person where name IS NULL;
</syntaxhighlight>
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'>
SELECT * FROM person where name = NULL;
</syntaxhighlight>
<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>}}
Also see: {{Internal|SQL#SQL_NULL|<tt>NULL</tt>}}


=Using Parentheses=
=<span id='CASE'></span>SQL Conditional Logic=
If the <code>WHERE</code> clause includes three or more conditions combined with <code>AND</code>, <code>OR</code> or <code>NOT</code>, you should use parentheses to make your intent clear.
{{Internal|SQL_Conditional_Logic#Overview|SQL Conditional Logic}}

Latest revision as of 21:06, 30 May 2024

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