SQL AND-OR-NOT

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)

ABResult
TRUETRUETRUE
TRUEFALSEFALSE
FALSETRUEFALSE
FALSEFALSEFALSE

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)

ABResult
TRUETRUETRUE
TRUEFALSETRUE
FALSETRUETRUE
FALSEFALSEFALSE

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)

AResult
TRUEFALSE
FALSETRUE

SYNTAX – 

SELECT * FROM table_name WHERE NOT condition1;

Illustration –

Consider the below student table is stored in the database

student_idFirst_NameLast_NameColorAgeGrade
1000JohnMillerred15ninth
1001BrigittaDillonblue15ninth
1002PeterPippergreen13seventh
1003JudyWindlered12sixth
1004SampsonHansywhite10fourth




Query 1 – 

 SELECT * FROM student WHERE age > 10 AND color = ‘red’;

student_idFirst_NameLast_NameColorAgeGrade
1000JohnMillerred15ninth
1003JudyWindlered12sixth

Query 2 –

 SELECT * FROM student WHERE age = 13 OR color = ‘white’;

student_idFirst_NameLast_NameColorAgeGrade
1002PeterPippergreen13seventh
1004SampsonHansywhite10fourth

Query 3 –

 SELECT * FROM student WHERE NOT grade= ‘ninth’;

student_idFirst_NameLast_NameColorAgeGrade
1002PeterPippergreen13seventh
1003JudyWindlered12sixth
1004SampsonHansywhite10fourth

Query 4 –
SELECT * FROM student WHERE age > 10 and (color = ‘green’ or grade=’ninth’);

student_idFirst_NameLast_NameColorAgeGrade
1000JohnMillerred15ninth
1001BrigittaDillonblue15ninth
1002PeterPippergreen13seventh

 

Leave A Reply

Your email address will not be published. Required fields are marked *