SQL CRUD Basics Part 2 – Read

In SQL CRUD Basics Part 1 – Create, we learned how to create new rows of data in a database table with the INSERT statement. In this post, we are going to visit the busiest statement in SQL – SELECT. If you want to view or read the stored data in a table, you use SELECT.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

• OpenSuse Leap 15.1
• MySQL 8.0.18

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

For the most part, this post will utilize tables from the Sakila database which mimics a mock DVD rental store.

Below is the description of the ‘category’ table:

Basic SELECT syntax follows this pattern:

To see data from all columns in the table, list them individually, each separated by a comma:

There is a wild card character you can use to retrieve all columns from the table without explicitly listing them out in the SELECT columns list:

Tip: Unless you are using one-off ad-hoc queries (queries that are on-the-fly or for exploration), it is generally best practice to list out each individual column in the SELECT list.

By specifying the * (asterisk) wild card character, all columns and rows in the table are returned in the results set.

You can retrieve just one or any combination of columns, so long as whatever column you specify, exists in the table. In this example, I query for data from the ‘category_id’ and ‘name’ column:

Including a column name that does not exist in the table, returns an error:

You are free to SELECT columns in any order you like:

Choosing to view the data from only a single column is just fine as well:

But, be conscience of any trailing comma after the last column of your SELECT list:

I like to think of this rule in a couple of different ways. One, the last column of your SELECT list must not have a following comma. Or, just think along the lines of there cannot be a comma preceding the FROM (clause) keyword.

Filtering rows of data

You have likely noticed that all of the query results so far have one thing in common: each has returned 16 rows of data. Regardless of how many columns have been selected.

Reasoning? Well, for starters, that is the current number of rows in the table.

And herein lies an important concept in regards to SELECT. It does not filter the number returned rows. It only provides the columns you specify in the column list.

So how is the number of rows (possibly) constrained?
With the WHERE clause.

While the SELECT clause provides the columns you see in a query result, restriction of rows – that those columns are part of – is based on a BOOLEAN predicate test in the WHERE clause. A BOOLEAN predicate what?

Think of 2 + 2 = 4. That is correct, right? That is a true statement. If this had been a BOOLEAN predicate test, we can say that it evaluated to TRUE.

So how does this apply to the WHERE clause? Several operators can be used in the WHERE clause predicate test. They are:

• < – less than
• > – greater than
• < = – less than or equals to
• >= – greater than equals to
• = – equals to (equality)
• <> – not equals to (Some SQL dialects accept != as well.)

Recall the ‘category_id’ column is a tinyint(3) data type so you can test for equality on that column with an INTEGER (whole number) value as shown in the next query:

In this particular query, for a row to be included the returned result set, its ‘category_id’ column value must be 4. Had multiple rows contained a value of 4 for that column, they would have been included in the returned results.

Queries involving string values in the WHERE clause can also be tested for equality on columns with string values.

Notice the use of the wild card asterisk (*) character, displaying all column names for the only row returned.

On the same token, to retrieve rows in which a column value is not equal to a particular value, use the not equals to operator (<>):

We can query for multiple (possible) rows to be returned using less than (< ) and greater than (>) operators:

The only rows returned are in fact TRUE according to the WHERE clause predicate, which specifies that the ‘category_id’ column value has to be less than 5. Easy peasy you see.

Likewise, in this query, no rows will be included in the returned result set unless that row’s ‘category_id’ column value is greater than 12:

The BETWEEN operator permits you to specify a range of values to be tested. For a row to be included in the results set, whatever value is part of the WHERE clause predicate must be in between the specified range. See this next query for an example:

You can also absolutely negate the BETWEEN operator using the NOT keyword, which in essence, has an opposite effect:

In this query, rows are not included in the result set if their ‘category_id’ column value falls between the numbers 5 and 12.

Multiple Conditionals

Using the operators AND and OR enables chaining (termed loosely here) multiple BOOLEAN predicate test in the WHERE clause. Let’s understand with an example.

Below is the structure of the Sakila database’s ‘payment’ table, which I will use to query information regarding customer id’s and payment amounts.

Let’s grab any rows with a ‘payment_id’ column value of 10 or less.

You can see in the result set below, by using the AND operator, the final number of rows has been filtered down to 4, versus the 10 from the query in which only the ‘payment_id’ column value was tested in the WHERE clause.

Here’s why.
In order for a row to be returned, not only does the ‘payment_id’ column value have to be less than or equal to 10, but now by using the AND operator, so too must the ‘amount’ column be no greater than 3.00 (dollars I’m guessing).

When the AND operator is present, all BOOLEAN predicate tests must return true for that row to be included in the final results. I like to think of the AND operator as being more restrictive on the result set rows.

On the other hand, the OR operator requires only 1 of the predicate test conditionals to return TRUE for a row to be included in the result set.

The next query uses a couple of OR operators for demonstration of its functionality:

Visiting these results, you can see the rows with ‘category_id’ value BETWEEN 5 and 10 are included. However, rows with a ‘name’ column values of ‘Action’ and ‘Travel’ are also included. Even though those 2 row’s ‘category_id’ values are not between 5 and 10, they do pass one of the names BOOLEAN predicate test specified by the OR operator.

I look at OR as less restricting than AND, in which all the BOOLEAN predicate tests (each side of AND) must be TRUE.

Recommended Reading

If interested, visit these links for more information on WHERE, AND, OR, and BETWEEN:

• MySQL OR Condition
• MySQL AND Operator
• MySQL BETWEEN
• MySQL – WHERE Clause

The WHERE clause is integral for filtering out rows of a result set, keeping those rows you actually want or need based on some condition. While I have not given every possible scenario of filtering with WHERE, you now understand what it is used for and the principle behind it. You’re well on your way to SQL CRUD Mastery. In the next post, we will learn all about the Update portion of CRUD, using the SQL UPDATE command.

Subscribe to the blog via the button in the sidebar so you don’t miss SQL CRUD Basics: Update when it releases.

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.