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 )

Answer 1

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, [e-mail]

Answer 2

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

Answer 3

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

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

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

Answer 5

Neither SQL work, there is no table name.

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

Hope above help !

Doanh

Answer 6

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

Answer 7

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, [e-mail]

Answer 8

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

I have added the Table name in your sample code.

Thanks!

Answer 9

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

Answer 10

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, [e-mail]