Rob's Blog

One for the geeks I’m afraid.

I’m developing a site with quite a complicated menu system getting details using equally complicated database queries. In various places in the menu, I needed to find out whether to display a particular sub-menu. To do this I had some SQL to check whether any products existed at that particular point.

The SQL to do this took quite a while to execute (in one case over 30 seconds), which is obviously too long to wait to load a page.

I realised that my SQL statements were selected all appropriate products, where as they in fact only had to check whether any existed.

I could have just added a LIMIT to just return the first row, but if my statement had any ordering etc this wouldn’t help. I don’t want the first row (I don’t care about the data), just to know whether there is one. I knew there must be a better way.

I discovered EXISTS, which although isn’t documented very much, seems to do exactly what I want very simply. The solution is to surround my existing SELECT statement as follows:

SELECT 1 FROM DUAL WHERE EXISTS ( [SELECT statement here] )

That’s it. I can plug any SELECT statement I like in, and it tells me (very quickly) whether any results would be returned. A very useful trick I’m sure I’ll use in future.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.