I have some coworkers that haven’t worked a lot with sql subqueries. Subqueries are a great tool, but can be confusing for folks who have limited sql experience. It’s my opinion that, like any code, good naming, proper formatting and comments can do much to make subqueries less intimidating for those who haven’t worked a lot with them.
So, in no particular order, and without adieu, here are my thoughts on how to construct friendlier queries.
Consistent naming of tables and columns
Using a consistent naming convention for your queries will make them easier to read. I make column names uppercase, and table names and operators lowercase. This, I believe, serves to draw the viewer’s attention to the column names, which is helpful when there are complex joins with many conditions.
Format your sql!
This is a pet peeve for me. I see a lot of embedded sql where the developer made no effort to format the sql. So there might be an inline query in some C# code that looks something like this:
string sql="select * from (select a.NAME,a.VALUE from mytable as a) as SUBQUERY"
This is an simple example. I’ve seen giant queries involving a dozen tables embedded into C# applications as three lines of code. Oh, the horror!
With a little more effort, the developer can make that in-line sql a lot easier to understand and maintain.
StringBuilder sql=new StringBuilder(); sql.AppendLine("select") .AppendLine(" *") .AppendLine(" from") .AppendLine(" (") .AppendLine(" select") .AppendLine(" a.NAME,") .AppendLine(" a.VALUE") .AppendLine(" from") .AppendLine(" mytable as a") .AppendLine(" ) as SUBQUERY");
Use subqueries for EAV tables
The codebase that I support in my day job has a lot of EAV tables. Subqueries are a more concise way to support retrieving multiple values from EAV tables. If you choose to do this, depending on how many values you’ll be retrieving, you might have a very long query. In long queries, good naming can really make the query easier to understand.
Let’s say that we have an EAV table that looks like this:
create table dbo.mybooks ( BOOKID int, NAME varchar(25), VALUE varchar(25) )
Let’s say that the table has these values:
So let’s say we want a list of books, with the price and weight of each book. There’s two ways to do this, and one involves subqueries. The long way to write the query would involve lots of joins.
select distinct book.ID, booktitle.VALUE as TITLE, bookweight.VALUE as WEIGHT, bookprice.VALUE as PRICE from dbo.mybooks as book left outer join dbo.mybooks as booktitle on bookweight.BOOKID=book.BOOKID and bookweight.NAME='title' left outer join dbo.mybooks as bookweight on bookweight.BOOKID=book.BOOKID and bookweight.NAME='weight' left outer join dbo.mybooks as bookprice on bookprice.BOOKID=book.BOOKID and bookprice.NAME='price'
There are some problems with this query that are endemic to EAV tables and I’m not going to go into them now. But I would like to demostrate how converting the joins to subqueries might make the above query a little easier to read.
select book.NAME, ( select top 1 n.VALUE from dbo.mybooks n where n.BOOKID=book.BOOKID and n.NAME='title' ) as TITLE, ( select top 1 n.VALUE from dbo.mybooks n where n.BOOKID=book.BOOKID and n.NAME='weight' ) as WEIGHT, ( select top 1 n.VALUE from dbo.mybooks n where n.BOOKID=book.BOOKID and n.NAME='price' ) as PRICE from ( select distinct n.BOOKID from dbo.mybooks n ) as book
Note that, when using subqueries, the entire query for each EAV value is in the select clause. This especially helps when the complete query includes joins to other tables. EAV joins can really clutter up a query!
Naming for subqueries
Note that the EAV table is aliased to "n" within the subquery. I use single character aliases here as I want to deemphasize the name of the table. Inside the EAV subquery, the name of the table isn’t important.
If the subquery is more complex, or will be referenced in a join, I try to give it a short, descriptive name.
The key with aliases is to try to keep them short. Brevity will make your queries more readable!