I apologize ahead of time if this is blatantly obvious to others, but I found it interesting and decided to share…
I’ve been doing database work for more than 20 yeas now. I have worked in worked in hierarchical and relational database. I have worked in most major DBMS systems. I had never tricked upon this which in retrospect seems so obvious but I’ve never seen it used before.
I had a query which did something like this (RL is three separate databases, but this is simplified for effect)
SELECT t1.fieldName1
, t1.fieldName2
, t2.fieldName1
, t2.fieldName2
FROM table1 t1
INNER JOIN table2 t2 on t2.table1_id = t1.id
INNER JOIN table3 t3 on t3.table2_id = t2.id
WHERE t1.fieldName5 = 'ABC'
AND (t1.fieldid1 = 42 OR t2.fieldid10 = 42 OR t3.fieldID = 42)
What I found was when I needed to change the OR values, and I was in a hurry, I’d end up changing one or two but would miss one and the resulting data would be odd until I realized my mistake.
I noticed that this OR section was reminiscent of a LIKE pattern, but where a LIKE as I’ve typically always used it was this pattern
WHERE field1 IN (value1, value2, value3)
-- INSTEAD OF
WHERE field1 = value1
OR field1 = value2
OR field1 = value3
I wanted to reverse it and use the VALUE in the field place and the FIELD where the value list typically went. So I changed my query to this.
SELECT t1.fieldName1
, t1.fieldName2
, t2.fieldName1
, t2.fieldName2
FROM table1 t1
INNER JOIN table2 t2 on t2.table1_id = t1.id
INNER JOIN table3 t3 on t3.table2_id = t2.id
WHERE t1.fieldName5 = 'ABC'
AND 42 IN (t1.fieldid1, t2.fieldid10, t3.fieldID)
And the query worked! For me, the query is cleaner and easier to read, and more importantly, less likely for me to screw up if I need to change it.
Like I’ve said, I’ve never seen this pattern used before and wanted to share in case someone finds it useful.
