Fun MySQL fact of the day: SELECT has LIMITs
We still have a few more Fun MySQL Sorting Facts Of The Day coming up, but for now, we've reached a LIMIT. And, if you'll believe it, it was exactly 2 months ago today that we first (and last!) considered LIMIT, but there's lots more fun than you may think. We'll start very simple today, stretching out the fun before we circle back to finishing up sorts next week.
In MySQL, LIMIT is an optional clause that may be used with SELECT, UPDATE, and DELETE statements to limit the number of results and/or rows affected. If you remember back to February 28, you'll recall that LIMITs aren't always safe to run with DML (Data Manipulation Language) statements, but, thankfully, today we're only going to focus on SELECT ... LIMIT. In particular, we'll look at the LIMIT syntax itself, which, interestingly, is unique to MySQL and complies to absolutely no ANSI-SQL standard. Having said that, MySQL does provide a LIMIT syntax that is used by other popular open-source databases like PostgreSQL, SQLite, H2, and so on.
In the formal syntax definition, MySQL's limit clause is defined as LIMIT {[offset,] row_count | row_count OFFSET offset}, which really means you can use any of the following syntax to fetch the first 100 rows from a query:
LIMIT 100, which is the most common usage wherein theoffsetis optional (default 0),LIMIT 0, 100, which is unique to MySQL orLIMIT 100 OFFSET 0, which is commonly used by other database vendors but still not ANSI-SQL
We'll talk about offsets and OFFSET more later, but for now: if you didn't know you could LIMIT results, now you do. But you just can't transfer that knowledge directly to any other database.