Logical Operators: AND OR NOT XOR

AND && AND Operator
OR || OR Operator
NOT ! Negates a Value
XOR XOR Operator
SQL AND OR NOT and XOR statement can be used with WHERE clause to list a set of records with matching combination of a database table.
This is our table ( Showing 10 records ) ,

idnameclassmarksex
1John DeoFour78male
2Max RuinThree85male
3ArnoldThree55male
4Krish StarFour60male
5John MikeFour60male
6Alex JohnFour55male
7My John RobFifth78male
8AsruidFive85male
9Tes QrySix78male
10Big JohnFour55male
 
We will apply AND  command to display records of class Four with more than 70 mark.
SELECT * FROM student WHERE class='Four' AND mark >70
idnameclassmarksex
1John DeoFour78male
15Tade RowFour88male
16GimmyFour88male
31Marry ToeeyFour88female
WE can see the record returned is class= Four and mark is more than 70.

Here both the conditions are satisfied.
SELECT * FROM student WHERE class='Five' OR mark >90
idnameclassmarksex
8AsruidFive85male
12ReckySix94male
18HonnyFive75male
33Kenn ReinSix96male
Here the records returned are either belongs to class Five OR having mark more than 90. Any one of the two condition is to be satisfied.

AND OR

condition1 OR condition2 Any one of the condition has to be satisfied for the record
condition1 AND condition2 Both conditions are to be satisfied for the record.

Using both AND OR with bracket.

Display all students of class Five and Six who got more than 80 mark.
SELECT * FROM student WHERE class='Five' OR class='Six'  and mark >80
This will include some wrong records
idnameclassmarksex
8AsruidFive85male
11RonaldSix89male
12ReckySix94male
18HonnyFive75male
33Kenn ReinSix96male
35Rows NoumpSix88female
You have one student with 75 mark .... This is because in our query considers all students of class six who has got more than 80 but does not apply the same condition of mark more than 80 to students of class Five. We used one OR condition in wrong place. Here is the correct SQL
SELECT * FROM student WHERE (class='Five' OR class='Six') and mark >80
idnameclassmarksex
8AsruidFive85male
11RonaldSix89male
12ReckySix94male
33Kenn ReinSix96male
35Rows NoumpSix88female
We have different set of pass marks for different classes, display all who got more than pass mark.
SELECT * FROM student WHERE (class='Five' AND mark>75) OR
 (class='Six' AND mark>80) OR (class='Seven' AND mark>85)
idnameclassmarksex
8AsruidFive85male
11RonaldSix89male
12ReckySix94male
13KtySeven88male
14BigySeven88male
25Giff TowSeven88male
28Rojj BaseSeven86male
32Binn RottSeven90male
33Kenn ReinSix96male
35Rows NoumpSix88female

Using NOT

We use NOT to negate any comparison.
SELECT * FROM student WHERE NOT class = 'Five'
Output will include all classes except Five class.
SELECT * FROM student 
	WHERE  class NOT IN ( 'Five','Six','Four','Seven','Three')
idnameclassmarksex
19TinnyNine18male
20JacklyNine65female
23HerodEight79male
SELECT * FROM student WHERE NOT mark BETWEEN 50 AND 100
Output
idnameclassmarksex
19TinnyNine18male


XOR

Exclusive OR gives output as 1 ( True ) when inputs differ.
SELECT 1 XOR 0;      # 1
SELECT 0 XOR 1;      # 1
SELECT 1 XOR 1;      # 0
SELECT 0 XOR 0;      # 0
SELECT * FROM student WHERE class='Five' XOR mark<50
idnameclassmarksex
7My John RobFive78male
8AsruidFive85male
18HonnyFive75male
19TinnyNine18male

DELETE Query with OR AND

Delete all records of class three and class four
DELETE  FROM student where class='Three' OR class='Four'
Delete all records from class Six and class Four who got less than 80 mark
DELETE  FROM student where (class='Six' or class='Four') and mark<80

UPDATE Query with OR AND

Let us give extra 5 mark to class Three and class Four students who got more than 80 mark.
UPDATE `student` set mark=mark + 5  
	WHERE (class='Three' or class='Four')  and mark >80
Comparison Operators Selecting records SQL WHERE


plus2net.com






Post your comments , suggestion , error , requirements etc here





SQL Video Tutorials










We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer