SQL OR AND NOT XOR Tutorial: SQL logical operators in where clause with syntax and statement

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 ) ,

id	name	class	mark	sex
1	John Deo	Four	78	male
2	Max Ruin	Three	85	male
3	Arnold	Three	55	male
4	Krish Star	Four	60	male
5	John Mike	Four	60	male
6	Alex John	Four	55	male
7	My John Rob	Fifth	78	male
8	Asruid	Five	85	male
9	Tes Qry	Six	78	male
10	Big John	Four	55	male

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

id	name	class	mark	sex
1	John Deo	Four	78	male
15	Tade Row	Four	88	male
16	Gimmy	Four	88	male
31	Marry Toeey	Four	88	female

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

id	name	class	mark	sex
8	Asruid	Five	85	male
12	Recky	Six	94	male
18	Honny	Five	75	male
33	Kenn Rein	Six	96	male

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

id	name	class	mark	sex
8	Asruid	Five	85	male
11	Ronald	Six	89	male
12	Recky	Six	94	male
18	Honny	Five	75	male
33	Kenn Rein	Six	96	male
35	Rows Noump	Six	88	female

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

id	name	class	mark	sex
8	Asruid	Five	85	male
11	Ronald	Six	89	male
12	Recky	Six	94	male
33	Kenn Rein	Six	96	male
35	Rows Noump	Six	88	female

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)

id	name	class	mark	sex
8	Asruid	Five	85	male
11	Ronald	Six	89	male
12	Recky	Six	94	male
13	Kty	Seven	88	male
14	Bigy	Seven	88	male
25	Giff Tow	Seven	88	male
28	Rojj Base	Seven	86	male
32	Binn Rott	Seven	90	male
33	Kenn Rein	Six	96	male
35	Rows Noump	Six	88	female

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')

id	name	class	mark	sex
19	Tinny	Nine	18	male
20	Jackly	Nine	65	female
23	Herod	Eight	79	male

SELECT * FROM student WHERE NOT mark BETWEEN 50 AND 100

Output

id	name	class	mark	sex
19	Tinny	Nine	18	male

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

id	name	class	mark	sex
7	My John Rob	Five	78	male
8	Asruid	Five	85	male
18	Honny	Five	75	male
19	Tinny	Nine	18	male

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

Subscribe to our YouTube Channel here

Logical Operators: AND OR NOT XOR

AND OR

Using both AND OR with bracket.

Using NOT

XOR

DELETE Query with OR AND

UPDATE Query with OR AND

Subscribe