Recently, I learned through some online reading that the TABLE
statement is supported in MySQL. I was aware that PostgreSQL supported the TABLE
command and used it from time to time. As time went on and I used MySQL more (nothing at all against PostgreSQL) I forgot about the TABLE
statement. In this blog post, I’ll cover some rudimentary uses of the MySQL TABLE
statement for any other developers who may not be aware that it too exists in MySQL…
OS and DB used:
- Linux Mint 20 “Ulyana”
- MySQL 8.0.23
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!
MySQL TABLE Statement: Syntax and basics
The MySQL TABLE
statement returns query results from a table in a similar fashion as if you had executed a SELECT * FROM
type of query.
+----------+------------------+------------+---------------------+
| store_id | manager_staff_id | address_id | last_update |
+----------+------------------+------------+---------------------+
| 1 | 1 | 1 | 2006-02-15 04:57:12 |
| 2 | 2 | 2 | 2006-02-15 04:57:12 |
+----------+------------------+------------+---------------------+
2 rows in set (0.00 sec)
For the above query, the MySQL TABLE
statement equivalent would look like this:
+----------+------------------+------------+---------------------+
| store_id | manager_staff_id | address_id | last_update |
+----------+------------------+------------+---------------------+
| 1 | 1 | 1 | 2006-02-15 04:57:12 |
| 2 | 2 | 2 | 2006-02-15 04:57:12 |
+----------+------------------+------------+---------------------+
2 rows in set (0.00 sec)
In the first set of queries, both statements return the exact same results: all columns and rows from the named table.
Notice the syntax for the MySQL TABLE
statement is simply the TABLE
keyword followed by the table name. There is no FROM
clause.
MySQL TABLE Statement: Specifying certain columns
It is perfectly valid to specify 1 or more columns in the SELECT
clause, to be returned in the query results:
+----------+
| store_id |
+----------+
| 1 |
| 2 |
+----------+
2 rows in set (0.00 sec)
However, the same is not true for the MySQL TABLE
statement as shown in the next example:
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 store' at line 1
Omitting the FROM
clause still results in an error as above:
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 'store' at line 1
MySQL TABLE Statement: Ordering result rows
To impose any determinate sorting on query results, you must use an ORDER BY
clause. In the below query, I order the results by the ‘first_name’ column, in ascending order. A pretty common and standard query:
-> ORDER BY first_name ASC
-> LIMIT 10;
+----------+------------+-------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-------------+---------------------+
| 71 | ADAM | GRANT | 2006-02-15 04:34:33 |
| 132 | ADAM | HOPPER | 2006-02-15 04:34:33 |
| 165 | AL | GARLAND | 2006-02-15 04:34:33 |
| 173 | ALAN | DREYFUSS | 2006-02-15 04:34:33 |
| 125 | ALBERT | NOLTE | 2006-02-15 04:34:33 |
| 146 | ALBERT | JOHANSSON | 2006-02-15 04:34:33 |
| 29 | ALEC | WAYNE | 2006-02-15 04:34:33 |
| 144 | ANGELA | WITHERSPOON | 2006-02-15 04:34:33 |
| 65 | ANGELA | HUDSON | 2006-02-15 04:34:33 |
| 76 | ANGELINA | ASTAIRE | 2006-02-15 04:34:33 |
+----------+------------+-------------+---------------------+
10 rows in set (0.07 sec)
I’ve also constrained – not filtered – the number of rows displayed by using the LIMIT
clause. Mostly for a more manageable output in the post. But, can you execute a similar type of query using the MySQL TABLE
statement?
You can, in fact, use ORDER BY
and LIMIT
clauses in a TABLE
query just the same as you would with SELECT
.
-> ORDER BY first_name ASC
-> LIMIT 10;
+----------+------------+-------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-------------+---------------------+
| 71 | ADAM | GRANT | 2006-02-15 04:34:33 |
| 132 | ADAM | HOPPER | 2006-02-15 04:34:33 |
| 165 | AL | GARLAND | 2006-02-15 04:34:33 |
| 173 | ALAN | DREYFUSS | 2006-02-15 04:34:33 |
| 125 | ALBERT | NOLTE | 2006-02-15 04:34:33 |
| 146 | ALBERT | JOHANSSON | 2006-02-15 04:34:33 |
| 29 | ALEC | WAYNE | 2006-02-15 04:34:33 |
| 144 | ANGELA | WITHERSPOON | 2006-02-15 04:34:33 |
| 65 | ANGELA | HUDSON | 2006-02-15 04:34:33 |
| 76 | ANGELINA | ASTAIRE | 2006-02-15 04:34:33 |
+----------+------------+-------------+---------------------+
10 rows in set (0.00 sec
Extra: The OFFSET
clause is allowed in a TABLE
query as well.
MySQL TABLE Statement: Filtering result rows
Filtering of rows in SQL happens in the WHERE
clause. For example, if you only wanted to see row(s) for ‘actor_id’ 71, place that predicate test in the query’s WHERE
clause:
-> FROM actor
-> WHERE actor_id = 71;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 71 | ADAM | GRANT | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
On the contrary, including the WHERE
clause in a MySQL TABLE
statement query is not allowed:
-> WHERE actor_id = 71;
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 'WHERE actor_id = 71' at line 2
Consider making a small donation on my behalf as I continue sharing useful and valuable content here on my blog.
Thank you.MySQL TABLE Statement: Can you use it on a VIEW?
You can retrieve rows and columns from a VIEW
just like a regular table using the MySQL TABLE
statement as shown below:
Query OK, 0 rows affected (0.70 sec)
+----------+------------------+------------+---------------------+
| store_id | manager_staff_id | address_id | last_update |
+----------+------------------+------------+---------------------+
| 1 | 1 | 1 | 2006-02-15 04:57:12 |
+----------+------------------+------------+---------------------+
1 row in set (0.00 sec)
Visit the official TABLE documentation for more information on this command.
If you are familiar with TABLE
and use it often, let me know in the comments below.
Like what you have read? See anything incorrect? Please comment below and thank you 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.
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, are 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.