DBA tips & tricks: SQL’s version of JS’s callback hell. Substituting OR for IN.

A quick round-up of my favorite tips & tricks for database administration! If there is a difference in method /query structure between T-SQL, Postgres or MySQL I will make sure to point it out. The assumption is that the version of SQL is current to the date of this posting.

What is OR good for? Absolutely nothing!

By those who have not been blessed by Edwin Starr’s classic “War”, video at the bottom of this page. It has nothing to do with SQL, just a banger.

Anyways, the OR operator. It’s good stuff. Using it in a WHERE clause though for more than like 4 clauses is like, so hard to follow. Kinda like, how I am using like, “like” in these sentences.

For example, let's say we want to know in the data where the Food is equal to “Apple”, “Orange”, “Bread”, “Alt Milk”, “Potatoe”, or “Cake”. You could do this:

SELECT * FROM Meals WHERE Food = 'APPLE' OR Food = 'ORANGE' OR Food = 'BREAD' OR Food = 'ALT MILK' OR Food = 'POTATOE' OR Food = 'CAKE'

..or, or, or, or, or. Honestly, this shouldn’t drive me as bananas as it does but does it ever!

What else can we do?

“IN” has entered the chat

True story: I had a SQL mentor who was taught SQL while in the USSR. He told me that computers were hard to come by so everything was written by hand and then someone else would enter in your code for you and let you know 1 or 0 (pass or fail). That is brutal, however, it did teach you every trick in the book to build short fast SQL. My old habit of overusing “OR” caused this old Russian man to calmly with no deminer tell me if I use us OR ever again behind a WHERE clause he would end me 🙃. I am still around and my obsession with OR is gone! But how do we use IN to replace OR?

SELECT * FROM Meals WHERE Food IN ('APPLE', 'ORANGE', 'BREAD', 'ALT MILK', 'POTATOE', 'CAKE');

We simply put an IN before we declare all of the values we want to check against! Simple and much more readable. Less likely a British spy posing as a teacher in your USSR university to type in the wrong command!

More readable and quicker to write. What else would you want?

One of my favourite websites for anything SQL is learnsql . They have a cookbook section that has easy-to-follow instructions on how to get the results you’re looking for in an aesthetically pleasing way (very on-trend).