The WHERE clause is used to filter records in a SELECT statement. The WHERE clause is optional, and adds conditional restrictions to the SELECT statement that will help limit the result set. It only displays the records that fit the condition listed in the WHERE clause. By using the WHERE clause, you can easily answer questions like:
SELECT *
FROM customer
WHERE customer_id = 5;
Notice that in the WHERE clause, we define the column (customer_id), the comparison operator (=), and the value that we wanted to compare it to (5).
If there are no rows that match the criteria in the WHERE clause, you should see a message similar to the following:
SELECT *
FROM customer
WHERE customer_id = 1000;
Note that SQL requires single quotes around text values. Numeric values should not be enclosed in quotes. Here is an example of what would happen if we forgot to include quotes around the text value 'Helena':
SELECT *
FROM customer
WHERE first_name = Helena;
We would get an error message:
This is because the database thinks the text value is a column. This could also present a problem if the text value is also an actual column. You would not get an error message; however, the results would not be what wanted either.
To properly use the WHERE clause, you would use the single quotes around the text values:
SELECT *
FROM customer
WHERE first_name = 'Helena';
We looked at the = operator above, but there are many other operators that can be used in the WHERE clause. Other comparison operators include:
= means equal to
< means less than
<= means less than or equal to
> means greater than
>= means greater than or equal to
<> means not equal to
Let us find the invoices that have a total greater than 14.
SELECT *
FROM invoice
WHERE total > 14;
The result set includes 12 rows. If we change the WHERE clause to >= 14, and include all invoices with the value of 14, the result set goes from 12 rows to 61 rows returned.
SELECT *
FROM invoice
WHERE total >= 14;
When it comes to integer values being compared, there would be no difference between using these two statements:
SELECT *
FROM invoice
WHERE total >= 15;
or
SELECT *
FROM invoice
WHERE total > 14;
However, if the total contained decimal values, these two statements would not be equivalent. The second statement would return rows with a total value between 14 and 15, like 14.5, whereas the first one would not.
[MUSIC PLAYING] Using the where clause in the select statement, you have the ability to be able to filter out data within the table itself. Utilizing the query select star from customer, we're returning every single row within the table itself. However, there's lots of instances in which we don't want to do so. We want to be able to filter out the details.
So for example, if we're looking for a specific customer ID, we can add in a where. We enter in the column name and equal to the specific value. In this case here, we'll enter in 5. So this should return just one row with the customer ID equal to 5, which we'll see here.
Another option as well, is that you can actually select based on different ranges. So for example, if you want to search for the customer ID with all the different values less than 5, it should return four rows with customer ID between 1 and 4.
We also have the option to be able to search on text. For example, if we're looking for those that live in Canada-- we're looking for the country, equal-- one thing to note is that when it comes to text within Postgres, you have to ensure that you are utilizing single quotes around the text.
If you don't enter in single quotes around the text, what you'll see is an error where it identifies that it's looking for a column, saying that the column Canada does not exist, being that this is a string literal. So because of that, we need to enter in single quotes to be able to have it work correctly. Now we have all eight rows returned. All of the individuals are from Canada.
[MUSIC PLAYING]
Source: Authored by Vincent Tran
