CTE’s in MySQL – First steps…

Having recently upgraded my learning/development environment to MySQL 8 from version 5.7, I thought to start blogging on a couple of the (many) new fantastic features included in this latest version. With this blog post, I will begin what I plan to be a multi-part series on Common Table Expressions as I learn, explore, and use them…

Photo by chuttersnap on Unsplash

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:


  • Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
  • MySQL 8.0.13

I am using both the customer and payment tables from the Sakila open-source practice database, that simulates a mock DVD rental system, for this post:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> DESC customer;
+-------------+----------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                                         |
+-------------+----------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment                                |
| store_id    | tinyint(3) unsigned  | NO   | MUL | NULL              |                                               |
| first_name  | varchar(45)          | NO   |     | NULL              |                                               |
| last_name   | varchar(45)          | NO   | MUL | NULL              |                                               |
| email       | varchar(50)          | YES  |     | NULL              |                                               |
| address_id  | smallint(5) unsigned | NO   | MUL | NULL              |                                               |
| active      | tinyint(1)           | NO   |     | 1                 |                                               |
| create_date | datetime             | NO   |     | NULL              |                                               |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------------------------+
9 rows in set (0.00 sec)

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            | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+----------------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.00 sec)

Let’s recognize all customers who spent more than $10.00 in rentals for the first 2 weeks of July with some kind of ‘reward’. Of course, we need their names and emails.

That information is easily found out via one of several different queries. For instance, I use an INNER JOIN to a derived table name ‘t’ (based on the needed results from the payment table) and get this results set back:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> SELECT first_name, last_name, email
    -> FROM customer AS c
    -> INNER JOIN (SELECT customer_id, SUM(amount), payment_date
    -> FROM payment
    -> WHERE payment_date BETWEEN '2005-07-01' AND '2005-07-14'
    -> GROUP BY customer_id, payment_date
    -> HAVING SUM(amount) > 10.00
    -> ORDER BY SUM(amount) DESC) AS t
    -> ON c.customer_id = t.customer_id;
+------------+--------------+------------------------------------------+
| first_name | last_name    | email                                    |
+------------+--------------+------------------------------------------+
| TERRANCE   | ROUSH        | TERRANCE.ROUSH@sakilacustomer.org        |
| KENT       | ARSENAULT    | KENT.ARSENAULT@sakilacustomer.org        |
| BENJAMIN   | VARNEY       | BENJAMIN.VARNEY@sakilacustomer.org       |
| ELEANOR    | HUNT         | ELEANOR.HUNT@sakilacustomer.org          |
| MITCHELL   | WESTMORELAND | MITCHELL.WESTMORELAND@sakilacustomer.org |
| HERMAN     | DEVORE       | HERMAN.DEVORE@sakilacustomer.org         |
| VALERIE    | BLACK        | VALERIE.BLACK@sakilacustomer.org         |
| BRITTANY   | RILEY        | BRITTANY.RILEY@sakilacustomer.org        |
| VINCENT    | RALSTON      | VINCENT.RALSTON@sakilacustomer.org       |
| DANIEL     | CABRAL       | DANIEL.CABRAL@sakilacustomer.org         |
| ERIC       | ROBERT       | ERIC.ROBERT@sakilacustomer.org           |
| EDDIE      | TOMLIN       | EDDIE.TOMLIN@sakilacustomer.org          |
| JOHN       | FARNSWORTH   | JOHN.FARNSWORTH@sakilacustomer.org       |
| WESLEY     | BULL         | WESLEY.BULL@sakilacustomer.org           |
| CATHY      | SPENCER      | CATHY.SPENCER@sakilacustomer.org         |
| MARION     | SNYDER       | MARION.SNYDER@sakilacustomer.org         |
| WILMA      | RICHARDS     | WILMA.RICHARDS@sakilacustomer.org        |
| ANGELA     | HERNANDEZ    | ANGELA.HERNANDEZ@sakilacustomer.org      |
+------------+--------------+------------------------------------------+
18 rows in set (0.02 sec)

This query works just fine, yet let’s try an alternate route, so to speak.

As I mentioned in the opener, MySQL version 8 introduced many new, powerful features. As a matter of fact, have a look at, MySQL in 2018: What’s in 8.0 and Other Observations, I wrote, as a guest writer, for the Severalnines Database Blog, providing a high-level overview of those features that caught my attention.

One of the more sought-after inclusions in this release is SQL standard Common Table Expressions (or CTE’s), which are constructed via the WITH clause.

CTE’s are extremely powerful and provide alternative means for working with query results you need or want to access. I will use a CTE and replace the derived table in our example query to showcase just one of their ‘limitless’ uses:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
WITH payment_data AS (
    SELECT customer_id, SUM(amount), payment_date
    FROM payment
    WHERE payment_date BETWEEN '2005-07-01' AND '2005-07-14'
    GROUP BY customer_id, payment_date
    HAVING SUM(amount) > 10.00
    ORDER BY SUM(amount) DESC
)

SELECT first_name, last_name, email
FROM customer AS c
INNER JOIN payment_data AS pt
ON c.customer_id = pt.customer_id;
+------------+--------------+------------------------------------------+
| first_name | last_name    | email                                    |
+------------+--------------+------------------------------------------+
| TERRANCE   | ROUSH        | TERRANCE.ROUSH@sakilacustomer.org        |
| KENT       | ARSENAULT    | KENT.ARSENAULT@sakilacustomer.org        |
| BENJAMIN   | VARNEY       | BENJAMIN.VARNEY@sakilacustomer.org       |
| ELEANOR    | HUNT         | ELEANOR.HUNT@sakilacustomer.org          |
| MITCHELL   | WESTMORELAND | MITCHELL.WESTMORELAND@sakilacustomer.org |
| HERMAN     | DEVORE       | HERMAN.DEVORE@sakilacustomer.org         |
| VALERIE    | BLACK        | VALERIE.BLACK@sakilacustomer.org         |
| BRITTANY   | RILEY        | BRITTANY.RILEY@sakilacustomer.org        |
| VINCENT    | RALSTON      | VINCENT.RALSTON@sakilacustomer.org       |
| DANIEL     | CABRAL       | DANIEL.CABRAL@sakilacustomer.org         |
| ERIC       | ROBERT       | ERIC.ROBERT@sakilacustomer.org           |
| EDDIE      | TOMLIN       | EDDIE.TOMLIN@sakilacustomer.org          |
| JOHN       | FARNSWORTH   | JOHN.FARNSWORTH@sakilacustomer.org       |
| CATHY      | SPENCER      | CATHY.SPENCER@sakilacustomer.org         |
| MARION     | SNYDER       | MARION.SNYDER@sakilacustomer.org         |
| WILMA      | RICHARDS     | WILMA.RICHARDS@sakilacustomer.org        |
| WESLEY     | BULL         | WESLEY.BULL@sakilacustomer.org           |
| ANGELA     | HERNANDEZ    | ANGELA.HERNANDEZ@sakilacustomer.org      |
+------------+--------------+------------------------------------------+
18 rows in set (0.04 sec)

How cool is that!!!

Let’s isolate the payment_data CTE’s results set without plugging it into the final query and see the data it returns:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
WITH payment_data AS (
    SELECT customer_id, SUM(amount), payment_date
    FROM payment
    WHERE payment_date BETWEEN '2005-07-01' AND '2005-07-14'
    GROUP BY customer_id, payment_date
    HAVING SUM(amount) > 10.00
    ORDER BY SUM(amount) DESC
)

SELECT * FROM payment_data;
+-------------+-------------+---------------------+
| customer_id | SUM(amount) | payment_date        |
+-------------+-------------+---------------------+
|         592 |       11.99 | 2005-07-06 22:58:31 |
|         591 |       11.99 | 2005-07-07 20:45:51 |
|         364 |       10.99 | 2005-07-10 18:54:05 |
|         148 |       10.99 | 2005-07-12 16:37:55 |
|         520 |       10.99 | 2005-07-10 15:02:17 |
|         481 |       10.99 | 2005-07-08 10:11:45 |
|         149 |       10.99 | 2005-07-09 15:36:17 |
|         187 |       10.99 | 2005-07-06 10:26:56 |
|         414 |       10.99 | 2005-07-06 22:05:47 |
|         310 |       10.99 | 2005-07-06 16:01:16 |
|         331 |       10.99 | 2005-07-07 18:01:22 |
|         434 |       10.99 | 2005-07-08 23:27:16 |
|         300 |       10.99 | 2005-07-10 10:09:17 |
|         163 |       10.99 | 2005-07-10 03:54:38 |
|         178 |       10.99 | 2005-07-09 05:59:47 |
|         212 |       10.99 | 2005-07-08 11:59:19 |
|         469 |       10.99 | 2005-07-06 01:03:29 |
|          29 |       10.99 | 2005-07-09 21:55:19 |
+-------------+-------------+---------------------+
18 rows in set (0.03 sec)

These results are the same that the derived table, ‘t’, provides in the original query joined to the customer table. Yet, in a different way. The results set of a CTE can be filtered even further if so desired:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH payment_data AS (
        SELECT customer_id, SUM(amount), payment_date
        FROM payment
        WHERE payment_date BETWEEN '2005-07-01' AND '2005-07-14'
        GROUP BY customer_id, payment_date
        HAVING SUM(amount) > 10.00
        ORDER BY SUM(amount) DESC
)

SELECT customer_id
FROM payment_data
LIMIT 3;
+-------------+
| customer_id |
+-------------+
|         592 |
|         591 |
|         364 |
+-------------+
3 rows in set (0.01 sec)

There are many more uses for CTE’s than those demonstrated here. Want to know of them or see them in action? Stay tuned here and subscribe for more blog posts on CTE’s coming soon!

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

Explore the official MySQL 8.0 Online Manual for more information.

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.

3 thoughts on “CTE’s in MySQL – First steps…”

    • Hi Vipin. Thanks so much for your compliment and reading. To my knowledge, CTE’s were introduced in version 8.0… I previously was using version 5.7 but upgraded to 8 to explore and learn about many of the features that version 8 implemented.

      Reply

Hey thanks for commenting! Leave a Reply

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