SQL

ลำดับตอนที่ #2 : SQL I - Getting Specific Rows

Getting Specific Rows

WHERE

To get specific rows, you can use the SQL keyword WHERE. We already know how to say which columns from which table we want to get rows, but with WHERE we can specifically say which rows to get.

Use the equals sign, in the examples below, to specify which rows you want to get from a table in the database. You can think of it like saying: "get any row where some column is ____" (where ____ is a value).

Let's Get "food" Rows!

Next, let's see how to get rows where the value in the "category" column is "food". Here, it's important to note the rules of data types in SQL. Text data like the word "food" must be put in quotes. We'll learn more about different data types in the next lesson.

Getting Rows from Various Columns with Conditions

Data Types

There are rules for storing data in databases called data types. These are used to display different kinds of data like text, numbers, and even date data.

Each column in a database has a different data type. The data types of the table we are using for this lesson are shown below on the left.

Number Data

Let's try to write a query to get all the rows that meet the condition "products with a price of $10". The price in the "price" column is saved as an integer data type. Note that, for number data like integers, quotes are not required.

Date Data

Next, we're going to get rows where "the date is 2018-10-10". The "date" is stored in the "purchased_at" column as a "date type". For date type data, double or single quotes are required. So, remember to always put quotes around dates!

Review of Data Types

It's important to take into consideration the various characteristics of each data type. For this lesson, though, you will only need to remember the 3 types shown in the figure below.

Getting Prices of "$10 or more"

Comparison Operators

In addition to = there are other symbols that can be used in a WHERE clause. They are called comparison operators. Like the greater than and less than operators, shown on the left below. Using these, you can create a "where the price column is greater than or equal to $10" condition to get those rows specifically.

In addition to number data, like the data in the "price" column, comparison operators can be used with "date type data" like in the "purchased_at" column. Remember to use quotes where needed, depending on the data type!

Rows that Include "Pudding"

LIKE

The LIKE Operator

When you want to get all rows that contain some specific characters, you can use the LIKE operator. As shown below, you can make a condition to get "rows that contain some string" for a specific column ("string" means a sequence of characters).

Wildcards

In order to use the LIKE operator, you'll need to also learn about wildcards. In the programming world, "wildcard" means a symbol that matches any string. With the LIKE operator, you can use the % symbol as a wildcard. The example below uses wildcards to get all rows that contain "pudding" anywhere in the specified column.

Using LIKE with Wildcards

 

Prefix Search

You can use wildcards at the start and end of a string. As shown below, "pudding%" will find "pudding" followed by any string. This allows you to search for any string that starts with "pudding" (any string you want to find). This kind of search is called a prefix search.

Postfix Search

You can also search for values that end with some specific string, like "%pudding" in the example below. Any string before "pudding" will be found. So, this allows you to search for strings that end with "pudding". This is called a postfix search.

Using Negative Conditions

NOT

The NOT Operator

To get rows where some value is not included or does not match exactly, you can use the NOT operator. Actually, you can use NOT in front of any of the operators we have learned so far. This will allow you to get all rows that do not meet the given condition.

Rows with Empty Columns

 

What is NULL?

 

IS NULL・IS NOT NULL

Getting Rows with NULL Values

In order to select rows that contain a NULL value for some column, we can use IS NULL. As in the example below, by using a condition like "where column_name IS NULL" we can find rows in which the value of a certain column is NULL.

Getting Rows Without NULL Values

On the other hand, if we want to get rows where the value for some column is not NULL, we can use IS NOT NULL. This works the same way as "IS NULL", but negates (reverses) the condition.

About Comparing Values with NULL

When you want to get rows where a certain column is or is not NULL, note that you cannot use the = operator. So, be careful to avoid making this mistake.

Finding Master White's Food

 

AND・OR

The AND Operator

If you use the AND operator, you can put multiple conditions in a WHERE clause. Using the structure "WHERE condition1 AND condition2", you can search for only rows that are true for both conditions.

The OR Operator

Like the AND operator, the OR operator can be used to make SQL statements with multiple conditions. Using the structure "WHERE condition1 OR condition2", you can search for rows that meet either one of the conditions (or both).