Sunday, February 10, 2013

How to use AND,OR Operators in SQL Server

SQL Server AND,OR Operators
The AND operator displays a record if both the first condition and the second condition are true.
The OR operator displays a record if either the first condition or the second condition is true.
AND Operator:
Suppose we have a and Employee Table as follows :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
David
Team Lead
2
35000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
18000
1008
Vivek
Team Lead
1
33000
1006
Smith
Team Lead
3
50000
Now we want select the Job='Analyst' AND Salary>1000
SELECT * FROM EMP WHERE Job='AnalystAND Salary > 10000

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1005
Sai Kumar Teja
Analyst
2
15000
1007
Vikram Teja Reddy
Analyst
2
18000

OR Operator :
Now we want to select  the Employees whose Job equal to "Analyst" OR 'Manager' , using below SELECT statement:
SELECT * FROM EMP WHERE Job='AnalystOR Job='Manager

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1003
Ravi Kumar Rao
Analyst
1
10000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
18000

Combining AND & OR:
You can also combine AND and OR (use parenthesis to form complex expressions).
Now we want to select  the Employees whose Dept_ID = 1 OR 2 AND Salary > 15000 , using below statement.
SELECT * FROM EMP WHERE Salary > 15000 AND (Dept_ID=1 OR Dept_ID=2 )

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
David
Team Lead
2
35000
1007
Vikram Teja Reddy
Analyst
2
18000
1008
Vivek
Team Lead
1
33000

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog