Optimization of SQL

I have a query regarding a sql query where i have 3 conditions on same column

AND TRP.X_ID <> '0' 

AND TRP.X_ID <&开发者_JS百科gt; ' ' 

AND TRP.X_ID  IS not NULL;

can this be handled with 1 or 2 conditions within where clause.

---

Well, if a column is currently NULL, it is never equal, not not equal, to any specific value, so you can skip the 3rd test immediately. As for the other two, you can condense it to:

TRP.X_ID not in ('0',' ')

which is shorter to read, but unlikely to change how the code performs - in e.g. SQL Server, it's internally re-written back into individual comparisons.

---

and coalesce(trp.x_id, '') not in ('', '0')

EDIT

or (mysql version)

length(case trp.x_id when '0' then '' else trp.x_id end) > 0

---

You can also do this... this is really just a syntactical optimization... sql optimizer will interpret these equally (as far as performance).

AND NULLIF(NULLIF(TRP.X_ID,'0'),' ') IS NOT NULL