Operators in The Where Clause
Operators in The Where Clause
In the SQL WHERE clause, various operators can be used to specify the condition for filtering data. Here are some commonly used operators in the WHERE clause:
- Comparison Operators Comparison operators are used to compare two values and return a Boolean value (True or False) based on the comparison. The comparison operators are:
- = : Equal to
- <> or != : Not equal to
- < : Less than
- > : Greater than
- <= : Less than or equal to
- >= : Greater than or equal to
Example: Retrieve all customers whose age is greater than 25.
SELECT * FROM customers
WHERE age > 25;
- Logical Operators Logical operators are used to combine two or more conditions to form a more complex condition. The logical operators are:
- AND : Returns true if both conditions are true
- OR : Returns true if at least one of the conditions is true
- NOT : Negates the condition (returns true if the condition is false)
Example: Retrieve all customers whose age is between 20 and 30 and whose country is ‘USA’.
SELECT * FROM customers
WHERE age BETWEEN 20 AND 30 AND country = 'USA';
- Wildcard Operators Wildcard operators are used to match patterns in string values. The wildcard operators are:
- % : Matches any sequence of characters (zero or more)
- _ : Matches any single character
Example: Retrieve all customers whose last name starts with ‘S’.
SELECT * FROM customers
WHERE last_name LIKE 'S%';
- IN Operator The IN operator is used to compare a value to a list of possible values. It returns true if the value matches any value in the list.
Example: Retrieve all customers whose country is either ‘USA’, ‘Canada’, or ‘Mexico’.
SELECT * FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');
- BETWEEN Operator The BETWEEN operator is used to compare a value to a range of values. It returns true if the value is within the specified range.
Example: Retrieve all orders whose order date is between January 1, 2022 and December 31, 2022.
SELECT * FROM orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';
These are some commonly used operators in the SQL WHERE clause. By using these operators, you can filter data based on specific conditions and retrieve only the data that meets those conditions.