# where clause

• ### Question

• hi,

I have a table EMPLOYEE  with the fields  employee demographics , contact  and  CURRENT_ACTIVE_FLAG( values will be 1,0 or null ) , EMP_END_DATE (values  will be date or null ) , i am trying to pull the records for employees currently active .

My question is what is the difference between the below  two SQL

SQL 1:

SELECT * FROM WHERE (CURRENT_ACTIVE_FLAG = 1 OR CURRENT_ACTIVE_FLAG IS NULL) AND EMP_END_DATE IS NULL

SQL2

SELECT * FROM WHERE (CURRENT_ACTIVE_FLAG = 1 OR CURRENT_ACTIVE_FLAG IS NULL AND EMP_END_DATE IS NULL )

Friday, December 26, 2014 9:59 PM

• The first query will return a row if EMP_END_DATE is NULL and CURENT_ACTIVE_FLAG is either 1 or NULL. The second will return all rows with CURRENT_ACTIVE_FLAG = 1 or NULL. It will also return rows with CURRENT_ACTIVE_FLAG = 0 if EMP_END_DATE is NULL.

The reason for this is the same why

(2 + 3) * 5 = 25
2 + 3 * 5 = 17

That is, OR is a addition operator and ADD is a multiplication operator that binds harder.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
• Proposed as answer by Wednesday, December 31, 2014 11:33 AM
• Marked as answer by Monday, January 5, 2015 2:31 AM
Friday, December 26, 2014 11:22 PM

### All replies

• The first query will return a row if EMP_END_DATE is NULL and CURENT_ACTIVE_FLAG is either 1 or NULL. The second will return all rows with CURRENT_ACTIVE_FLAG = 1 or NULL. It will also return rows with CURRENT_ACTIVE_FLAG = 0 if EMP_END_DATE is NULL.

The reason for this is the same why

(2 + 3) * 5 = 25
2 + 3 * 5 = 17

That is, OR is a addition operator and ADD is a multiplication operator that binds harder.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
• Proposed as answer by Wednesday, December 31, 2014 11:33 AM
• Marked as answer by Monday, January 5, 2015 2:31 AM
Friday, December 26, 2014 11:22 PM
• SQL 1 will return the rows of data in which EMP_END_DATE is NULL and either CURRENT_ACTIVE_FLAG is 1 or CURRENT_ACTIVE_FLAG is NULL

SQL 2 will return the rows of data in which CURRENT_ACTIVE_FLAG is 1 or  CURRENT_ACTIVE_FLAG is NULL and EMP_END_DATE is NULL

A Fan of SSIS, SSRS and SSAS

Friday, December 26, 2014 11:55 PM
• in the second case since all the three conditions are enclosed within same braces  the OR condition will cause all other conditions to get bypassed if it evaluates to true ie so far as CURRENT_ACTIVE_FLAG IS NULL then it doesn't look for other conditions ie result will contain even records where EMP_END_DATE IS NOT NULL

In the first query since is outside the braces it will always be enforced regardless of the value of CURRENT_ACTIVE_FLAG field

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Saturday, December 27, 2014 6:09 AM
• The where clause in 1st statement, is true, when either one of these 2 conditions

```CURRENT_ACTIVE_FLAG = 1
CURRENT_ACTIVE_FLAG is null, ```

is true, and EMP_END_DATE must be null.

While the where clause in 2nd statement, is true, when either one of these 2 conditions

```CURRENT_ACTIVE_FLAG = 1
CURRENT_ACTIVE_FLAG is null and EMP_END_DATE is null```

is true.

Many Thanks & Best Regards, Hua Min

• Edited by Saturday, December 27, 2014 7:30 AM
Saturday, December 27, 2014 7:29 AM
• Neither SQL work, there is no table name.

-----------------------------------------------

Hope above help !

Doanh

Saturday, December 27, 2014 3:07 PM
• Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. And you probably need to read and download the PDF for:
https://www.simple-talk.com/books/sql-books/119-sql-code-smells/

>> I have a table EMPLOYEE [sic] with the fields [sic] employee demographics, contact and CURRENT_ACTIVE_FLAG (values will be 1,0 or NULL ), EMP_END_DATE (values will be date or NULL ), I am trying to pull the records [sic] for employees currently active. <<

NO! Read any book on data modeling. Do you really have only one employee? No, a table is a set; the name of a set is plural or collective noun. The correct name is Personnel. Fields are not anything like columns; and rows are not records. We do not use assembly language flags in SQL or RDBMS.

From your narrative, it looks like this flag is also redundant. And a flag is never NULL-able. In SQL, we use (start_date. end_date) pairs to mark an interval, using the ISO half-open model. When the interval is still active, we use a NULL in the end_date column (NOT FIELD!).

Since you are too rude to post DDL, how do you think we can help you? Does your boss make you work without DDL?

>> My question is what is the difference between the below two SQL <<

Both of them are crap and you have no table in the FROM clause. We also not use SELECT * in production code.

SELECT <<list of columns >>
FROM Personnel
WHERE employment_end_date IS NULL;

I am sure someone will explain how NULLs work, so you can keep being an awful programmer, but I urge you to have higher goals.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Saturday, December 27, 2014 6:56 PM
• Both of them are crap and you have no table in the FROM clause. We also not use SELECT * in production code.

And you think this is production code? Get real.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Saturday, December 27, 2014 7:28 PM
• <<SQL 1:

SELECT * FROM TableName WHERE (CURRENT_ACTIVE_FLAG = 1 OR CURRENT_ACTIVE_FLAG IS NULL) AND EMP_END_DATE IS NULL>>

SQL 1 will return all the rows which has either CURRENT_ACTIVE_FLAG 1 or NULL and EMP_END_DATE is NULL

<<SQL2

SELECT * FROM TableName  WHERE (CURRENT_ACTIVE_FLAG = 1 OR CURRENT_ACTIVE_FLAG IS NULL AND EMP_END_DATE IS NULL ) >>

which is equivalent to:

SELECT * FROM TableName  WHERE (CURRENT_ACTIVE_FLAG = 1 OR (CURRENT_ACTIVE_FLAG IS NULL AND EMP_END_DATE IS NULL) )

SQL 2 will return all the rows which has either CURRENT_ACTIVE_FLAG 1 or CURRENT_ACTIVE_FLAG is NULL and EMP_END_DATE IS NULL

Just mind the parenthesis highlighted in bold.

Thanks!

Saturday, December 27, 2014 7:29 PM
• Both of them are crap and you have no table in the FROM clause. We also not use SELECT * in production code.

And you think this is production code? Get real.

Are you going to tell me that nobody writes SELECT * in production code queries? That this garbage is so extreme that it is not easy enough to find when you go consulting? Now, you can get real :)

If you want to teach, then tell them when they are screwing up and insist that they follow good coding practices out of habit. Make doing it right a reflex, not a careful consideration with a lot of planning.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Saturday, December 27, 2014 8:58 PM
• And you think this is production code? Get real.

Are you going to tell me that nobody writes SELECT * in production code

I'm telling you that the question was not about production code.

If you want to teach,

If you want to teach, you need to learn manners first.

And you need to learn when a certain comment is relevant or not.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Saturday, December 27, 2014 9:09 PM