Open In App

SQL Query to Exclude Multiple Values

Improve
Improve
Like Article
Like
Save
Share
Report

To exclude multiple values to be fetched from a table we can use multiple OR statements but when we want to exclude a lot of values it becomes lengthy to write multiple AND statements, To avoid this we can use the NOT IN clause with the array of values that need to be excluded with the WHERE statement.

In this article let us see the SQL query to exclude multiple values using both AND and NOT IN clauses.

Creating a Database

Use the below command to create a database named GeeksforGeeks:

CREATE DATABASE GeeksforGeeks

Using the Database

To use the GeeksforGeeks database use the below command:

USE GeeksforGeeks

Creating a Table

Create a table student_details with  4 columns using the following SQL query:

CREATE TABLE student_details(
stu_id VARCHAR(8),
branch VARCHAR(20),
course_code VARCHAR(10),
backlogs VARCHAR(10)
);

Verifying the Table:

To view the description of the tables in the database using the following SQL query:

EXEC sp_columns student_details;

Inserting Data into the Table:

Inserting rows into student_details tables using the following SQL query:

INSERT INTO student_details VALUES
  ('191401','E.C.E','ECPC-251', 'NO'),
  ('191302','I.C.E','ICPC-221','YES'),
  ('191305','I.C.E','ICPC-225','YES'),
  ('191410','E.C.E','ECPC-251', 'YES'),
  ('191210','M.E','MEPC-103', 'YES'),
  ('191215','M.E','MEPC-101', 'NO'),
   ('191505','C.E','CEPC-501', 'NO'),
  ('191525','C.E','CEPC-502', 'NO');

Verifying the Inserted Data

Viewing the table student_details after inserting rows by using the following SQL query:

SELECT* FROM employee_details;

Example Queries With the Syntax:

1. Query to find the students other than ‘ECE’, ‘ICE’, ‘ME’

Using NOT IN:

Syntax:

SELECT * FROM table_name
WHERE req_column NOT IN(data1,data2,data3,....)

Query:

SELECT* FROM student_details
WHERE branch NOT IN ('E.C.E','I.C.E','M.E');

Using AND:

Syntax:

SELECT * FROM table_name
WHERE condition1 AND condition2 AND condition3;

Query:

SELECT* FROM student_details
WHERE branch<>'E.C.E' AND branch <> 'I.C.E' AND branch<>'M.E';

2. Query to update the backlogs to NO other than students of C.E and M.E.

UPDATE student_details
SET backlogs='NO' WHERE branch NOT IN ('C.E','M.E');
SELECT* FROM student_details;


Last Updated : 13 Sep, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads