SELECT with WHERE

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 

Arithmetic Operators 


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 

Logical Operators 


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)