Use of OR operator with WHERE

Question

I have studied that DBMS won't evaluate the second condition in a WHERE clause if the first condition is met.

select *

from student

where name = 'ABC' or name ='XYZ';



select *

from student

where name = 'ABC' or 'XYZ';

The first returns all the rows with name 'ABC' and 'XYZ' while second one returns just the rows with name 'ABC'.

Answer 1

SQLite treats Boolean expressions as 0 for False and 1 for True.
Also converts implicitly string values to numbers when necessary in expressions like:

name = 'ABC' or 'XYZ'

in order to evaluate them.
So the above expression is equivalent to:

(name = 'ABC') or 'XYZ'

and so 'XYZ' is implicitly converted to the numeric value of 0 meaning False.
The result of this expression is:

(name = 'ABC') or False

which is equivalent to:

name = 'ABC'

This is why it returns just the rows with name 'ABC'

Answer 2

In second query XYZ is treated as condition(XYZ - false):

select *
from student
where name = 'ABC' or 'XYZ';

<=>

select *
from student
where name = 'ABC' or false;

<=>

select *
from student
where name = 'ABC';

"I have studied that DBMS won't evaluate the second condition in a WHERE clause if the first condition is met."

It depends, short-circuiting is not always the case.

I guess you want:

select *
from student
where name IN('ABC', 'XYZ'); -- basically the same as your first query