How to Exclude Records With Certain Values in SQL Select?

In this article, we will understand how to exclude some records having certain values from a table. For the purpose of demonstration, we will be creating a Participant table in a database called “GeeksForGeeksDatabase“.

Step 1: Creating the Database

Use the below SQL statement to create a database called GeeksForGeeksDatabase.

Query:

CREATE DATABASE GeeksForGeeksDatabase;

Step 2: Using the Database

Use the below SQL statement to switch the database context to GeeksForGeeksDatabase.

Query:

USE GeeksForGeeksDatabase;

Step 3: Table Definition

Query:

CREATE TABLE Geeks(
GeekID INTEGER PRIMARY KEY,
GeekName VARCHAR(255) NOT NULL,
GeekRank INTEGER NOT NULL,
GeekSchool VARCHAR(255) NOT NULL
);

Step 4: Insert some data into the table

Query:

INSERT INTO Geeks VALUES (101, 'Nix',2 ,'Code Valley School');
INSERT INTO Geeks VALUES (102, 'Rutz',4 ,'Blue Chip School');
INSERT INTO Geeks VALUES (103, 'Shrey',1 ,'GCOEA School');
INSERT INTO Geeks VALUES (104, 'Ankx',3 ,'Round Robin Play School');
INSERT INTO Geeks VALUES (105, 'Ridz',7 ,'Dream School');
INSERT INTO Geeks VALUES (106, 'Mayo',6 ,'Silver Shining School');
INSERT INTO Geeks VALUES (107, 'Bugs',5 ,'Twinkle Star Convent');

You can use the below statement to see the contents of the created table:

Query:

SELECT * FROM Geeks;

Now let’s see how to exclude some records from the table according to certain conditions.

There are many ways to do so, lets see the examples one by one:

Query:

Query to exclude a student from a particular school i.e. Blue Chip School. NOT shows those records where the condition is NOT TRUE.

Note: If we haven't use NOT here then the result would be the opposite.

SELECT * FROM Geeks WHERE NOT GeekSchool = 'Blue Chip School';

This query will output all students except the students with a given school:

We can also exclude some more records by providing where conditions are separated by AND OR operator.

Note: We can also do the same using != operator

Query:

SELECT * FROM Geeks WHERE NOT GeekID > 104;

Now see the difference in how NOT is working. Here in the example, we provided the condition, which when true follows the NOT means the query will select all the rows for which the provided condition is not true.

In the above output, the condition GeekID > 104 is satisfied and due to NOT all the rows are selected which are less than 104.

Note: We can also do the same using != operator

Query:

SELECT * FROM Geeks WHERE GeekID NOT IN (104,101,102,107);

In this query, we are excluding those records (rows) where GeekID does not lie in the provided list (i.e. GeekID should not be 104,101,102,107)

So the resultant data will contain the records excluding the provided Geek ids.

Thus we can apply any condition to any column of the table and exclude those using NOT operator.

Query:

We can also provide subquery in IN operator and can also include one or many conditions using WHERE clause :

SELECT * FROM Geeks WHERE GeekRank NOT IN (SELECT GeekRank FROM Geeks WHERE GeekRank >= 4);

The resultant table selects all the rows which not satisfies the condition GeekRank >=4, So all the geeks with ranks above 4 are selected. We can also combine many conditions together and get different results accordingly.

chaudharinikita9999

Follow

Improve

Article Tags :

• SQL
• Blogathon
• SQL-Server