SQL tips 'n tricks
By Leonid Toshchev, March 16, 2015, 7:42 a.m.

Using SQL every day for many years, I have found for myself a few useful tricks. Maybe it cannot be called full-fledged tricks, but rather approaches to solving certain simple tasks that save us time and effort.

I. Using of cross-reference tables.

Often we have many to many relations in our database model. So, we have 2 tables with entities (e.g., clients and achievements) and a junction table that contains common fields from them.

Using relational databases makes strong influence on our way of thinking. Most of the time we focused at the entities. Example, our task is to see the trend in the number of earning achievements among users. It is the simple query with grouping (clients have unique emails):

SELECT c.email, count(a.id)
FROM clients c
JOIN achievements_clients ac
  ON c.client_id = ac.client_id
JOIN achievements a
  ON a.id = ac.achievement_id
GROUP BY c.email

Three tables, two joins. But what is most interesting, we don't really need user's emails. client_id from achievements_clients table is enough to distinguish individual clients.

The key idea is to consider relations as an entity itself. Next query gives us the same information as previous, but it's shorter and don't require knowing of JOINs.

SELECT client_id, count(achievement_id)
FROM  achievements_clients
GROUP BY client_id

Sad but I still occasionally added redundant tables into my queries. Try to remember about Occam's razor. Write plain and descriptive queries.

II. Boolean logic instead of CASE WHEN clause.

Here, here and here I have written about the CASE WHEN clause and its application in real life. We could use other functions with the same task or we could use boolean operators.

Programmers use this approach for decades. It's simple to use and often outperforms ternary operators and control flow operators (recently this tendency come to naught).

Let's look at an example. We want to select order's positions. If the position's status equals to 0 we want the price lower than $300. If position's stats is equals to 1 take positions with price lower than $400. With CASE WHEN clause, we will use this query:

SELECT *
FROM positions
WHERE price <
  CASE status_id
    WHEN 0 THEN 300
    WHEN 1 THEN 400
  END

If we want to use boolean logic instead we should follow next rules:

1) conditions for price and status_id combined by AND operator;

2) major condition blocks combined by OR operator.

In this way query will be look like this:

SELECT *
FROM positions
WHERE
  status_id = 0 AND price < 300 OR
  status_id = 1 AND price < 400

The advantage of boolean logic to a CASE WHEN clause is query execution speed. In case of boolean logic RDBMS could easily manage the query execution plan and indexes.

Of course, this trick work in both directions. If you have a lot of different conditions for price and haven't any time limits. Than for the sake of readability you can convert complex condition with OR and AND to the clear CASE version :)

III. Using of primary key.

Almost always there is a surrogate primary key with autoincrement in the table. We will try to find a couple of additional applications for it, except for use as a reference to a particular row.

1. Pagination.

If you are a happy user of Oracle DB or MSSQL, to probably use rownum and top() for pagination. In MySQL pagination making by LIMIT, which is too slow at big tables and not so convenient as rownum.

One of the optimization that you could make is to use the primary key in the table without “holes” (when you never delete rows from the table).

SELECT *
FROM positions
WHERE id BETWEEN 1 AND 10

This query pulls first 10 rows. Shifting borders of the range you can get second, third and other pages.

2. Get every N-th row.

For this task you need to get only that rows where a reminder of dividing id by N equals to 0.

SELECT *
FROM positions
WHERE id % 2 = 0

By the way, this query also convenient for getting only the half of the table without calculation table length. Second half you can get by comparing reminder with 1.

Of course using rownum and top() allow you to use this method also at the tables with “holes”.

Achievement unlocked
A lot of words about why we are so awesome. Or something else. Dont know. Really.