AND OR NOT Logical Connectors in SQL
For Complete YouTube Video: Click Here
In this class, we will understand AND OR NOT Logical Connectors in SQL.
We have already discussed the concepts of IN NOT IN and BETWEEN NOT BETWEEN.
Table of Contents
AND OR NOT Logical Connectors in SQL
AND OR NOT Keywords are the logical connectors in SQL.
They are used to Bind the logical Expressions.
AND & OR are binary connectors.
A binary connector means they will bind two expressions.
The logical AND operator is true if both the expression are true.
The logical OR is true if any one of the expressions or both the expressions are true.
NOT is a unary operator.
A Unary operator is used only with one expression.
NOT operator is true only if the expression is false.
Examples on AND OR NOT Logical Connectors in SQL
To understand all the examples, we will consider the following table.
Query 1: Write a Query to find the male students whose salary is greater than 200.
In the above query, we have to select all the students whose sex is male (M) AND whose salary is greater than 200.
The query is as shown below.
SELECT * FROM students WHERE sex = ‘M’ AND salary > 200;
The output for the query is as shown below.
Query 2: Write a Query to find the students of Section A or C.
The above query states that we have to select all the students who belong to either section A or B.
The query is as shown below.
SELECT * FROM students WHERE section = ‘A’ OR section = ‘C’;
The output for the query is as shown below.
Query 3: Write a Query to find the students of Section A or C and Salary > 200.
This query is a bit tricky to understand.
Let us consider the query as shown below.
SELECT * FROM students WHERE section = ‘A’ OR section = ‘C’ AND salary > 200;
This query seems to produce the correct results, but the output is erroneous.
The output is shown below.
In the above output, it is considering the student whose salary is 100.
This error is because of the precedence of the operators.
Among all the logical operators, AND has the highest precedence.
The SQL considers the query as shown below.
SELECT * FROM students WHERE section = ‘A’ OR (section = ‘C’ AND salary > 200);
The query selects the students belonging to section A OR a student belonging to section C AND salary greater than 200.
The correct query to select section A or C students whose salary is greater than 200 is below.
SELECT * FROM students WHERE (section = ‘A’ OR section = ‘C’) AND salary > 200);
The output of the query is below.
Query 4: Write a Query to find the students whose salary is NOT greater than 200.
We have to select the students whose salary is NOT greater than 200.
The query and the output are as shown below.
SELECT * FROM students WHERE NOT salary > 200;
