How to Filter Data in SQL Using Comparison and Logical Operators Guide(3)
SQL is a powerful language for manipulating data in databases. You can use SQL to create, update, delete, and query data from tables. But sometimes, you may not want to work with all the data in a table. You may want to filter out some rows based on certain criteria. For example, you may want to find all customers who live in a certain city, or all products that have a price above a certain threshold. How can you do that in SQL?
The answer is to use the WHERE clause. The WHERE clause allows you to specify one or more conditions that the rows in a table must satisfy in order to be included in the result set. You can use various operators to form these conditions, such as comparison operators, logical operators, and special operators like BETWEEN, IN, and LIKE. In this blog post, we will explain how to use these operators to filter data in SQL.
Comparison Operators:
Comparison operators are used to compare two values and return a logical value of TRUE, FALSE, or UNKNOWN. The following table shows the comparison operators in SQL: Table
You can use comparison operators with numeric, character, date, and time values. For example, the following query finds all employees who have a salary greater than 10,000:
The following query finds all employees who have a last name that starts with ‘S’:
Note that we used the LIKE operator with a wildcard character (%) to match any string that starts with ‘S’. We will explain more about the LIKE operator later.
Logical Operators:
Logical operators are used to combine two or more conditions and return a logical value of TRUE, FALSE, or UNKNOWN. The following table shows the logical operators in SQL: Table
You can use logical operators to create complex filter conditions. For example, the following query finds all employees who work in the department 2 and have a salary greater than 10,000:
You can use parentheses to change the order of evaluation of the logical operators. For example, the following query finds all employees who work in the department 5 or have a salary greater than 12,000, but not both:
BETWEEN Operator:
The BETWEEN operator is used to check if a value is within a range of two values. The syntax of the BETWEEN operator is as follows:
value BETWEEN lower_bound AND upper_bound
You can also use the NOT operator with the BETWEEN operator to negate the condition. For example, the following query finds all salaries that have a range outside the range of 2500 and 2900.
IN Operator:
The IN operator is used to check if a value is within a list of values. The syntax of the IN operator is as follows:
value IN (value1, value2, …, valueN)
The value can be any expression that returns a single value. The value1, value2, …, valueN can be constants, variables, or expressions that return single values. The IN operator returns TRUE if the value is equal to any of the values in the list. Otherwise, it returns FALSE.
For example, the following query finds all employees who has the following employee id:
LIKE Operator:
The LIKE operator is used to check if a value matches a pattern. The syntax of the LIKE operator is as follows:
value LIKE pattern
The value can be any expression that returns a single value. The pattern can be a constant, variable, or expression that returns a single value. The pattern can contain two special characters: the percent sign (%) and the underscore (_). The percent sign matches any sequence of zero or more characters. The underscore matches any single character.
For example, the following query finds all employees who have an email address that in between with ‘%haan%’.
SELECT employee_id, first_name, last_name, email
FROM employees
WHERE email LIKE ‘%haan%’;
The following query finds all employees who have a first name that contains the letter ‘a’:
SELECT employee_id, first_name, last_name, email
FROM employees
WHERE first_name LIKE ‘%a%’;
The following query finds all employees who have a last name that has exactly three letters:
SELECT employee_id, first_name, last_name, email
FROM employees
WHERE last_name LIKE ‘___’;
You can also use the NOT operator with the LIKE operator to negate the condition. For example, the following query finds all employees who do not have an email address that ends with ‘a.org’:
SELECT employee_id, first_name, last_name, email
FROM employees
WHERE email NOT LIKE ‘%l.org’;
Conclusion:
In this blog post, we have learned how to filter data in SQL using comparison and logical operators. We have also learned how to use special operators like BETWEEN, IN, and LIKE to create more complex filter conditions. Filtering data is an essential skill for any SQL user, as it allows you to extract the relevant information from a large table.Credit :SQL Query from :sqltutorial.org
If you enjoyed this blog post and found it helpful, please like it and follow me here and as well LinkedIn. I will be posting more articles on SQL and other topics related to data and AI.
This blog post is part of a series of SQL guides that I have created for students and beginners who want to learn SQL from scratch. If you want to join this SQL series learning community, please subscribe to me and get notified of new posts.
If you want to learn more in detail about SQL A to Z and how to use it to manipulate and analyze data, then check out this online course that offers online learning and tests: — -> learnsql.com. You will also get access to exercises and solutions to practice and test your skills.
For more information about SQL you can refer to My ebook SQL for Students on Gumroad. This ebook covers all the topics you need to know to about SQL, with clear explanations and examples.
I hope you have learned something new and useful from this blog post. Thank you for reading and happy learning!
“Data is the new oil. It’s valuable, but if unrefined it cannot really be used. It has to be changed into gas, plastic, chemicals, etc to create a valuable entity that drives profitable activity; so must data be broken down, analyzed for it to have value.” — Clive Humby
Comments
Post a Comment