In MySQL select query, checking for a string or checking for true in a where clause?

Question

Consider the following table:

SELECT id, Bill_Freq, Paid_From, Paid_To, Paid_Dt, rev_code FROM psr_20160708091408;

The requirement is to fetch the row which has rev_code populated with the string **SUM**. I've also noticed that for every row with rev_code populated as **SUM** its Bill_Freq won't be either null or zero.

So I wrote two queries to fetch the row with the lowest id

Query based on string check in where clause:

select
        min(id) as head_id,
        bill_freq,
        Paid_From,
        Paid_To,
        Paid_Dt
from
    `psr_20160708091408` where rev_code = "**SUM**";

Query based on true condition:

select
    min(id) as head_id,
        bill_freq,
        Paid_From,
        Paid_To,
        Paid_Dt
from
    `psr_20160708091408` where bill_freq;

I haven't seen anyone use the second type, would like to know its reliability and circumstance of failure.

Answer 1

If by "second type" you mean a where clause with no explicit condition, then there is a good reason why you do not see it.

The SQL standard -- and most databases -- require explicit conditions in the where. MySQL allows the shorthand that you use but it really means:

where not billing_freq <=> 0

or equivalently:

where billing_freq <> 0 or billing_freq is null

(The <=> is the null-safe comparison operator.

The more important issue with your query is the min(). I presume that you actually want this:

select p.*
from psr_20160708091408 p
where rev_code = '**SUM**'
order by id
limit 1;

Also, you should use single quotes as string delimiters. That is the ANSI standard and there is rarely any reason to use double quotes.