MySQL TABLE statement – Example queries

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…

table with chairs in a coffee shop by a brick wall
Image by Pexels from Pixabay

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.

mysql> SELECT * FROM store;
+----------+------------------+------------+---------------------+
| 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:

mysql> TABLE store;
+----------+------------------+------------+---------------------+
| 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:

mysql> SELECT store_id FROM store;
+----------+
| 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:

mysql> TABLE store_id FROM store;
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:

mysql> TABLE store_id store;
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:

mysql> SELECT * FROM actor
    -> 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.

mysql> TABLE actor
    -> 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:

mysql> SELECT *
    -> 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:

mysql> TABLE actor
    -> 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:

mysql> CREATE VIEW store_1 AS SELECT * FROM store WHERE store_id = 1;
Query OK, 0 rows affected (0.70 sec)
mysql> TABLE store_1;
+----------+------------------+------------+---------------------+
| 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.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.