WHERE Clause

Introduction

The WHERE clause is used to filter the data records on the basis of a specified condition or a set of conditions in the search queries. When the WHERE condition is used in a query, the data records are verified based on the specified conditions, and the commands are executed only on the records that satisfy the conditions.

For example, the syntax for using the WHERE condition in a SELECT statement is:

    

        
copy
        

            

        
    
    

         SELECT column_name(s) FROM base_table_name WHERE condition
    

The WHERE condition can be used with the UPDATE and DELETE statements as well.

Operators Supported by the WHERE Clause

You can use the following operators in the WHERE conditions in ZCQL queries:

Example:

To view a list of the movies that are screened on days other than July 13, 2018 from the Movies table, you can specify the date condition in the query using the WHERE clause in the following way:

    

        
copy
        

            

        
    
    

         SELECT MovieName, ShowDate FROM Movies WHERE ShowDate IS '2018-07-14'
    

This will generate the following output:

Multiple WHERE Conditions

You can specify a maximum of five WHERE conditions in a single query. When multiple WHERE conditions are specified, you can use either an AND or an OR operator to link the conditions together. The functionalities of the AND and OR operators are:

• AND: Produces only the data records that satisfy both of the conditions that are associated with the AND operator.

• OR: Produces the data records that satisfy either of the conditions that are associated with the OR operator.

For example, the syntax for using multiple WHERE conditions in a SELECT statement is:

    

        
copy
        

            

        
    
    

         SELECT column_name(s)
FROM base_table_name
WHERE condition_1 AND|OR condition_2.. AND|OR condition_5
    

Example:

To view a list of the show dates and show times from the Movies table for either ‘The First Purge’ or ‘Skyscraper’ movies that are screened on July 14, execute the following query:

    

        
copy
        

            

        
    
    

         SELECT MovieName, ShowDate, ShowTime FROM Movies WHERE MovieName='The First Purge' OR MovieName='Skyscraper' AND ShowDate='2018-07-14'
    

There is only one record that matches the above conditions. The query will therefore generate the following result:

LIKE Statement

The LIKE condition enables you to indicate records which contain a specific criteria and select all records that match that condition. For example, you can indicate a specific alphabet that the records you require to be fetched must begin with, or a particular value that they must contain.

For example, the syntax of the LIKE condition in an UPDATE statement is as follows:

    

        
copy
        

            

        
    
    

         UPDATE base_table_name
SET column_name(s)=value(s)
WHERE condition
LIKE *value* 
    

The ‘*’ can be used either before or after, or both before and after, the value that you specify. The ‘*’ is essentially a placeholder that indicates that any value can be replaced in its stead.

For example, to indicate that all records in a table that begin with the alphabet ‘A’ must be fetched, you can enter the LIKE value as ‘A*’. This specifies that after the letter A, any values can follow.

Example:

To update the records where the movie names begin with the letter ‘S’ in the Movies table, execute the following query:

    

        
copy
        

            

        
    
    

         UPDATE Movies SET ShowDate='2018-07-17' WHERE MovieName like 'S*'
    

This will update the following record and set the value:

BETWEEN Statement

The BETWEEN condition enables you to filter records by indicating the starting and ending values in a particular column containing numerical values. For example, you can select all the records where the percentage values are between 70 and 80.

For example, the syntax of a BETWEEN condition in a DELETE statement is as follows:

    

        
copy
        

            

        
    
    

         DELETE FROM base_table_name
WHERE condition
BETWEEN Value1 AND Value2
    

Example:

To delete the records where the value of the MovieID is between 2056 and 2059 in the Movies table, execute the following query:

    

        
copy
        

            

        
    
    

         DELETE FROM Movies WHERE MovieID BETWEEN 2056 AND 2059
    

The following records from the table will be deleted: