SQL Query to Exclude Multiple Values
Last Updated : 13 Sep, 2021
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;
Similar Reads
SQL Query to Exclude Null Values
In relational databases, managing NULL values is a critical aspect of data integrity and accuracy. A NULL value signifies the absence of data in a column, distinguishing it from a zero, which is an integer, or a blank space, which is a character. Queries involving NULL values require careful handlin
3 min read
SQL Query for Matching Multiple Values in the Same Column
Querying multiple values within a single column is a vital skill in SQL, enabling users to filter and retrieve data based on specific criteria. Whether we're working with large datasets or simple tables, mastering techniques like the IN clause, LIKE operator, and comparison operators (e.g., >=) e
4 min read
How to Get Multiple Counts With One SQL Query?
Efficiency is important in database management, and performing operations like data retrieval should be optimized. Obtaining multiple counts in a single query is a useful technique to enhance performance and streamline queries. Instead of executing separate queries for different conditions, we can u
6 min read
SQL Query to Delete Duplicate Columns
Through this article, we will learn how to delete duplicate columns from a database table. As we know that duplicity in our database tends to be a waste of memory space. It records inaccurate data and is also unable to fetch the correct data from the database. To remove the duplicate columns we use
2 min read
SQL Query to Find Unique Column Values From Table
Finding unique column values is a common task in SQL when analysing data or ensuring data integrity. By using the DISTINCT clause, we can efficiently retrieve unique values from a specified column, avoiding duplicate results. In this article, we will explain the use of the DISTINCT clause with detai
3 min read
How to Ignore 0(zero) Values in SQL Query?
In SQL, many times we are required to display all the rows of a table in which the entries having 0(zero) as an entry/value are ignored. This is achieved through REPLACE command in SQL. In this article, we will discuss how to ignore 0(zero) and replace them with an empty space in SQL. For this artic
2 min read
SQL Query to Delete Duplicate Rows
Duplicate rows in a database can cause inaccurate results, waste storage space, and slow down queries. Cleaning duplicate records from our database is an essential maintenance task for ensuring data accuracy and performance. Duplicate rows in a SQL table can lead to data inconsistencies and performa
6 min read
SQL Query to Find the Sum of all Values in a Column
In SQL, calculating the sum of values in a column is a crucial task for performing data analysis and generating reports. The SUM() function helps to calculate the total sum of numeric values from a column, which is especially useful in scenarios like finding total sales, total employees, or total re
5 min read
SQL - Multiple Column Ordering
SQL is the standard language used for managing and manipulating databases. One of its powerful features is the ability to sort data using the ORDER BY clause, allowing us to arrange the query results in a meaningful way. By default, SQL sorts data in ascending order, but we can customize it to sort
4 min read
How to Get Multiple Counts With Single Query in SQLite?
In data analysis, obtaining multiple counts for different categories is a common requirement. SQLite, a lightweight and versatile database management system, offers a powerful feature that allows us to achieve this efficiently. In this article, we'll explore how to use SQLite to retrieve multiple co
3 min read