Where Clause in SQL

Last Updated: 22nd June, 2023

Overview

The WHERE clause in SQL filters records from a result set, frequently in conjunction with other clauses, such as SELECT, Update, or Delete. The WHERE clause indicates the conditions that must be met for a record to be included within the result set. This clause can be utilized to filter records based on various criteria, including certain values in indicated areas, certain expressions, and certain combinations of criteria.

The SQL WHERE Clause Syntax

Pushpa was working as a program designer at an expansive monetary services company. She was entrusted with creating a modern application permitting clients to get monetary information in real-time. After a few weeks of coding, Pushpa realized that she was required to utilize a WHERE clause in her SQL queries to guarantee that only the proper data was being recovered from the database. She spent a few days investigating the distinctive types of WHERE clauses and how best to apply them in her application.

SELECT column1, column2, ...
FROM table_name
WHERE condition1

The WHERE clause in SQL is utilized to indicate criteria that constrain the results of a query. The syntax comprises of a list of conditions that must be met for a record to be returned. Each condition is separated by either the AND or OR keyword, depending on how the conditions are evaluated. For illustration, in the event that you need to return records where both condition1 and condition2 must be genuine, at that point, you'd utilize the AND operator. The syntax for a WHERE clause is: SELECT column1, column2, ... FROM table_name WHERE condition1 AND/OR condition2 AND/OR condition3 AND/OR .

WHERE Clause Example

SELECT * 
FROM employees 
WHERE last_name = 'Smith' 
AND department = 'Finance';

This query will return all of the information from the employees table where the last name is Smith, and the department is Finance.

Text Fields vs. Numeric Fields

Text fields can be utilized within the WHERE clause of an SQL explanation to filter the comes about. In any case, they could be more productive than numeric fields. Text fields require extra processing time to change over the values into a usable format, while numeric fields don't. Also, text fields cannot be utilized in mathematical operations, such as numerical comparisons, which may be required for certain queries.

For illustration, in the event that you wanted to discover all the lines in a table with a specific text value, you'll utilize the following query:

SELECT *
FROM table
WHERE field_text = 'value';

This query would search the field_text column for the value 'value', and return any matching rows. However, if the field_text column were a numeric field, the same query would need to be written as:

SELECT *
FROM table
WHERE field_numeric = 'value';

This query would search the field_numeric column for the value 'value' and return any matching rows. Notice that the value must be enclosed in quotation marks since it is text-based. If the field_numeric column had been a text field, the query would have needed to include a conversion function to convert the value to a numerical value before comparing it.

Operators in The WHERE Clause

1. = : The equal operator is utilized to compare two expressions or values. In the event that the values in both the expressions rise to, at that point, it'll return true, else it'll return wrong.
2. >: The greater than operator is utilized to compare two expressions or values. If the value of the left expression is greater than the value of the proper expression, at that point, it'll return genuine, or else it'll return false.
3. < : The less than operator is utilized to compare two expressions or values. If the value of the left expression is less than the esteem of the correct expression, at that point, it'll return genuine, or else it'll return false.
4. >= : The greater than or equal to the operator compares two expressions or values. If the esteem of the cleared-out expression is more noteworthy than or equal to the esteem of the correct expression, at that point, it'll return genuine, or else it'll return false.
5. <= : The less than or equal to the operator is utilized to compare two expressions or values. In the event that the value of the left expression is less than or equal to the esteem of the proper expression, at that point, it'll return genuine, or else it'll return wrong.
6. <> : The not equal operator is utilized to compare two expressions or values. If the values in both the expressions are not break even, at that point, it'll return true, else it'll return false.
7. LIKE : The LIKE operator compares esteem to similar values utilizing wildcard operators. It can be utilized to look for a particular design in a column.
8. IN : The IN operator is used to indicate numerous values in a WHERE clause. It will return genuine if the value is shown within the list of indicated values, or it'll return wrong.
9. BETWEEN : The BETWEEN operator compares two expressions or values and checks in case esteem is between two indicated values. It'll return genuine if the value is in between the two indicated values, or it'll return wrong.

Examples

1. = : For example,

Customer Table

SELECT * FROM customers WHERE age = 25; 

This will return all customers with an age equal to 25.

2.  > : For example,

SELECT * FROM customers WHERE age > 25;

This will return all customers with age greater than 25.

3. < : For example,

SELECT * FROM customers WHERE age < 25; 

This will return all customers age less than 25.

4.  >=: For example, 

SELECT * FROM customers WHERE age >= 25; This will return all customers with an age greater than or equal to 25.

5.  <= : For example,

 SELECT * FROM customers WHERE age <= 25;

This will return all customers with age less than or equal to 25.

6. <> : For example,

SELECT * FROM customers WHERE age <> 25;

This will return all customers with age not equal to 25.

7. LIKE: For example,

SELECT * FROM customers WHERE name LIKE 'J%';

This will return all customers with name starting with the letter 'J'.

8. IN: For example,

SELECT * FROM customers WHERE age IN (20,25,30); 

This will return all customers with age 20, 25, or 30.

9. BETWEEN: For example,

SELECT * FROM customers WHERE age BETWEEN 20 AND 30; 

This will return all customers between age of 20 and 30

Conclusion

After investigating the diverse types of WHERE clauses in SQL, she was able to induce the application to work, and the clients could get to their monetary information in real-time. Pushpa's hard work and commitment had paid off.

Key takeaways

1. The WHERE clause is used in an SQL statement to filter data based on conditions specified in the WHERE clause.
2. The WHERE clause can be used to filter data based on a single criterion or multiple criteria.
3. The WHERE clause can be used in conjunction with the ORDER BY clause to sort data in a particular order.
4. The WHERE clause works with various comparison operators (e.g. =, <>, >, >=, etc.) to evaluate the conditions specified in the WHERE clause.
5. The WHERE clause can also be used in conjunction with the LIKE operator to search for specific patterns in the data.

Quiz

1. What is the correct syntax for using the WHERE clause in a SQL statement?  
   1. WHERE clause 
   2. WHERE(clause) 
   3. WHERE[clause] 
   4. WHERE clause;

Answer: d. WHERE clause;

2. Which of the following is not a valid comparison operator when using the WHERE clause? 
   1. < 
   2. LIKE  
   3. OVER  
   4. >

Answer: c. OVER

3. What is the proper syntax for selecting all records from a database table where a column's value is equal to a specific value? 
   1. SELECT * FROM table WHERE value = 'specific value'; 
   2. SELECT * FROM table WHERE 'specific value' = value; 
   3. SELECT * FROM table WHERE 'specific value'; 
   4. SELECT * FROM table WHERE value == 'specific value';

Answer: a. SELECT * FROM table WHERE value = 'specific value';

4. What is the proper syntax for selecting all records from a database table where a column's value is NOT equal to a specific value? 
   1. SELECT * FROM table WHERE value != 'specific value'; 
   2. SELECT * FROM table WHERE 'specific value' != value; 
   3. SELECT * FROM table WHERE 'specific value'; 
   4. SELECT * FROM table WHERE value <> 'specific value';

Answer: a. SELECT * FROM table WHERE value != 'specific value';