EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL Except Select
 

SQL Except Select

Priya Pedamkar
Article byPriya Pedamkar

Updated July 1, 2023

SQL Except Select

 

 

Introduction to SQL Except Select

SQL except select is useful while retrieving data from the query, and you have to mention that certain data should not be added in the final resultset. Manier times while fetching the data from the database, updating the records in the database, or deleting multiple records from the database, we have to mention that certain records from the retrieved resultset of the execution of the query should be excluded from the final resultset on which either of the operations will get performed i.e select, update or delete. In the query, after the SELECT statement, you can use the EXCEPT clause to specify either hardcoded values or another query statement that will determine the values to be excluded from the final result set.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

In this article, we will study the general syntax of the except clause inside the select clause and also learn about its implementation using multiple examples. Further, we will learn about the difference in the working of the not in the clause and except. Microsoft SQL Server 2005 introduced except clause and its functionality.

Syntax of SQL Except Select

The syntax of except select clause in the select operation of the SQL query statement is as shown below –

SELECT column name 1 [, column name 2 ] FROM table name 1 [, table name 2 ] [WHERE condition or restriction]
EXCEPT
SELECT column name 1 [, column name 2 ] FROM table name 1 [, table name 2 ] [WHERE condition or restriction]

In the above syntax, the first select statement that is on the right side is the main query that retrieves the resultset from the table named table name 1, and the retrieved values contain values of columns with names column name 1, column name 2, and so on. Now what the EXCEPT keyword does is that the rows that are retrieved from the second select query statement on the left side should be removed from the result set obtained after execution of the right side query if present. Note that both the queries on the left and right sides should retrieve the same number of comparative data-typed values from each other similar to union clause usage.

Examples of SQL Except Select

Let us first consider a simple example where we have one existing table named developers, which has the structure and contents of its table that are as shown in the output of the below query statement –

SQL Except Select-1.1

To exclude records with a salary less than 20000 from the result set of a query that retrieves name, position, and technology from the developer’s table, you can use the EXCEPT keyword in the following query statement:

SELECT
NAME,
POSITION,
technology
FROM
`developers`
EXCEPT
SELECT
NAME,
POSITION,
technology
FROM
`developers`
WHERE salary < 20000;

The execution of the above query statement gives an output that is as shown below-

SQL Except Select-1.2

We can observe that all the developers having a salary greater than or equal to 2000 are included in the final result set, and the others with less than 20000 are excluded because those many records were retrieved from the second select statement after except. Now, let us consider one more example where we have two tables two existing tables named Articles and UpdatedArticles whose contents and structure are as shown in the output of the following query statement –

SELECT * FROM Articles;

The execution of the above query statement gives an output that is as shown below –

SQL Except Select-1.3

SELECT * FROM UpdatedArticles;

The execution of the above query statement gives an output that is as shown below –

SQL Except Select-1.4

Now, we have to retrieve the records from the updated articles table such that those records should not be present in the table named articles. Note that both the tables contain the same columns, and we have to retrieve the topic of the article and its rate. For this, we can make use of the EXCEPT clause and build our query statement as follows –

SELECT
`ArticleTopic`,
`Rate`
FROM
`UpdatedArticles`
EXCEPT
SELECT
`ArticleTopic`,
`Rate`
FROM
`Articles` ;

The execution of the above query statement gives an output that is as shown below –

SQL Except Select-1.5

We can observe that only a single record is retrieved, a this is the only one that was present in updated articles but not in articles table records.

Difference between EXCEPT and NOT IN Clauses

We can see that the functionality of both clauses is the same. That is, they help specify certain resultsets that should not be included in the final resultset of the query. But there lie many differences in their working.

When using the EXCEPT clause in a query, it is necessary to specify all the column values that the query will retrieve in the query that specifies the records to be excluded. This means that the exclusion constraint applies to all the column values in the result set.

Recommended Articles

We hope that this EDUCBA information on “SQL Except Select” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. For Loop in Unix
  2. Crontab in Unix
  3. Array in Unix
  4. VI Editor in Unix

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW