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.
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”.
If you have any comments or suggestions - feel free to email me: firstname.lastname@example.org