Using the OR operator - SQL Server

Question

I'm reading documentation about OR operator, it says:

Most of the better DBMS will not even evaluate the second condition in an OR WHERE clausule if the first condition allready been met. (If the first condition is met, the row would be retrieved regardless the second condition).

SELECT prod_code, prod_price
FROM Articles
WHERE Code = '1' OR Code = '2'

Does this mean by documentation if Code = '1' query will return all values with '1' ignoring second condition WHERE Code = '2'.

I've tested this on SQL Server and it returns all values with 1 and all values with 2, so actually 2 rows.

So I'm bit confused here.

It means that if a row fulfils the condition of Code = 1 then it won't evaluate the clause Code = 2 for that row. That doesn't mean it stops checking the rest of the rows. The RDBMS will still return every row that fulfils either of those requirements. — Thom A
– Thom A ♦, Commented Feb 10, 2019 at 12:10
@Larnu aaa for that row is the key! That part of sentence is missing in the book! :) Thanks mate! So if Code=1 is found RDBMS will go to next row, wont check same row twice! Thanks — rokie12234
– rokie12234, Commented Feb 10, 2019 at 12:12
This probably means conditions on different columns, e.g. colA = 'foo' or colB between 1 and 5 — dnoeth
– dnoeth, Commented Feb 10, 2019 at 13:01

TylerH · Accepted Answer · 2023-04-19 14:10:50Z

4

The statement is simply about optimization, something called "short-circuiting". It is saying that the comparison to 2 is not done when 1 matches the condition. Because it does not matter what the comparison to 2 evaluates to.

You seem to be a bit confused by saying

if Code = '1' query will return all values with '1' ignoring second condition WHERE Code = '2'.

Code is a column in each row, not an overall value that is constant for all rows. (If so, it would be @Code in SQL Server, rather than @Code.)

For your example, though, you should be using IN:

WHERE Code IN ('1', '2')

If Code is numeric, the comparison values should not have single quotes.

edited Apr 19, 2023 at 14:10

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Feb 10, 2019 at 12:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rokie12234 Over a year ago

I started learning mssql today, so I'm bit confused, but now it's clearer, author of book meant on one row, if value of Code in a row is 1, then why the heck do I need to check if value of Code is equal to 2 because it definatelly is not cuz I allready ound value of 1 there, so lets chek another row! RIght ?

Gordon Linoff Over a year ago

@rokie12234 . . . Exactly. This is a standard optimization across all programming languages (at least those that support conditional logic). It is not particular to SQL.

Martin Smith Over a year ago

@rokie12234 - it is not really because it knows that Code is definitely not equal to 2 rather it is because it does not matter what it is

TylerH Over a year ago

"If so, it would be @Code in SQL Server, rather than @Code" Did you mean to use the same code value in both places in this sentence?

Thom A · Accepted Answer · 2019-02-10 12:14:35Z

Ripped from my original comment: It means that if a row fulfils the condition of Code = 1 then it won't evaluate the clause Code = 2 for that row. That doesn't mean it stops checking the rest of the rows. The RDBMS will still return every row that fulfils either of those requirements.

So if we have a table like:

CREATE TABLE YourTable (ID int IDENTITY,
                        Code int,
                        SomeValue varchar(10));
INSERT INTO YourTable (Code,SomeValue)
VALUES (1,'asdkjsa'),
       (1,'asdojsa'),
       (2,'saohdsald'),
       (3,'sdfkjhsadk'),
       (3,'asdkjsagd');

If you were to run the query SELECT * FROM YourTable WHERE Code = 1 OR Code = 2; then for the second 1st 2 rows, the OR Code = 2 wouldn't be evaluated, as Code = 1 has already evalutated true.

However, rather than using WHERE Code = 1 OR Code = 2 you could use the more succinct operator IN: WHERE Code IN (1,2).

forpas · Accepted Answer · 2019-02-10 12:17:30Z

1

Does this mean by documentation if Code = '1' query will return all values with '1' ignoring second condition WHERE Code = '2'.

Every row has 1 value at the column Code.
When the table is scanned, the conditions in the WHERE clause are checked.
If the rdbms you are using performs short circuit evaluation, then in the case of OR:
if the value is say '1' then it does not also compare to '2' since it is not needed and moves on to the next row.
If the value is not '1' then it performs another comparison to '2'.
So to answer your question:
The rows with Code = '2' are not ignored.
It's the condition checking that is ignored when it is not needed.

answered Feb 10, 2019 at 12:17

forpas

165k10 gold badges51 silver badges85 bronze badges

Comments

Martin Smith · Accepted Answer · 2019-02-10 16:43:59Z

Your example search condition is

Code = '1' OR Code = '2'

This is a boolean expression that is itself comprised of two sub boolean expressions combined with OR.

Code = '1'
Code = '2'

In SQL boolean expressions can have three possible truth values true, false, unknown.

When combining expressions with AND or OR truth tables can be used to expand out all the possible results. The truth table for OR is below.

╔═════════╦══════╦═════════╦═════════╗
║         ║ TRUE ║  FALSE  ║ UNKNOWN ║
╠═════════╬══════╬═════════╬═════════╣
║ TRUE    ║ TRUE ║ TRUE    ║ TRUE    ║
║ FALSE   ║ TRUE ║ FALSE   ║ UNKNOWN ║
║ UNKNOWN ║ TRUE ║ UNKNOWN ║ UNKNOWN ║
╚═════════╩══════╩═════════╩═════════╝

From reviewing the table above it can be seen that if you know that one of the sub expressions is true it does not matter what value the other expression has. The value of the overall combined expression will still be true so there's no need to even evaluate it.

SQL Server can do short circuit evaluation but it doesn't guarantee that it will.

Additionally it may come up with an execution plan where the two predicates are not even relevant to evaluate together. Potentially it could come up with a plan for the query in the question that contains an index seek operator with two seeks. One on Code = '1' and one on Code = '2'.

The separate predicate evaluation can now be removed from the execution plan as it is guaranteed that the combined results of these index seeks will match the WHERE clause and so there is no need to do any additional predicate evaluation beyond what would happen as part of these seeks.

Collectives™ on Stack Overflow

Using the OR operator - SQL Server

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related