Value before variable

Question

I'm looking at some SQL code which has a WHERE clause like this:

WHERE 'USD' = CCY

I asked the writer why he's putting the value on the left hand side, and he said it's best practice to do so, stemming from C++ where people could mistakenly assign the value instead of comparing equality by forgetting the second equals sign.

I've never seen this before.

What are your thoughts?

Answer 1

Never seen it in SQL, where of course the C++ reasoning does not apply, as '=' is not an assignment operator in this context. also, a lot of C++ programmers (including me) don't like this style.

Answer 2

Best practise in c++ does not make it best practise in SQL. The query optimizer will not care, so it is just a matter of preference, but I have to say it would not be my preference or how I would naturally write it.

Answer 3

If you look at it:

'USD' = CCY

is essentially the same:

 CCY = 'USD'

As for:

it's best practice to do so, stemming from C++ where people could mistakenly assign the value instead of comparing equality by forgetting the second equals sign.

Well, i have never seen this happen, and if this was that important, we would definitely have seen this somewhere and this would have been practiced by most if not by all.

Answer 4

Er, C++ is not SQL. There's no == in SQL and no assignments in a WHERE clause.

I'm not sure it qualifies as "best practice" but there is a convention which places the known value on the right-hand side. So, with a literal as in your example that would be

WHERE CCY = 'USD' 

Answer 5

I personally would not do it that way, but put the column name on the left hand side as this to me is more readable / easier to follow within an SQL query.

I've very rarely seen it done the opposite way, and don't think the reason given is really applicable to SQL (as has been pointed out, it's "=" in SQL, not "==")

Answer 6

If he says it's a best practice, I'd ask him to prove that with SQL not C++ sources. Since 99.9% of the SQL code I've ever read (including our code, other organization's code, Microsoft help files, SQL Blogs, etc) does the opposite of what your dev does, I'd say that violating the normal expectation of the developer who will maintain the code is a bad idea. In SQL we expect to see the form

WHERE CCY = 'USD'  

not

WHERE  'USD' = CCY  

Therefore the professsional would also write code in that manner to ensure it is clear to the maintainer.