Using the WHERE Clause to Specify Rows
The WHERE clause in SQL database management systems is used to specify certain logical expression conditions in a database table. This allows table operations such as selecting, updating, or deleting rows in the existing database table. The WHERE clause provides database users with the ability to filter table database data based on specific expression criteria, which helps database users control and manage which relevant table data and information are affected.
The purpose of the WHERE clause in SQL database management systems.
- The WHERE clause in SQL database management systems is used to perform database operation logical conditions.
- This includes filtering table rows based on certain conditions when performing data queries in the database table using the (SELECT) statement command.
- Using the (UPDATE) command statement to update specific table rows in a database table.
- Using the (DELETE) command statement to delete a particular table row from a database table.
- Remember, without the WHERE clause statement in a database table, all rows of the existing table will be modified or affected. Therefore, it is very important to use the WHERE clause properly in the table to avoid unwanted changes.
Syntax of the WHERE clause in SQL database.
The basic syntax of the WHERE clause in an SQL database table is as follows.
SELECT column1, column2, …
FROM table_name
WHERE condition;
Elements of the WHERE clause.
condition – Here, the WHERE clause condition in the database table is a user-defined logical expression that defines the user-defined criteria and conditions for selecting, updating, or deleting rows in the existing table.
Common comparison operators in the WHERE clause in SQL database.
In SQL database tables, user-defined comparison operators are commonly added or performed in the WHERE clause. Comparison operators are used in the table to compare values in the database based on the given condition. Here we will learn more about the commonly used comparison operators.
Common comparison operators used in the WHERE clause.
- = – Equal to comparison operator
- > – Greater than comparison operator
- < – Less than comparison operator
- >= – Greater than or equal to comparison operator
- <= - Less than or equal to comparison operator
- <> or != – Not equal to comparison operator
Example of using Equal to comparison operators in WHERE.
Equal to comparison operator.
= – Equal to.
SELECT * FROM employee
WHERE department = ‘Design’;
This will display all the table rows in the existing employee table where the department column is ‘Design’.
> – Greater than comparison operator.
Greater than (>).
SELECT * FROM employee
WHERE salary > 33000;
This will display all the employee rows in the existing employee table whose monthly salary is greater than 33000.
< – Less than comparison operator.
Less than (<).
SELECT * FROM employee
WHERE emp_age < 40;
This will display all the employee rows in the existing employee table whose age is less than 40 years.
Not equal to comparison operator.
Not equal to (<> or !=).
SELECT * FROM employee
WHERE department <> ‘Development’;
This will display all the employee rows in the existing employee table that are not in the ‘Development’ department.
Logical operators in the WHERE clause in SQL database.
In SQL database management, database users can add multiple logical condition expressions using logical operators to improve the logic in a table query. The main popular logical operators used are.
Common Logical operators used in the WHERE clause.
- AND – In the AND logical operator, all the given conditions must be true.
- OR – In the OR logical operator, at least one of the given conditions must be true.
- NOT – The NOT logical operator reverses the given condition.
Examples of using logical operators in an SQL database.
Using the AND logical operator.
SELECT * FROM employe
WHERE department = ‘Development’ AND salary > 30000;
This will display all the employee rows from the employee table where the department is ‘Development’ AND the salary is greater than 30,000. Here, both conditions must be true simultaneously.
Using the OR logical operator.
SELECT * FROM employe
WHERE department = ‘Design’ OR department = ‘Development’;
This will display all the employee rows from the employee table where the employee is either in the ‘Design’ department OR the ‘Development’ department. Here, at least one of the two conditions must be true.
Using the NOT logical operator.
SELECT * FROM employe
WHERE NOT department = ‘Marketing’;
This will display all the employee rows from the employee table where the employee’s department is NOT ‘Marketing’.
Using BETWEEN, IN, LIKE, and IS NULL in the WHERE clause of SQL database management.
In SQL database management systems, special database operators such as BETWEEN, IN, LIKE, and IS NULL can also be used with the WHERE clause.
BETWEEN Special Database Operator.
The BETWEEN operator in SQL database tables is used to filter table data information within a particular fixed range. This operator can be used in number, text, and date columns.
Example of the BETWEEN Special operator.
SELECT * FROM employee
WHERE emp_age BETWEEN 20 AND 30;
This will display all employee rows in the existing employee table where the employee’s age is between 20 and 30 years, including employees aged 20 and 30.
IN Special Database Operator.
The IN operator in SQL database tables allows the database user to specify multiple user-defined values in the WHERE clause. It is generally a shorthand method for multiple OR logical operator conditions.
Example of the IN Special operator.
SELECT * FROM employee
WHERE department IN (‘Design’, ‘Development’, ‘Marketing’);
This will display all employee rows in the existing employee table that belong to the Design, Development, or Marketing departments.
LIKE Special Database Operator.
The LIKE operator in SQL database tables is used for pattern matching in string values. The LIKE operator is most often used with wildcards.
Elements of the LIKE Special Operator.
- % – This displays zero or more characters.
- _ – This displays a single character.
Example of the LIKE Special Operator.
SELECT * FROM employee
WHERE first_name LIKE ‘S%’;
This will display all employee rows in the existing employee table. Employees whose first_name starts with the character ‘S’.
SELECT * FROM employe
WHERE first_name LIKE ‘S__i’;
This will display all employee rows in the existing employee table where the employee’s first_name is three characters long, starts with the character ‘S’, and ends with ‘i’ (e.g., “Siddhi”).
IS NULL Special Database Operator.
The IS NULL operator in SQL databases is used to check if a table column contains a NULL value.
Example of the IS NULL Special Operator.
SELECT * FROM employe
WHERE hire_date IS NULL;
This will display all employee rows in the existing employee table where the employee does not have a hire_date. That is, the hire_date for those employees is defined as NULL.
Using WHERE in UPDATE and DELETE Statements in SQL Databases.
The WHERE clause in SQL databases is used in UPDATE and DELETE statements to indicate which table rows should be modified or deleted.
Using WHERE in the UPDATE statement in SQL Databases.
UPDATE employe
SET salary = 60000
WHERE department = ‘Design’ AND emp_age > 20;
This will set the salary to 60000 for all employees in the Design department in the existing employee table whose age is greater than 20.
Using WHERE in the DELETE statement in SQL Databases.
DELETE FROM employe
WHERE department = ‘Design’ AND age < 31;
This will delete all employees from the existing employee table who are in the Design department and whose age is less than 31.
Combining multiple conditions with parentheses in SQL databases.
When using multiple logical operators (AND, OR, NOT) in SQL database tables, parentheses can be used to group multiple logical conditions and control the order of evaluation.
Example of combining multiple conditions with parentheses.
SELECT * FROM employee
WHERE (department = ‘Development’ OR department = ‘Marketing’)
AND emp_age > 20;
This will display all employee rows in the existing employee table that belong to either the Development or Marketing department, and whose age is greater than 20 years.
Conclusion of Using the WHERE Clause to Specify Rows.
- The WHERE clause in SQL database tables is essential for filtering and controlling which rows in the existing database table are selected, updated, or deleted. By using appropriate comparison operators, logical operators, and pattern matching special operators, you can improve database queries and efficiently manage more complex database data manipulation tasks.
- Comparison operators You can use the =, >, <, >=, <=, <> operators to filter table data based on values.
- Logical operators Database users can use the AND, OR, NOT logical operators to combine multiple conditions.
- Special operators Database users can use the BETWEEN, IN, LIKE, IS NULL operators for more advanced and complex database table filtering.
- Ensure proper use of the WHERE clause in UPDATE and DELETE statements to avoid unintended consequences on all rows in the table during transactions and operations.
