Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

What is the "EXCEPT" operator in SQL?

Educative Team

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Overview

The SQL EXCEPT operator joins two SELECT statements and retrieves rows from the first SELECT statement not obtained by the second SELECT statement. This indicates that EXCEPT only returns entries that aren’t accessible in the second SELECT query.

Syntax

SELECT <Attributes> FROM <Table1>
EXCEPT
SELECT <Attributes> FROM <Table2>

The following illustration helps us to grab this concept more firmly. Let’s look into it.

Except operator

Code

To determine which students are not participating in cricket, utilize the EXCEPT statement.

use msdb;
-- Create Student table
CREATE TABLE dbo.Students
(
    ID int,
    Name varchar(15),
    Age int
);
-- Insert values
INSERT INTO dbo.Students VALUES(1, "Hamid", 21);
INSERT INTO dbo.Students VALUES(2, "Haris", 24);
INSERT INTO dbo.Students VALUES(3, "Ali", 22);
INSERT INTO dbo.Students VALUES(4, "Jawad", 21);
INSERT INTO dbo.Students VALUES(5, "Khan", 25);
INSERT INTO dbo.Students VALUES(6, "Moiz", 20);
-- Display Students table
SELECT * FROM msdb.dbo.Students 

-- Create Cricker_Team table
CREATE TABLE dbo.Cricket_Team
(
    Player varchar(15),
    Role varchar(15)
);
-- Insert values
INSERT INTO dbo.Cricket_Team VALUES("Hamid", "Batsman");
INSERT INTO dbo.Cricket_Team VALUES("Haris", "Keeper");
INSERT INTO dbo.Cricket_Team VALUES("Moiz", "Batsman");
-- Dislay Cricker_Team table
SELECT * FROM msdb.dbo.Cricket_Team 

-- Using except statement
SELECT Name FROM dbo.Students
Except
SELECT Player FROM dbo.Cricket_Team;

-- Dropping tables
drop table msdb.dbo.Students;
drop table msdb.dbo.Cricket_Team;

Explanation

  • Line 1: We select the database msdb.
  • Lines 3–8: We create the Students table using schema dbo.
  • Lines 10–15: We insert values in the Students table.
  • Line 17: We display the Students table.
  • Lines 20–24: We create the Cricket_Team table using schema dbo.
  • Lines 26–28: We insert values in the Cricket_Team table.
  • Lines 33-35: We use the EXCEPT keyword to find out which students are not taking part in cricket.
  • Lines 38 and 39: We use the DROP statement to delete the Students and Cricket_Team tables.

RELATED TAGS

sql
Copyright ©2022 Educative, Inc. All rights reserved

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring