What Are The Excluded Values

Introduction to SQL EXCLUDE

In SQL, in order to EXCLUDE certain rows from being returned by a SELECT query, we utilise some restricting or excluding conditions based on some criteria. EXCLUDE conditions in SQL unremarkably appear in the WHERE clause of the statement or in the HAVING clause of an aggregate query. Some commonly used EXCLUDE operators in SQL are NOT, NOT IN, Non LIKE, '!=', EXCEPT, NOT NULL, etc.

Y'all must be wondering what happens when you mention a restricting condition in a SELECT statement. It is actually pretty simple. When the SELECT query runs, the database server applies the restricting condition on each row that is being fetched while searching. If the row meets the mentioned condition, it is included.

Syntax and Parameters

The bones syntax for writing exclude queries in SQL are as follows:

SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name1 conditional_operator comparision_expression;

If y'all want to write exclude queries using the EXCEPT operator, the syntax is as follows :

SELECT column_name1, column_name2, ...
FROM table_name1
EXCEPT
SELECT column_name1, column_name2, ...
FROM table_name2;

Parameters:

The parameters used in the higher up-mentioned syntax are as follows :

• column_name1, column_name2, … : columns or field names that accept to be fetched for the final upshot set.
• table_name1, table_name2: database tables from which the above-mentioned columns take to be fetched.
• conditonal_operator: operators such as (Non IN, Not LIKE, Not NULL, NOT EQUAL TO (!=), etc.)
• comparison_expression: subquery or expression based on which exclusion has to be washed.

Examples of SQL EXCLUDE

In order to illustrate the excluding or restricting of sure rows in SQL, let us create a dummy tabular array called "students". We can utilise the post-obit CREATE statement to create the student'due south table.

CREATE Table students(
roll_no int Non Cipher PRIMARY Key,
student_name VARCHAR(255),
degree_major VARCHAR(255) NOT NULL,
degree_year VARCHAR(255),
guild VARCHAR(255)
);

Having created the students table. Let us insert a few records in it to work with.

INSERT INTO [practice_db].[dbo].[students] ([roll_no] ,[student_name] ,[degree_major] ,[degree_year] ,[society])
VALUES
(1,'Mohith 1000','Informatics Engineering','IV','Dramatics'),
(2,'Ayesha Khan','Electrical Technology','I','Music'),
(3,'Kylie Green','Computer Science Engineering','III','Choreography'),
(4,'Alisha Rojer','Chemic Engineering science','Three','Music'),
(5,'Andy Bernard','Geosciences','Four','Dramatics')
Go

The data in the students table looks something as follows:

At present we are all ready to effort some examples on excluding rows in SQL.

Case #1

Exclude rows using Non EQUAL to operator

Find details of all the students excluding students with roll_no 4.

SELECT *
FROM students
WHERE roll_no != 4 ;

Instance #2

Exclude rows using Non Like operator

Find details of all the students excluding pupil whose name starts with A.

SELECT *
FROM students
WHERE student_name NOT Like 'A%';

Case #3

Exclude rows using NOT IN operator

Discover details of all the students excluding students whose roll_no is not 1, iii, or 5.

SELECT *
FROM students
WHERE roll_no NOT IN (i,3,five);

Example #four

Exclude rows using Not  IN operator with subqueries on the same table.

Notice details of all the students excluding educatee who are from 'Dramatics' or 'Music' society.

SELECT *
FROM students
WHERE student_name NOT IN (SELECT student_name
FROM students
WHERE
lodge = 'Dramatics' OR society = 'Music' );

Exclude rows using Not IN operator with subqueries on a different table.

In order to illustrate this, permit us create a tabular array called "home_town" that contains details pertaining to the abode cities of students in the students table.

CREATE Tabular array [dbo].[home_town](
[roll_no] [int] NOT Nothing,
[home_city] [varchar](l) Aught
)
INSERT INTO [practice_db].[dbo].[home_town] ([roll_no] ,[home_city])
VALUES
(2, 'New York'),
(iii, 'New Bailiwick of jersey'),
(5, 'Denver')
Get

Example #5

Find details of all the students excluding pupil whose roll_no is non in the list of roll_nos where the home_city is 'Denver'.

SELECT *
FROM students
WHERE roll_no <> (SELECT roll_no
FROM home_town
WHERE home_city = 'Denver' );

Example #6

Exclude rows with Nil values from a LEFT / RIGHT Join

Find the roll_no, student_name, degree_major, lodge, and home_city for students excluding rows where home_city is Cypher or does not exist.

SELECT s.roll_no,
south.student_name,
s.degree_major,
s.degree_year,
s.social club,
h.home_city
FROM students as s LEFT JOIN home_town as h
ON s.roll_no = h.roll_no
WHERE h.roll_no IS Non Zilch;

Instance #7

Exclude rows using EXCEPT operator

In order to exclude rows that are present only in the results of the first select argument but not in the 2nd, we can utilize EXCEPT operator.

Find the roll_no of all the students except those mentioned in the home_town table.

SELECT roll_no
FROM students
EXCEPT
SELECT roll_no
FROM home_town;

Advantages

Some of the advantages are given below:

1. Helps in limiting the number and kind of rows that can brand it to the concluding result ready.
2. Helps to excerpt and insert only relevant results when using SQL queries, such equally: SELECT, UPDATE, or DELETE statement.

Conclusion

To restrict the kind and number of rows a SELECT query should return, nosotros use some exclude weather condition which work equally filter criteria. These filter criteria are mentioned in the WHERE clause of SELECT, UPDATE or DELETE statements.

Recommend ed Articles

This is a guide to SQL EXCLUDE. Here we also discuss the introduction and syntax and parameters of sql exclude forth with unlike examples and its code implementation. Yous may also have a wait at the following articles to larn more –

1. SQL WITH AS Statement
2. MySQL List User
3. PostgreSQL Extensions
4. PostgreSQL ORDER BY DESC

What Are The Excluded Values,

Source: https://www.educba.com/sql-exclude/

Posted by: williamsstenly.blogspot.com

Share this post