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.
OS and DB used:- 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:
1 2 3 4 5 6 7 8 9 | mysql> DESC category; +-------------+---------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+-------------------+-----------------------------------------------+ | category_id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(25) | NO | | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +-------------+---------------------+------+-----+-------------------+-----------------------------------------------+ 3 rows in set (0.00 sec) |
Basic SELECT
syntax follows this pattern:
1 2 3 | SELECT column_name or multiple_columns FROM table_name_here WHERE some_predicate_here; --optional |
SELECT
statement. However, they are not covered in this post.)
To see data from all columns in the table, list them individually, each separated by a comma:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> SELECT category_id, name, last_update -> FROM category; +-------------+-------------+---------------------+ | category_id | name | last_update | +-------------+-------------+---------------------+ | 1 | Action | 2006-02-15 04:46:27 | | 2 | Animation | 2006-02-15 04:46:27 | | 3 | Children | 2006-02-15 04:46:27 | | 4 | Classics | 2006-02-15 04:46:27 | | 5 | Comedy | 2006-02-15 04:46:27 | | 6 | Documentary | 2006-02-15 04:46:27 | | 7 | Drama | 2006-02-15 04:46:27 | | 8 | Family | 2006-02-15 04:46:27 | | 9 | Foreign | 2006-02-15 04:46:27 | | 10 | Games | 2006-02-15 04:46:27 | | 11 | Horror | 2006-02-15 04:46:27 | | 12 | Music | 2006-02-15 04:46:27 | | 13 | New | 2006-02-15 04:46:27 | | 14 | Sci-Fi | 2006-02-15 04:46:27 | | 15 | Sports | 2006-02-15 04:46:27 | | 16 | Travel | 2006-02-15 04:46:27 | +-------------+-------------+---------------------+ 16 rows in set (0.00 sec) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> SELECT * FROM category; +-------------+-------------+---------------------+ | category_id | name | last_update | +-------------+-------------+---------------------+ | 1 | Action | 2006-02-15 04:46:27 | | 2 | Animation | 2006-02-15 04:46:27 | | 3 | Children | 2006-02-15 04:46:27 | | 4 | Classics | 2006-02-15 04:46:27 | | 5 | Comedy | 2006-02-15 04:46:27 | | 6 | Documentary | 2006-02-15 04:46:27 | | 7 | Drama | 2006-02-15 04:46:27 | | 8 | Family | 2006-02-15 04:46:27 | | 9 | Foreign | 2006-02-15 04:46:27 | | 10 | Games | 2006-02-15 04:46:27 | | 11 | Horror | 2006-02-15 04:46:27 | | 12 | Music | 2006-02-15 04:46:27 | | 13 | New | 2006-02-15 04:46:27 | | 14 | Sci-Fi | 2006-02-15 04:46:27 | | 15 | Sports | 2006-02-15 04:46:27 | | 16 | Travel | 2006-02-15 04:46:27 | +-------------+-------------+---------------------+ 16 rows in set (0.00 sec) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> SELECT category_id, name FROM category; +-------------+-------------+ | category_id | name | +-------------+-------------+ | 1 | Action | | 2 | Animation | | 3 | Children | | 4 | Classics | | 5 | Comedy | | 6 | Documentary | | 7 | Drama | | 8 | Family | | 9 | Foreign | | 10 | Games | | 11 | Horror | | 12 | Music | | 13 | New | | 14 | Sci-Fi | | 15 | Sports | | 16 | Travel | +-------------+-------------+ 16 rows in set (0.01 sec) |
Including a column name that does not exist in the table, returns an error:
1 2 | mysql> SELECT last_update, first_name FROM category; ERROR 1054 (42S22): Unknown column 'first_name' in 'field list' |
You are free to SELECT
columns in any order you like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> SELECT name, category_id FROM category; +-------------+-------------+ | name | category_id | +-------------+-------------+ | Action | 1 | | Animation | 2 | | Children | 3 | | Classics | 4 | | Comedy | 5 | | Documentary | 6 | | Drama | 7 | | Family | 8 | | Foreign | 9 | | Games | 10 | | Horror | 11 | | Music | 12 | | New | 13 | | Sci-Fi | 14 | | Sports | 15 | | Travel | 16 | +-------------+-------------+ 16 rows in set (0.00 sec) |
Choosing to view the data from only a single column is just fine as well:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> SELECT last_update -> FROM category; +---------------------+ | last_update | +---------------------+ | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | | 2006-02-15 04:46:27 | +---------------------+ 16 rows in set (0.00 sec) |
But, be conscience of any trailing comma after the last column of your SELECT
list:
1 2 | mysql> SELECT last_update, category_id, FROM category; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM category' at line 1 |
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:
1 2 3 4 5 6 7 8 9 | mysql> SELECT category_id, name -> FROM category -> WHERE category_id = 4; +-------------+----------+ | category_id | name | +-------------+----------+ | 4 | Classics | +-------------+----------+ 1 row in set (0.01 sec) |
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.
1 2 3 4 5 6 7 8 9 | mysql> SELECT * -> FROM category -> WHERE name = 'Comedy'; +-------------+--------+---------------------+ | category_id | name | last_update | +-------------+--------+---------------------+ | 5 | Comedy | 2006-02-15 04:46:27 | +-------------+--------+---------------------+ 1 row in set (0.00 sec) |
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 (<>):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> SELECT name -> FROM category -> WHERE name <> 'Games'; +-------------+ | name | +-------------+ | Action | | Animation | | Children | | Classics | | Comedy | | Documentary | | Drama | | Family | | Foreign | | Horror | | Music | | New | | Sci-Fi | | Sports | | Travel | +-------------+ 15 rows in set (0.00 sec) |
We can query for multiple (possible) rows to be returned using less than (< ) and greater than (>) operators:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> SELECT * -> FROM category -> WHERE category_id -> < 5; +-------------+-----------+---------------------+ | category_id | name | last_update | +-------------+-----------+---------------------+ | 1 | Action | 2006-02-15 04:46:27 | | 2 | Animation | 2006-02-15 04:46:27 | | 3 | Children | 2006-02-15 04:46:27 | | 4 | Classics | 2006-02-15 04:46:27 | +-------------+-----------+---------------------+ 4 rows in set (0.13 sec) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> SELECT * -> FROM category -> WHERE category_id > 12; +-------------+--------+---------------------+ | category_id | name | last_update | +-------------+--------+---------------------+ | 13 | New | 2006-02-15 04:46:27 | | 14 | Sci-Fi | 2006-02-15 04:46:27 | | 15 | Sports | 2006-02-15 04:46:27 | | 16 | Travel | 2006-02-15 04:46:27 | +-------------+--------+---------------------+ 4 rows in set (0.00 sec) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> SELECT * -> FROM category -> WHERE category_id BETWEEN 5 AND 12; +-------------+-------------+---------------------+ | category_id | name | last_update | +-------------+-------------+---------------------+ | 5 | Comedy | 2006-02-15 04:46:27 | | 6 | Documentary | 2006-02-15 04:46:27 | | 7 | Drama | 2006-02-15 04:46:27 | | 8 | Family | 2006-02-15 04:46:27 | | 9 | Foreign | 2006-02-15 04:46:27 | | 10 | Games | 2006-02-15 04:46:27 | | 11 | Horror | 2006-02-15 04:46:27 | | 12 | Music | 2006-02-15 04:46:27 | +-------------+-------------+---------------------+ 8 rows in set (0.12 sec) |
You can also absolutely negate the BETWEEN
operator using the NOT
keyword, which in essence, has an opposite effect:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> SELECT * -> FROM category -> WHERE category_id NOT BETWEEN 5 AND 12; +-------------+-----------+---------------------+ | category_id | name | last_update | +-------------+-----------+---------------------+ | 1 | Action | 2006-02-15 04:46:27 | | 2 | Animation | 2006-02-15 04:46:27 | | 3 | Children | 2006-02-15 04:46:27 | | 4 | Classics | 2006-02-15 04:46:27 | | 13 | New | 2006-02-15 04:46:27 | | 14 | Sci-Fi | 2006-02-15 04:46:27 | | 15 | Sports | 2006-02-15 04:46:27 | | 16 | Travel | 2006-02-15 04:46:27 | +-------------+-----------+---------------------+ 8 rows in set (0.00 sec) |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> DESC payment; +--------------+----------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------+------+-----+-------------------+-----------------------------------------------+ | payment_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | customer_id | smallint(5) unsigned | NO | MUL | NULL | | | staff_id | tinyint(3) unsigned | NO | MUL | NULL | | | rental_id | int(11) | YES | MUL | NULL | | | amount | decimal(5,2) | NO | | NULL | | | payment_date | datetime | NO | | NULL | | | last_update | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +--------------+----------------------+------+-----+-------------------+-----------------------------------------------+ 7 rows in set (0.00 sec) |
Let’s grab any rows with a ‘payment_id’ column value of 10 or less.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> SELECT payment_id, amount -> FROM payment -> WHERE payment_id < = 10; +------------+--------+ | payment_id | amount | +------------+--------+ | 1 | 2.99 | | 2 | 0.99 | | 3 | 5.99 | | 4 | 0.99 | | 5 | 9.99 | | 6 | 4.99 | | 7 | 4.99 | | 8 | 0.99 | | 9 | 3.99 | | 10 | 5.99 | +------------+--------+ 10 rows in set (0.00 sec) |
WHERE
clause predicate and be included in the returned rows.)
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> SELECT payment_id, amount -> FROM payment -> WHERE payment_id < = 10 -> AND -> amount < 3.00; +------------+--------+ | payment_id | amount | +------------+--------+ | 1 | 2.99 | | 2 | 0.99 | | 4 | 0.99 | | 8 | 0.99 | +------------+--------+ 4 rows in set (0.16 sec) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> SELECT * -> FROM category -> WHERE category_id BETWEEN 5 AND 10 -> OR name = 'Travel' -> OR name = 'Action'; +-------------+-------------+---------------------+ | category_id | name | last_update | +-------------+-------------+---------------------+ | 1 | Action | 2006-02-15 04:46:27 | | 5 | Comedy | 2006-02-15 04:46:27 | | 6 | Documentary | 2006-02-15 04:46:27 | | 7 | Drama | 2006-02-15 04:46:27 | | 8 | Family | 2006-02-15 04:46:27 | | 9 | Foreign | 2006-02-15 04:46:27 | | 10 | Games | 2006-02-15 04:46:27 | | 16 | Travel | 2006-02-15 04:46:27 | +-------------+-------------+---------------------+ 8 rows in set (0.00 sec) |
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
:
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.