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:
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:
|IS||TRUE if the operand is the same as the value|
|IS NULL||TRUE if the operand is a null value|
|IS NOT NULL||TRUE if the operand is not a null value|
|=!||Not equal to|
|LIKE||TRUE if the operand matches a pattern|
|NOT LIKE||TRUE if the operand does not match a pattern|
|BETWEEN||TRUE if the operand value is between the start and end values|
|IN||TRUE if the operand is equal to a list of expressions|
|NOT IN||TRUE if the operand is not equal to a list of expressions|
|>=||Greater than or equal to|
|<=||Lesser than or equal to|
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:
SELECT MovieName, ShowDate FROM Movies WHERE ShowDate IS '2018-07-14'
This will generate the following output:
|Hotel Transylvania 3: Summer Vacation||2018-07-14|
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:
SELECT column_name(s) FROM base_table_name WHERE condition_1 AND|OR condition_2.. AND|OR condition_5
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:
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:
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:
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.
To update the records where the movie names begin with the letter ‘S’ in the Movies table, execute the following query:
UPDATE Movies SET ShowDate='2018-07-17' WHERE MovieName like 'S*'
This will update the following record and set the value:
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:
DELETE FROM base_table_name WHERE condition BETWEEN Value1 AND Value2
To delete the records where the value of the MovieID is between 2056 and 2059 in the Movies table, execute the following query:
DELETE FROM Movies WHERE MovieID BETWEEN 2056 AND 2059
The following records from the table will be deleted:
|2057||Ant-Man and the Wasp||2018-07-13||14:20:00||052|
|2058||Hotel Transylvania 3: Summer Vacation||2018-07-14||17:00:00||052|
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.
The general syntax for column comparison within the same table is as follows:
SELECT * FROM tablename WHERE columnname1 = tablename.columnname2
Select MovieName FROM Movies WHERE Actor = Movies.Producer
|The White River Kid|
UPDATE Movies SET self_produced = "Yes" WHERE Actor = Movies.Producer
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 :
SELECT tablename1.columnname1, tablename2.columnname1 FROM tablename2 LEFT JOIN tablename1 ON tablename2.columnname1 = tablename1.columnname2 WHERE tablename1.columnname1 = tablename2.columnname2
SELECT Movies.name, Theatres.city from Theatres left join Movies on Theatres.ROWID = Movies.theatreID where Movies.distributionCity = theatre.city
|Dark Knight Rises||Los Angeles|
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:
SELECT column_name FROM table_name WHERE column_name operator (SELECT column_name FROM table_name)
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:
|ZT-001||Amelia Burrows||Product Management||15000|
|ZT-2001||Bruce Wayne||Sr. Management||85000|
|ZT-239||Clark Kane||Media Relations||85000|
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:
SELECT Name, Salary FROM Zylker_Employee_DB WHERE Salary > (SELECT MIN (Salary) FROM Zylker_Employee_DB)
Last Updated 2023-08-29 12:29:32 +0530 +0530
Send your feedback to us