Skip-and-Keep Queries

What is Skip-and-Keep

We called Skip-and-Keep (a catchy name!) the process of slicing the result of a query execution: you may Skip first n records and Keep next m records of the whole set. Depending on the database type, Skip may be associated with OFFSET, START AT or SKIP keywords, while Keep points to LIMIT, TOP or FIRST.

The mechanism is different from one database to another, but we tried to provide a transparent approach. Whenever you enter a number in the Skip toolbar field, the query re-runs, but skip that number of records. Whenever a Keep is available in the toolbar field, returned number of rows will not be greater than this number.

Skip-and-Keep Demo

Just as an example of how complex and different each generated SQL query could be, let’s see how Skip-and-Keep works on the emp table in Oracle 11g. Oracle’s implementation of slicing is pretty unique, using ROWNUM. We’ll not show the generated SQL here, but try to inspect the generated SQL queries after each of the following steps.

Create a new query based on emp, turn paging off (click on the Page Size button), sort by EMPNO, keep just the fields below and run the query. We get 14 records:

Let’s Keep now only the first 3 records. Enter this value in the Keep field, in the bottom toolbar, and the query re-runs automatically when you quit the field:

Don’t change Keep, but Skip also first 8 records. Enter this value in the Skip field, in the bottom toolbar, and the query re-runs automatically when you quit the field:

Remove now the Keep value, leave the field empty and move to another field:

Paging

The most important and direct application of the Skip-and-Keep is the pagination, or paging. By default, each new query (collecting data from at least one table!) has paging turned on. The Page Size field shows how many records can be brought on screen at once and you may change this value (it will change for ANY other query!). When you press the Page Size label (which is actually a button), you turn paging off. Press again and it gets back on.

Paging is very important for largely populated tables. You do not want ALL data on screen, at once, when you request rows from a table with millions of rows. With paging on, you get first 1000 records (the default), then you navigate to next page, to another page and so on. Or you jump to another page by its number. Or you go directly to the last page in the set.

Paging can be seen as a continuous Skip-and-Keep: Keep always with the Page Size value, and Skip 0 for the first page, Page Size for second, twice Page Size for third and so on. What’s more interesting here, our Skip-and-Keep also works with paging on, you may have both mechanisms combined. On our last query, left with Skip 8, turn paging on and change the Page Size to 5: MILLER will now appear on the second page!

Current Page SQL

If paging is on, we always show the generated SQL query for the first page. It may have some “keep” in it, as the Page Size. If your data result is at a different page and you switch to SQL, you will see the SQL generated for that page! Regenerate it again and it goes back to the first page.

Let’s try this for the emp table in a MySQL database (which implements Skip-and-Keep with very simple LIMIT and OFFSET clauses):

• If you leave Page Size at 5, when you create a new emp-based query, you will see that your query ends in LIMIT 5. This is the “keep” for your first page, the Page Size 5.
• Run the query, then navigate to the next page. Switch to SQL, and your query ends now in LIMIT 5 OFFSET 5. That’s different, it’s for a different page.
• Run it again and go to the last page. Switch to SQL and your query ends in LIMIT 5 OFFSET 10 this time.
• Click SQL again to shows the query for the initial page.
• Turn paging off and you see no LIMIT or OFFSET ending the generated SQL.