3 SQL Tricks To Use When Query Building

I write hundreds of lines of SQL every day, and that’s just for my day job. This repetition has allowed me to realize some exceptional tricks. Simple SQL tricks which help me write cleaner code, code that is easier to troubleshoot, guarantees more accurate data sets and is easier to manage as the query grows. So, here are three SQL tricks that I consistently use in every query I write:

SQL Tricks and Tips to use in every day query writing.

SQL Tricks WHERE 1 = 1

This is one of my favorite SQL tricks that always causes people confusion when looking at my queries. I’ll reiterate this again later, but this is NOT a production ready trick, this is merely a tool to help make troubleshooting easier.

How does the WHERE 1=1 trick work?


SELECT FOO.USER
FROM DB.FOO
WHERE 1=1
  --AND FOO.TYPE <> 'NINJA'
  AND FOO.AGE > 25
  AND FOO.TYPE = 'NINJA';

SELECT FOO.USER

FROM DB.FOO

WHERE 1=1

--AND FOO.TYPE <> 'NINJA'

AND FOO.AGE > 25

AND FOO.TYPE = 'NINJA';

Hopefully, you’ll notice right away how troubleshooting a problem or trying to find particular data can be greatly assisted with this trick. Simply by adding or removing a commented AND statement you can quickly swap out filters without having to constantly re-write the ANDs.

Note: It is important to note, however, that this trick works best when using ANDs, this can introduce complications when using ORs.

Count(1) to validate SQL joins

All too often when adding joins to a query you’ll end up introducing a cartesian, or botching a join, a trick I use to solve this dilemma is to validate counts at every stage of a join. Take this query as an example:


SELECT A.*, B.*, C.*
FROM DB.A
  INNER JOIN DB.B
    ON A.FOO = B.FOO
  LEFT JOIN DB.C
    ON B.BAR = C.BAR;

SELECT A.*, B.*, C.*

FROM DB.A

INNER JOIN DB.B

ON A.FOO = B.FOO

LEFT JOIN DB.C

ON B.BAR = C.BAR;

Now, let’s introduce this query validation trick to the above query:


SELECT A.*, B.*, C.*
--SELECT COUNT(1)
FROM DB.A --1,356,489
  INNER JOIN DB.B --1,356,489
    ON A.FOO = B.FOO
  LEFT JOIN DB.C --4,954,356,489  <----Red Flag!
    ON B.BAR = C.BAR;

SELECT A.*, B.*, C.*

--SELECT COUNT(1)

FROM DB.A --1,356,489

INNER JOIN DB.B --1,356,489

ON A.FOO = B.FOO

LEFT JOIN DB.C --4,954,356,489 <----Red Flag!

ON B.BAR = C.BAR;

You’ll see that I introduce a SELECT COUNT(1) to my query which allows me to test each join. Notice that the first table returns 1.3 Million records, as does the join to table B, but further notice the cartesian product that occurs when joining to table C. This is an easily caught artifact that remains uncovered by using this trick.

I like to add the counts to each join section by simply placing a comment at the end of the join clause. This makes it easy to add joins later and expose problems, if there even is one, and more importantly when showing your code in a code review, it is clearly identifiable that you’ve validated your counts.

Note: I’ll often keep the SELECT COUNT(1) statement in my query, but commented out.

Always Reference a Table’s Alias

When a query begins it’s always simple. You’re starting with finding the number of ninjas that purchased throwing stars that day. But soon, like an avalanche, you’ve joined dozens of aggregate facts and cross joined a date dimension – the query has blown up! Somewhere in that mix, you forgot to tag one of your fields with an alias and you’re receiving an “Ambiguous name reference” error or something of the like.

Coming up with clean, consistent aliases can save you tons of headaches as you develop your query. Even more importantly, save the next person who has to read your code a lot of time figuring out which table the unaliased field comes from. Here is an example of how to properly use aliases (in my opinion):


SELECT
  DC.FULL_NAME
 ,DD_CUST_DOB.FULL_DATE
 ,DD_CUST_LAST_PRCH.FULL_DATE
FROM EDW.DIM_CUSTOMER AS DC
  LEFT JOIN EDW.DIM_DATE AS DD_CUST_DOB --<------------- Example A
    ON DC.DOB_DATE_KEY = DD_CUST_DOB.DATE_KEY
  LEFT JOIN EDW_DIM_DATE AS DD_CUST_LAST_PRCH --<------- Example B
    ON DC.LAST_PURCHASE_DATE_KEY = DD_CUST_LAST_PRCH.DATE_KEY;

SELECT

DC.FULL_NAME

,DD_CUST_DOB.FULL_DATE

,DD_CUST_LAST_PRCH.FULL_DATE

FROM EDW.DIM_CUSTOMER AS DC

LEFT JOIN EDW.DIM_DATE AS DD_CUST_DOB --<------------- Example A

ON DC.DOB_DATE_KEY = DD_CUST_DOB.DATE_KEY

LEFT JOIN EDW_DIM_DATE AS DD_CUST_LAST_PRCH --<------- Example B

ON DC.LAST_PURCHASE_DATE_KEY = DD_CUST_LAST_PRCH.DATE_KEY;

In this query, we’re retrieving the customer’s name, date of birth, and the last date they purchased something. But, in order to do that, we have to join to the date dimension twice. Notice how I alias dim_customer as “DC” but instead how I alias the date dimensions quite differently.

This isn’t the only example, but it is probably the most obtrusive. Still, another great example is when you have two tables that both contain the same field. Let’s take a look at that query:


SELECT
  DC.NAME
 ,TRANSACTION_DATE_KEY --<-----Example A
 ,TRANSACTION_AMOUNT --<-------Example B
FROM EDW.DIM_CUSTOMER AS DC
  INNER JOIN EDW.FACT_TRANSACTION AS FT
    ON DC.CUSTOMER_ID = FT.CUSTOMER_ID
  INNER JOIN EDW.DIM_TRANSACTION AS DT
    ON FT.TRANSACTION_ID = DT.TRANSACTION_ID;

SELECT

DC.NAME

,TRANSACTION_DATE_KEY --<-----Example A

,TRANSACTION_AMOUNT --<-------Example B

FROM EDW.DIM_CUSTOMER AS DC

INNER JOIN EDW.FACT_TRANSACTION AS FT

ON DC.CUSTOMER_ID = FT.CUSTOMER_ID

INNER JOIN EDW.DIM_TRANSACTION AS DT

ON FT.TRANSACTION_ID = DT.TRANSACTION_ID;

You’ll notice that the two examples above, A & B don’t explicitly reference an alias to a table. You have absolutely no way of knowing which table, the dimension or the fact, the two fields come from. This query may well work, but it isn’t helpful once you start compounding upon it.

Do you use any of these SQL tricks? Are there others you routinely use? Drop a comment below, I’d love to hear about it!