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 “w3wikiDatabase“.

Step 1: Creating the Database

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

Query:

CREATE DATABASE w3wikiDatabase;

Step 2: Using the Database

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

Query:

USE w3wikiDatabase;

Step 3: Table Definition

Query:

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

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

Query:

SELECT * FROM Beginner;

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 Beginner 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 Beginner 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 Beginner 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 Beginner WHERE GeekRank NOT IN (SELECT GeekRank FROM Beginner WHERE GeekRank >= 4);

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