Home » Enterprise Java » sql » SQL EXCEPT operator

About Yatin

Avatar photo
An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).

SQL EXCEPT operator

Hello. In this tutorial, we will learn the EXCEPT operator in SQL and will use the PostgreSQL database running on Docker.

1. Introduction

SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time, manages a large amount of data that is written and read simultaneously, and any query that reaches the server is processed into three parts –

  • Parsing – Process to check the query syntax
  • Binding – Process to check the query semantics
  • Optimization – Process to generate the SQL query execution plan

1.1 Usage of SQL

Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too –

  • Execute queries against the database
  • Retrieve data from the database
  • Insert new records into the database
  • Update existing records in the database
  • Created stored procedures, functions, and materialized views in the database
  • Create users and grant permissions
  • Set permissions on tables, stored procedures, functions, and materialized views

1.2 EXCEPT operator in SQL

  • The EXCEPT operator in returns all rows in the first SELECT statement that is not returned by the second SELECT statement
  • Follow the two rules –
    • The number and order of columns in both queries have to be the same
    • Data types of corresponding columns from both the SELECT statements must be compatible with each other
    • The fields in the respective columns of the two SELECT statements cannot be the same

The EXCEPT operator in SQL is represented by the below syntax.

Query Syntax

select expression1, expression2,... expression_n
from tables
[where conditions]
except
select expression1, expression2,... expression_n
from tables
[where conditions];

2. EXCEPT operator in SQL

Let us dive into some practice implementation on the PostgreSQL database.

2.1 Pre-requirement – Postgres Setup

Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run PostgreSQL.

Docker commands

-- command to run postgres on docker –

-- remember to change the password --
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres

-- command to stop the Postgres docker container --
docker stop postgres

-- command to remove the Postgres docker container --
docker rm postgres

Remember to enter the password of your choice. If everything goes well the PostgreSQL database server would be up and running on a port number – 5433 and you can connect with the Dbeaver GUI tool for connecting to the server.

Fig. 1. Postgres on Docker

2.2 Setting up pre-requisite data

Create the database named learning and add the following table and data to the tables with the help of the below SQL script.

Pre-requisite sql script

-- student
create table student(
  stuid serial primary key, name varchar, city varchar
);

insert into student (name, city)
values 
  ('ootterwell0', 'Mocupe'),('gfrondt1', 'Río Limpio'),('msegot2', 'Lahar'),('jkeerl3', 'Cikubang'),('llyman4', 'Barrouallie'),('lczajka5', 'Fangtang'),('kbeacon6', 'Saint-Denis');

select *
from student;

-- employee
create table employee(
  empid serial primary key, name varchar, city varchar
);

insert into employee (name, city)
values 
  ('ootterwell0', 'Mocupe'),('gfrondt1', 'Río Limpio'),('msegot2', 'Lahar');

select *
from employee;

Once the SQL script is executed the mock data will be successfully added to the student and employee tables respectively.

2.3 Playing with the EXCEPT operator

Use the below SELECT keyword to understand the EXCEPT SQL operator.

EXCEPT operator

-- sql except example
-- get all rows in the first select statement that are not returned by the second select statement
select name, city
from student
except 
select name, city
from employee
order by name;

If everything goes well the result of the SQL query will be shown on the console and the usage of the EXCEPT operator can be practically understood.

Fig. 2: SQL EXCEPT operator
Fig. 2: SQL EXCEPT operator

3. Summary

In this tutorial, we learned the brief of SQL EXCEPT operator with the help of an example. You can download the scripts used in this tutorial from the Downloads section.

4. Download the SQL scripts

This was a tutorial to understand the EXCEPT operator in SQL.

Download
You can download the full source code of this example here: SQL EXCEPT operator

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments