The WHERE clause is the mechanism for filtering out unwanted rows from the result set. Rows can be selected by looking for column values that satisfy various criteria, and different types of values can be filtered out. In the statement below, the query is selecting information from three columns, from a given table with a clause where a specific city name is to be used. In other words, it is answering the question…"What is the ID and district for the city of New York?", as follows;
mysql> SELECT ID, Name, District FROM City
-> WHERE Name = 'New York';
+-------+--------------+-------------+
| ID | Name | District |
+-------+--------------+--------------+
| 3793 | New York | New York |
+-------+--------------+--------------+
1 row in set (#.## sec)
Expressions in WHERE clauses can use the following types of operators;
Arithmetic -- for calculation
Comparison – for comparison of column and row data, needed for each (smart) WHERE condition
Logical – to combine different WHERE conditions
Perform mathematical operations on column entity values.
Operator Definition
+ Addition
- Subtraction
* Multiplication
/ Division
DIV Integer Division
% Modulo (remainder after division)
Comparison Operators
Compare column entity values.
Operator Definition
< Less than
<= Less than or equal to
= Equal to
<=> Equal to (works even for NULL values)
<> or != Not equal to
>= Greater than or equal to
> Greater than
BETWEEN Indicate a range of numerical
x AND Y values
Combine different conditions.
Operator Definition
AND Logical AND
OR Logical OR
XOR Logical exclusive-OR
NOT Logical negation
Additional options in WHERE expressions;
- IN is equivalent to a list of comparisons with OR
- NULL values can be filtered by using IS NULL (WHERE column_name IS NULL)
- Parenthesis can also be used to group parts of an expression
- Operators can be performed using constants, table columns and function call
- It is also possible to search for combinations of values
The example below uses a few of the operators and techniques mentioned above (on the Country table):
mysql> SELECT Name, Population FROM Country
-> WHERE Population > 50000000 AND
-> (Continent = 'Europe' OR Code ='USA');
+---------------------------+-----------------+
| Name | Population |
+---------------------------+-----------------+
| United Kingdom | 59623400 |
| Italy | 57680000 |
| France | 59225700 |
| Germany | 82164700 |
| Ukraine | 50456000 |
| Russian Federation | 146934000 |
| United States | 278357000 |
+---------------------------+------------------+
7 rows in set (#.## sec)
This example demonstrates the use of the IN clause is a WHERE expression to apply the query to a list of entities;
mysql> SELECT ID, Name, District FROM city
-> WHERE Name IN ('New York', 'Rochester', 'Syracuse');
+-------+--------------+--------------+
| ID | Name | District |
+-------+--------------+--------------+
| 3793 | New York | New York |
| 3871 | Rochester | New York |
| 3935 | Syracuse | New York |
+-------+--------------+--------------+
3 rows in set (#.## sec)
Comments
Post a Comment