SQL Puzzler
Yesterday I shared this SQL Puzzler in my editorial:
Here is a brain twister for you. What is the purpose of the + in the following SQL?
WHERE ineStatus in ( + ‘P’, ‘O’, ‘D’)
Ed wrote in with his ideas
Thought I’d take a stab at the plus sign—might that be to somehow make an indirect reference? Or concatenate “P” to “O” or to “D”?
This puzzler came from one of our readers. They came across this syntax on a database where they are helping. Neither of us has seen the syntax before nor can find any documentation as to how or why this would be sued, or how it performs.
I haven’t been able to figure out a good google search phrase to locate assistance for what is going on. One thing we noticed is that when you remove the + the result is the same. At that point I became curious if this is performing as some sort of operator. A search for SQL Server + Operator resulted in something rather obvious that this could be performing concatenation as Ed guessed.
SELECT + ‘D’ returns the same result as SELECT ‘D’. That’s my clue that it is simply performing a concatenation, not with other characters in the list, but with something else.
SELECT + 'A', 'A'
Returns A A
What I can’t figure out is if + is acting as a concatenation operator, with what is it concatenating? If you change the query to SELECT NULL + A the result is NULL.
What if the + operator follows the string value such as SELECT 'A' + ? This syntax results in an error.
So, how does this work using the IN Operator for selecting rows. Here is my final test…
DECLARE @T TABLE (MyKey CHAR(1) NOT NULL)
INSERT INTO @T VALUES ('A'), ('B'), ('C')
SELECT MyKey
FROM @T
SELECT MyKey
FROM @T
WHERE MyKey IN ('A', 'B', 'C')
SELECT MyKey
FROM @T
WHERE MyKey IN (+'A', 'B', 'C')
All three select statements return the same dataset of three rows of A, B, and C. The + operator has no impact on the final results. At this point it appears to me that the behavior of concatenation with only one trailing value results in the concatenation of empty string for the un-specified preceding value.
Did you learn anything from this little exercise? Do you have a solution as to why someone would actually put this in code or is it simply a matter of leftover code from something else? Share your thoughts or send other puzzlers to btaylor@sswug.org.
Cheers,
Ben