In my honest opinion, MS Access is one garbage of a database. More likely, database is too strong of a word. While I won’t be using Access in this article, there is a story there (but I’m not here to have an Access bash party). Continue reading for an example of how you may use a correlated subquery and simulate the LEAD() Window Function.
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.
When working with modern SQL databases which support Window Functions, one tends to get spoiled on their convenience. I honestly never have a ‘roll your own’ mindset for Window Functions with the databases I typically work with.
I mean don’t all databases support them (Window Functions)?
Aren’t we (living) in the modern SQL era?
Most SQL databases support Window Functions
Well, real databases do support Window Functions. But seeing as how MS Access isn’t a real database, there are no Window Functions available in that environment.
So yes, you have to hack your own.
This is just what I had to do recently when working with MS Access. But like I previously wrote, I won’t be using MS Access in this article.
Instead, I’ll be using MySQL and pretending that MySQL has no Window Function support (which of course MySQL v8+ does!).
For the example query data, I’ll be using a table with some of my favorite (past and present) hiking/walking shoes:
Suppose I need to inspect the value of a row further in the result set, from the current row. In a more modern SQL setting, I could leverage the LEAD() window function.
As you likely have surmised, I’ll demonstrate how you can look at further rows if you are not so lucky as to have staple Window Functions at your disposal.
Instead, we can use a correlated subquery.
If I want to inspect a following rows’ ‘brand_name‘ column value, based on the ‘shoe_id‘ ordering, using a correlated subquery to generate that particular column value, could resemble the following query:
SELECT sw.brand_name,
(SELECT sw2.brand_name
FROM shoes_worn AS sw2
WHERE sw2.shoe_id > sw.shoe_id
ORDER BY sw2.shoe_id
LIMIT 1) AS lead_simulated
FROM shoes_worn AS sw
order by sw.shoe_id ASC;
Multiple rows returned in the SELECT list correlated subquery
Notice the column alias ‘lead_simulated‘ is in fact derived (termed loosely here) from a SELECT query in place of what would typically be a column name or expression. This is just one example and use of a correlated subquery.
This works because a SELECT query that generates a SELECT list column or expression value cannot return more than 1 column and value per row.
In this example, were I to remove the LIMIT 1 clause from the correlated subquery, we get an error that tells us just that (too many rows so to speak):
SELECT sw.brand_name,
(SELECT sw2.brand_name
FROM shoes_worn AS sw2
WHERE sw2.shoe_id > sw.shoe_id
ORDER BY sw2.shoe_id
) AS lead_simulated
FROM shoes_worn AS sw
order by sw.shoe_id ASC;
Multiple columns in the SELECT list correlated subquery
On the other hand, if I include 2 columns in the correlated subquery – regardless of the presence of the LIMIT 1 clause – we get an error for that as well:
SELECT sw.brand_name,
(SELECT sw2.brand_name, sw2.shoe_id
FROM shoes_worn AS sw2
WHERE sw2.shoe_id > sw.shoe_id
ORDER BY sw2.shoe_id
LIMIT 1) AS lead_simulated
FROM shoes_worn AS sw
order by sw.shoe_id ASC;
Based on my experience and understanding, some things to be mindful of when using a correlated subquery like this (or for the LEAD() window function in general):
- Be mindful of duplicate values in the ‘lead-like’ column
- You need a meaningful sorting order for the ‘lead-like’ column
- When there are no more rows remaining in the result set, NULL will (typically) be returned for that row’s lead value.
Like always, I enjoy sharing what I learn as I face new challenges when coding. If you see anything wrong in the code or know of a better solution, please share it freely in the comments section so that we all can benefit from this knowledge.
Thank you for reading this post. Please share it with someone else who would enjoy it as well.
Josh Otwell has a passion to grow as a PHP Developer, SQL expert, and technical blogger/writer.
Disclaimer: The majority of examples in this post, are performed in a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Like always, just because you can do something doesn’t mean you should. My opinions are my own.
More ways I can help
- Need hosting for your next web application or WordPress site? I highly recommend Hostinger and use them to host my niche bass fishing site. The service is second to none.
- 🔒5 Truths I’ve Come To Realize As a Self-taught Developer
- Desktop and mobile wallpapers, digital downloads, photography services, Shopify and WooCommerce customizations, and content writing – all in one E-commerce Shop. Find your next digital purchase today!
- Take your Laravel applications next level with Battle Ready Laravel by Ash Allen. Learn how to improve the performance, maintainability, and security of your Laravel projects in this e-book.
Disclosure: Some of this blog post’s services and product links are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.
📰 Get your brand, product, or service the attention it deserves with affordable classified ad placement in the OpenLampTech newsletter. I appreciate your support!