I was today years old when I learned

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.

3 Likes

Very nice!

Is there a difference performance wise or is the planner wise enough to figure it out?

1 Like

From what I can tell looking at the execution plans, performance is EXACTLY the same, so the planner must translate it to the same exact operations.

1 Like

sweet

i’ve used this on many occasions

2 Likes

Figured that. Can’t sneak anything past you :lol:

And you’ve been doing DB work at least as long as I have, so :tongue:

2 Likes

I never used it and never seen it before. I don’t think that I would have ever figured it out by myself.

database since 1978 (CODASYL IDMS)

SQL since 1987 (DB2 with QMF)

1 Like

Like I said, longer than me. I started working with DBs and SQL in the early 90s. First “professional” job used Datacom DB in 1993/94