Sunday, 15 May 2011

SQL Logical Operators

SQL Logical Operators

There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.

Logical Operators -Description

OR -For the row to be selected at least one of the conditions must be true.
AND -For a row to be selected all the specified conditions must be true.
NOT -For a row to be selected the specified condition must be false.

"OR" Logical Operator:
If you want to select rows that satisfy at least one of the given conditions, you can use the logical operator, OR.

For example: if you want to find the names of students who are studying either Maths or Science, the query would be like,

SELECT first_name, last_name, subject
FROM student_details
WHERE subject = 'Maths' OR subject = 'Science'

"AND" Logical Operator:

If you want to select rows that must satisfy all the given conditions, you can use the logical operator, AND.

For Example: To find the names of the students between the age 10 to 15 years, the query would be like:

SELECT first_name, last_name, age
FROM student_details
WHERE age >= 10 AND age <= 15; "NOT" Logical Operator:

If you want to find rows that do not satisfy a condition, you can use the logical operator, NOT. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the row is not returned.

For example: If you want to find out the names of the students who do not play football, the query would be like:

SELECT first_name, last_name, games
FROM student_details
WHERE NOT games = 'Football'

Nested Logical Operators:

You can use multiple logical operators in an SQL statement. When you combine the logical operators in a SELECT statement, the order in which the statement is processed is

1) NOT
2) AND
3) OR

For example: If you want to select the names of the students who age is between 10 and 15 years, or those who do not play football, the

SELECT statement would be
SELECT first_name, last_name, age, games
FROM student_details
WHERE age >= 10 AND age <= 15
OR NOT games = 'Football'

No comments:

Post a Comment