SQL WHERE: Difference between revisions
(6 intermediate revisions by the same user not shown) | |||
Line 3: | Line 3: | ||
* [[SQL_UPDATE|SQL <tt>UPDATE</tt>]] | * [[SQL_UPDATE|SQL <tt>UPDATE</tt>]] | ||
* [[SQL_DELETE|SQL <tt>DELETE</tt>]] | * [[SQL_DELETE|SQL <tt>DELETE</tt>]] | ||
=TODO= | |||
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch04.html</font> | |||
=Overview= | =Overview= | ||
Line 15: | Line 17: | ||
=Filter Conditions= | =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 <code>CONCAT()</code> | |||
* A [[SQL_SELECT#Subqueries_(Querying_a_Derived_Table|subquery]] | |||
* A list of expressions such as <code>('A', 'B', 'C')</code> | |||
The operators used within conditions include: | |||
* Comparison operators: <code>=</code>, <code>!=</code>, <code><</code>, <code>></code>, <code><></code>, <code>LIKE</code>, <code>IN</code>, <code>BETWEEN</code>. | |||
* Arithmetic operators: <code>+</code>, <code>-</code>, <code>/</code>, <code>*</code> | |||
==Equality Conditions== | |||
==Inequality Conditions== | |||
==Range Conditions== | |||
==The <tt>BETWEEN</TT> Operator== | |||
==String Ranges== | |||
==Membership Conditions== | |||
==Using Subqueries== | |||
==Matching Conditions== | |||
==Using Regular Expressions== | |||
==<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> | |||
Also see: {{Internal|SQL#SQL_NULL|<tt>NULL</tt>}} | Also see: {{Internal|SQL#SQL_NULL|<tt>NULL</tt>}} | ||
=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. |
Revision as of 02:12, 24 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, and optionally grouped together with parentheses.
FROM <filter_condition> AND|OR <filter_condition> ...
SELECT * FROM person WHERE person.name = 'Alice' AND (person.eye_color = 'blue' OR person.eye_color = 'black');
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
Inequality Conditions
Range Conditions
The BETWEEN Operator
String Ranges
Membership Conditions
Using Subqueries
Matching Conditions
Using Regular Expressions
NULL in Conditions
Also see:
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.