CODEWITHSUNDEEP
sqlsql-serversql-server-2008logical-operators
rokie12234
rokie12234

Reputation: 63

Using the OR operator - SQL Server

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.

Upvotes: 3

Views: 984

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

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.

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 453287

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.

Upvotes: 0

forpas
forpas

Reputation: 164099

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.

Upvotes: 1

Thom A
Thom A

Reputation: 95561

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).

Upvotes: 1

Related Questions