What is this WHERE clause

Question

What is this WHERE clause

oradbguru

Hall of Fame

Points: 3126
More actions
June 29, 2016 at 1:20 pm

#311452

Hello, I was debugging some legacy code and came across an insert statement with only this in the where clause.
WHERE ' ' = ' '
Any ideas what this would do?
It looks like it reduces a result set from around 120m row to around 700.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

oradbguru (6/29/2016)
Hello, I was debugging some legacy code and came across an insert statement with only this in the where clause.
WHERE ' ' = ' '
Any ideas what this would do?
It looks like it reduces a result set from around 120m row to around 700.

That's not possible. Constants' comparisons will return either all rows or none.

Something else might be limiting the result set, unless you're not sharing the exact clause.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

It's the same as WHERE 1=1, a logical condition that can never be false, will never exclude any rows and is ignored by the Query Optimiser

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Luis Cazares (6/29/2016)
oradbguru (6/29/2016)
Hello, I was debugging some legacy code and came across an insert statement with only this in the where clause.
WHERE ' ' = ' '
Any ideas what this would do?
It looks like it reduces a result set from around 120m row to around 700.
That's not possible. Constants' comparisons will return either all rows or none.
Something else might be limiting the result set, unless you're not sharing the exact clause.

.

I think that it's this:

SELECT *

FROM sys.all_columns

WHERE ' ' = ' '

E.g. The same as

WHERE 1 = 1

I have never understood why people do this.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Alan.B (6/29/2016)
Luis Cazares (6/29/2016)
oradbguru (6/29/2016)
Hello, I was debugging some legacy code and came across an insert statement with only this in the where clause.
WHERE ' ' = ' '
Any ideas what this would do?
It looks like it reduces a result set from around 120m row to around 700.
That's not possible. Constants' comparisons will return either all rows or none.
Something else might be limiting the result set, unless you're not sharing the exact clause.
.
I think that it's this:
SELECT *
FROM sys.all_columns
WHERE ' ' = ' '
E.g. The same as
WHERE 1 = 1
I have never understood why people do this.

I've seen people using it to test, that way they can comment conditions easier. It shouldn't be part of the final code IMO.

It's also used when creating dynamic queries, so they won't have to validate the first condition. There are other ways to deal with this, but I don't see it as an important problem.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

As a side note, in Oracle a NULL and an empty string '' are equal, which means that:

'' IS NULL, the expression '' = '' would always evaluate False, and the expression 'A' > '' would be False.

Be mindful of that if you have something like an SSIS package that is sourcing data from Oracle into SQL Server.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho