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:

BOOKID	NAME	VALUE
2	title	The Goal
2	weight	2.3
2	price	The Goal

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!