Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at dev.Objective() 2015 (Bloomington, MN) with: Andy Matthews and Tim Cunningham and Matt Gifford and Dave Ferguson and Dan Wilson
Ben Nadel at dev.Objective() 2015 (Bloomington, MN) with: Andy Matthews@commadelimited ) , Tim Cunningham@TimCunningham71 ) , Matt Gifford@coldfumonkeh ) , Dave Ferguson@dfgrumpy ) , and Dan Wilson@DanWilson )

Using ORDER BY And LIMIT Clauses In A UNION SQL Statement

By Ben Nadel on
Tags: ColdFusion, SQL

When it comes to SQL (Structured Query Language), I don't use the UNION construct all that often. But, when I do, one of the features that makes it so powerful is the fact that you can apply both an ORDER BY clause and a LIMIT clause to the derived result set of the union (at least in MySQL). Furthermore, the individual SELECT statements can also have their own ORDER BY and LIMIT clauses.

To see this in action, take a look at the following query. It's a nonsense example, I know, but I think it illustrates the point.

  • <!---
  • NOTE: I am using a "UNION ALL" here instead of a "UNION" simply because I know that
  • there will not being row conflicts (based on the pkey logic).
  • --->
  • <cfquery name="friends" datasource="testing">
  • (
  •  
  • SELECT
  • f1.id,
  • f1.name
  • FROM
  • friend f1
  • WHERE
  • f1.id < 4
  •  
  • )
  • UNION ALL
  • (
  •  
  • SELECT
  • f2.id,
  • f2.name
  • FROM
  • friend f2
  • WHERE
  • f2.id >= 4
  • ORDER BY
  • f2.name DESC
  • LIMIT
  • 2
  •  
  • )
  • <!---
  • In this case, the ORDER BY applies to the result set derived from the UNION of
  • both SELECT statements. Notice that it refers to the derived column name.
  • --->
  • ORDER BY
  • name ASC
  •  
  • <!--- In this case, the LIMIT applies to the derived result set. --->
  • LIMIT
  • 10
  • </cfquery>
  •  
  • <!--- Output the query for debugging. --->
  • <cfdump var="#friends#" label="friends" />

I happen to be using "UNION ALL," instead of "UNION" in this case, because I know that there will be no duplicate rows in the result set; the UNION ALL action saves a little bit of processing overhead. But, it wouldn't matter which one I chose for this demo.

That said, you can see that I am using an ORDER BY clause to sort the derived result set and then a LIMIT clause to limit the size of the returned result set. What's more, I am using an ASC sort even though one of the sub-selects is using a DESC sort. The sub-select sort is limited to the scope of that sub-select - the ORDER BY of the UNION will determine the sort of the final result set.

When we run the above code, we get the following CFDump output:


 
 
 

 
 Using ORDER BY and LIMIT in a UNION query in MySQL. 
 
 
 

Definitely a small but very powerful SQL feature!



Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

I would sometimes use this sort of functionality. For instance, I've used it in a sub-query to ensure that I had a default, non-NULL value.

Example: I wrote a query for tracking immunizations received against immunizations scheduled. The query needed to have a set number of rows for the immunization, but you may not always have that many immunizations received. The sub-query would be a SELECT that retrieved that cardinal immunization and performed a UNION with a SELECT that calculated when that cardinal immunization was scheduled. If the child had received the immunization, the sub-query got two results ("received" and "scheduled") and you could LIMIT it to the first result; if the child had not received the immunization, the sub-query only got one result and you would still LIMIT it to that one result.

@Paul,

That's one of the most frustrating scenarios - when a record may have zero-N associations. Zero or 1 is ok, since you can do a LEFT OUTER JOIN and know that, at MOST, you'll get a matching record. But, it gets more complicated when the LEFT OUTER JOIN may return 2 or more records. Then we gotta start jumping through hoops, like you're talking about.

@All,

One thing that I've learned in the last year or two is that UNION, in general, can be not so great for performance. Especially when the UNION includes fields that are Text fields and have to be read off of disk.

At work, we were dealing with some query performance issues and the guys from Percona (MySQL consultants and builder of MySQL tools and databases) were helping us out. They explained that if you had a UNION (and an ORDER BY for that matter) that dealt with Text fields, the database would have to write a temp table to disk while performing the operation because it can't deal with Text in memory. If this is in a high-volume query, this can be a noticeable hit on performance.

As such, we ended up breaking a number of UNIONs apart and doing the zippering together in the code, rather than in the database.

Think about this:

I want to get results from two tables, but want to have a custom order for each of them.

For example

Get all user.name from table User where id > 100 ORDER BY Name

UNION

Get all user.name from table User where user.name like 'Th%' ORDER BY ID