So far, we have queried all the data in the table, and the most we have been able to determine is to reduce the number of lines in the output and arrange them with a specific condition, but what if we want to review all the lines that fulfill a condition? How can we review books whose price exceeds a specific value? Or books created after a specific date?
We can filter information in a database by using conditions in our queries and adding conditions to our SQL statements, we can get only the records that meet specific criteria.
For instance, we can use a condition to retrieve all products with a price greater than a particular value, or all customers who live in a specific city. We can do this by using the WHERE clause in our SQL statement, which lets us specify the conditions that data must meet to be included in the result set.
By the end of this lesson, you’ll know how to use conditions in your queries to retrieve specific information from a database. You’ll have a better understanding of how to apply this knowledge, and you’ll be able to get the data you need more efficiently.
SQL WHERE Clause
Most likely we will set conditions in most of the queries that we will write in the future, for many reasons, the most important of which is to specify the data we want to fetch, delete or modify. Setting conditions is very easy and essential when dealing with data specifically, and here are some cases in which we must deal with conditions:
- If we want to delete a specific user from the database, we must put a condition in the query that we want to delete exactly that person, not all users.
- If we want to count the number of people born in a given year, we have to set a condition in which we specify the year number.
- If we want to know the number of male users and the number of female users, we have to set a condition in which you specify the gender of the people we will count each time and so on.
In general, in PostgreSQL, we use the WHERE command to set the conditions and with this word, you must use one of the following symbols:
=– This equal operator is used with numbers, text, and dates to execute the command if the values being compared are equal.!=– This is used with numbers, text, and dates to execute the command if the values being compared are not equal.<>– This is used with numbers, text, and dates to execute the command if the values being compared are not equal like the symbol!=.>– This is used with numbers and dates to execute the command if the values being compared are greater than a specific value.>=– This is used with numbers and dates to execute the command if the values being compared are greater than or equal to a specific value.<– This is used with numbers and dates to execute the command if the values being compared are smaller than a specific value.<=– This is used with numbers and dates to execute the command if the values being compared are smaller or equal to a specific value.
If we want to put more than one condition or more than one option to determine whether to execute commands, we can use the following words.
AND– We use this operator to execute the order if the condition placed before it and the condition placed after it is true. Here both conditions must be met for the command to be executed.OR– We use this operator to execute the order if the condition set before it or the condition placed after it is true. Here it is enough for one of the two conditions to be fulfilled in order for the order to be executed.BETWEEN value1 AND value2– We use this sentence to execute the command if the value exists between two values.
When we want to access a specific line in any table, we have to deal with the field in it, which cannot contain duplicate values. For example, we can’t rely on people’s names because we may find more than one person with the same name.
Now, since the only thing that can’t be repeated in the table we’re dealing with is the title (Usually, the non-repeated value is the id, but in our example here. We have inserted two rows with the same id, and we’re going to talk about why later on. We can rely on it to differentiate between one book and another.
Greater Than or Equal Operator in PostgreSQL
The following query fetches all the information of the books that own the ID equal to or greater than 4.
SELECT * FROM books WHERE id >= 4;
Not Equal Operator in PostgreSQL
This query retrieves data for all books, except for the book with an ID of 6.
SELECT * FROM books WHERE id != 6; OR SELECT * FROM books WHERE id <> 6;
We get the same result:
Greater Than Operator in PostgreSQL
This query retrieves data for all books with the price >20 0r the name is Docker:
SELECT * FROM books WHERE price > 20 OR title='Docker';
This query checks if the price is greater than 20. If it is, the result is returned without checking if the title is ‘Docker‘. The query uses the OR operator, which means either condition can be true for the result to be returned. Understanding logical operators like OR can help create more sophisticated queries for working with data effectively.
Equal Operator in PostgreSQL
The following query brings information for each books with the id = 6 and its name is ‘Rust‘.
SELECT * FROM books WHERE id = 6 AND title='Rust';
Note: Even if there are two books with the same ID (ID=6), they will only be included in the results if their names are the same. Both conditions must be true for a book to be included.
The following query brings information for each book that has an id equal to 1 or 5 or 8.
SELECT * FROM books WHERE id IN (1,5,8);
The CASE statement enables us to use a syntax that is similar to the switch structure found in most programming languages.
It can be compared to a series of sequential if-statements that allow us to perform specific tests on other values. In the example provided in the chapter, the column contains simple integers.
Conclusion
In this section, we discuss how to filter data in PostgreSQL using the WHERE command with various comparison operators like =, !=, <, >, <=, >=. We also emphasize the importance of setting conditions while querying data.
