SQL AND, OR and NOT Operators

In this tutorial, we are going to learn SQL AND, OR, NOT OPERATOR. with examples using SQL server.

The where clauses are combined with AND, OR, NOT operators.

where clause and with AND Operator have required two conditions are true.

where clause and with OR operator have required one of two conditions is true.

where clause with not-operator negates the conditions and displays others.

AND Syntax:

  
SELECT 
   column1, 
   column2, ...
   column N
FROM 
   table_name
WHERE 
   condition1 AND condition2 ... ; 
  
  

OR Syntax:

  
SELECT 
  column1, 
  column2, ...
  column N
FROM 
  table_name
WHERE 
  condition1 OR condition2;  
  
  

NOT Syntax:

  
SELECT 
   column1, 
   column2, ...
   column N
FROM 
   table_name
WHERE NOT 
   condition;
  
  

Now, we are going to create a table and name as a student in the SQL server

  
create table student
(
s_id int primary key,
s_name varchar(20) not null,
score int not null,
status varchar(20) not null,
city varchar(20) not null,
gender varchar(20) not null
);  
  
  

  
insert into student values(101,'jack',297, 'fail','chennai','male'); 
insert into student values(102,'Rithvik',367, 'pass','chennai','male'); 
insert into student values(103, 'Jaspreet',342 ,'fail','vellore','male'); 
insert into student values(104,'Praveen',498,' pass','chennai','male'); 
insert into student values(105,'Bisa',397,' pass' ,'thiruvannamalai','male'); 
insert into student values(106, 'Suraj',224 ,'fail','vellore','male'); 
insert into student values(107,'abinaya',423,' pass','chennai','female'); 
insert into student values(108,'swetha',563,' pass','chennai','female'); 
insert into student values(109,'raju',351,' fail','vellore','male'); 
insert into student values(110,'rahul',368,' pass','chennai','male'); 
  
  

---

AND OPERATOR:

  
SELECT *
  FROM student
WHERE  
  city='chennai' AND gender='female';  
  
  

---

OR OPERATOR:

  
select * 
 FROM student 
WHERE city ='chennai' OR city ='vellore';  
  
  

---

NOT OPERATOR:

  
SELECT * 
  FROM student 
WHERE not city='chennai';  
  
  

---

WHERE COMBINE NOT, AND CONDITION:

  
SELECT *
  FROM student
WHERE NOT city='chennai' 
      AND gender='male';  
  
  

---

WHERE COMBINE NOT , OR CONDITION:

  
select * 
FROM student 
WHERE NOT city='chennai' OR city ='vellore';  
  
  

---

WHERE COMBINE AND , OR OPERATOR:

  
SELECT * 
  FROM student
  WHERE 
gender='male' AND 
  (City='vellore' OR City='chennai');  
  
  

---