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:

Column to Column Comparison

You can compare two columns of the same table or in different tables by referring to the column names with their respective table names in the LHS and RHS of the comparison. The result of the comparison will return the rows that contain the same value for column1 and column2 in the table.

This comparison support in WHERE clause is applicable to the SELECT, UPDATE and DELETE queries.

You can compare any two columns that are of the following data types : BOOLEAN, DOUBLE, DATE, DATETIME, ENCRYPTED, VARCHAR, TEXT, INT and BIGINT.

SELECT:

The general syntax for column comparison within the same table is as follows:

    

        
copy
        

            

        
    
    

         SELECT * FROM tablename WHERE columnname1 = tablename.columnname2
    

Example:

    

        
copy
        

            

        
    
    

         Select MovieName FROM Movies WHERE Actor = Movies.Producer
    

UPDATE:

    

        
copy
        

            

        
    
    

         UPDATE Movies SET self_produced = "Yes" WHERE Actor = Movies.Producer
    

DELETE:

    

        
copy
        

            

        
    
    

         DELETE FROM Movies WHERE Actor = Movies.Producer
    

Comparison of Columns in Different Tables

You can also compare columns of two different tables as shown in the syntax below :

    

        
copy
        

            

        
    
    

         SELECT tablename1.columnname1, tablename2.columnname1 
FROM tablename2 LEFT JOIN tablename1 ON tablename2.columnname1 = tablename1.columnname2 
WHERE tablename1.columnname1 = tablename2.columnname2
    

Example:

    

        
copy
        

            

        
    
    

         SELECT Movies.name, Theatres.city from Theatres left join Movies on Theatres.ROWID = Movies.theatreID where Movies.distributionCity = theatre.city
    

Similarly, you can compare columns belonging to two different tables while executing the UPDATE and DELETE queries.

Subqueries in WHERE Clause

A subquery is a query that is written inside another query statement. Catalyst enables you to execute simple singular subqueries with WHERE clause. You can write subqueries with the WHERE clause in SELECT, UPDATE and DELETE queries.

The syntax of a subquery with WHERE clause is given below:

    

        
copy
        

            

        
    
    

         SELECT column_name FROM table_name WHERE column_name operator (SELECT column_name FROM table_name)
    

Example Database

The employee details of Zylker Technologies are being maintained in the Data Store in the Zylker_Employee_DB table. The table contains the following columns and rows:

An example subquery statement to list the salaries and the names of the employees who earn greater than the minimum salaried employee is given below:

    

        
copy
        

            

        
    
    

         SELECT Name, Salary FROM Zylker_Employee_DB WHERE Salary > (SELECT MIN (Salary) FROM Zylker_Employee_DB)