SQL AND/OR/NOT
SQL provides 3 different logical operators(AND, OR, NOT) to use either separately or in combination in a WHERE clause. This is similar to what you would have learned in school/college days. You would recollect it once you take a look at the below truth table for each of them –
AND Operator – (If either one of the condition is false, the result is false. AND operator will return true, only if both the conditions are true)
A | B | Result |
---|---|---|
TRUE | TRUE | TRUE |
TRUE | FALSE | FALSE |
FALSE | TRUE | FALSE |
FALSE | FALSE | FALSE |
SYNTAX –
SELECT * FROM table_name WHERE condition1 AND condition2 …;
OR Operator – (If either one of the condition is true, the result is true. OR operator will return false, only if both the conditions are false)
A | B | Result |
---|---|---|
TRUE | TRUE | TRUE |
TRUE | FALSE | TRUE |
FALSE | TRUE | TRUE |
FALSE | FALSE | FALSE |
SYNTAX –
SELECT * FROM table_name WHERE condition1 OR condition2 …;
NOT Operator – ( If the condition is true, the result is false. If the condition is false, the result is true)
A | Result |
---|---|
TRUE | FALSE |
FALSE | TRUE |
SYNTAX –
SELECT * FROM table_name WHERE NOT condition1;
Illustration –
Consider the below student table is stored in the database
student_id | First_Name | Last_Name | Color | Age | Grade |
---|---|---|---|---|---|
1000 | John | Miller | red | 15 | ninth |
1001 | Brigitta | Dillon | blue | 15 | ninth |
1002 | Peter | Pipper | green | 13 | seventh |
1003 | Judy | Windle | red | 12 | sixth |
1004 | Sampson | Hansy | white | 10 | fourth |
Query 1 –
SELECT * FROM student WHERE age > 10 AND color = ‘red’;
student_id | First_Name | Last_Name | Color | Age | Grade |
---|---|---|---|---|---|
1000 | John | Miller | red | 15 | ninth |
1003 | Judy | Windle | red | 12 | sixth |
Query 2 –
SELECT * FROM student WHERE age = 13 OR color = ‘white’;
student_id | First_Name | Last_Name | Color | Age | Grade |
---|---|---|---|---|---|
1002 | Peter | Pipper | green | 13 | seventh |
1004 | Sampson | Hansy | white | 10 | fourth |
Query 3 –
SELECT * FROM student WHERE NOT grade= ‘ninth’;
student_id | First_Name | Last_Name | Color | Age | Grade |
---|---|---|---|---|---|
1002 | Peter | Pipper | green | 13 | seventh |
1003 | Judy | Windle | red | 12 | sixth |
1004 | Sampson | Hansy | white | 10 | fourth |
Query 4 –
SELECT * FROM student WHERE age > 10 and (color = ‘green’ or grade=’ninth’);
student_id | First_Name | Last_Name | Color | Age | Grade |
---|---|---|---|---|---|
1000 | John | Miller | red | 15 | ninth |
1001 | Brigitta | Dillon | blue | 15 | ninth |
1002 | Peter | Pipper | green | 13 | seventh |