SQL – What's the difference between this two codes?

Question

select sex, id from qualification where cid = ('Male' or 'Female')

vs

select sex, id from qualification where cid = 'Male' or cid = 'Female'

The first code would generate all of the items while the second gives what I want.

In a sense, we can't have or in a brackets for sql?

Answer 1

MySQL normally cast a value to another type for our convenience. One example of it would be casting a string into number.

MySQL can cast a string into a number by relying on the leading number of the said string. For example:

select cast('test' as signed); //returns 0
select cast('1test' as signed); //returns 1

This has been discussed before on another QA as Gelu pointed out.

On another note, true represented as 1 and false represented as 0 in bit.

---

Now let's examine your query:

select sex, id from qualification where cid = ('Male' or 'Female')

Lets resolve bit-by-bit on the where clause.

cid = ('Male' or 'Female')

The or operator accepts two boolean operand. On your case that would be 'Male' and 'Female'. Based on how MySQL handles casting, both 'Male' and 'Female' would return as 0 given they have no leading number at all.

cid = (0 or 0)

An or with both operand valued as false will result false. After resolving the ('Male' or 'Female') the where clause will be:

cid = 0

With the assumption that cid column contains string without any leading number at all, we can assert that all cid casted as 0. It will result your query to be rewritten as:

select sex, id from qualification where 0 = 0

Which also meant:

select sex, id from qualification where true

The query above will return all rows.

Edit: Major restructuring on the answer. I feel the previous answer might be a bit confusing.