Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • PostgreSQL - Get Started
  • Install PostgreSQL
  • Connect to PostgreSQL DB
  • Create Database
  • Create Table
  • Copy Table
  • Drop Table
  • Drop Database
  • Truncate Table
  • ALTER Table
  • Rename Table
  • Rename Columns
  • Add Columns
  • Modify Column Type
  • Set Default Value of Column
  • Remove Columns
  • Add Constraints to Table
  • Insert Data
  • Upsert Data
  • Update Data
  • Delete Data
  • SELECT Statement
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • DISTINCT Clause
  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self Join
  • Natural Join
  • Cross Join
  • LIMIT OFFSET Clause
  • GROUPING SETS
  • GROUPING() Function
  • GROUP BY CUBE
  • GROUP BY ROLLUP
  • Sub Query
  • ALL Operator
  • ANY Operator
  • UNION Operator
  • INTERSECT Operator
  • EXCEPT Operator
  • IS NULL Operator
  • BETWEEN Operator
  • LIKE Operator
  • CAST Operator
  • CASE Expressions
  • NULLIF()
  • COALESCE()
  • GREATEST(), LEAST()
  • WITH Queries (CTE)
  • Constraints
  • NOT NULL Constraint
  • Unique Constraint
  • Check Constraint
  • Primary Key
  • Foreign Key
  • Sequence
  • Serial Type
  • Identity Columns
  • Generated Columns
  • Data Types
  • Boolean Type
  • Character Type
  • Integer Type
  • Numeric Type
  • Date Type
  • Time Type
  • TimeStamp Type
  • Interval Type
  • Array Type
  • Json Type
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

PostgreSQL EXCEPT Operator

PostgreSQL EXCEPT operator is used to combine the result set of two or more SELECT statements into a single result set. The EXCEPT operator returns all rows from the first SELECT statement that are not returned by the second SELECT statement.

The UNION, UNION ALL, INTERSECT, and EXCEPT operators generally used with same kind of tables to find the same or different data in tables.

Syntax
SELECT Query 1
EXCEPT 
SELECT Query 2
EXCEPT
SELECT Query 3
...

The following rule must be applied to combine the result set of two or more quires using the EXCEPT operator.

  • There must be the same number of columns or expressions in each SELECT statement.
  • The corresponding columns in each SELECT statement must have similar data types.

We will use the following Employee and Person tables to understand how EXCEPT operator works.

The following example demonstrates the EXCEPT operator with the SELECT query.

Example: EXCEPT Operator
SELECT first_name, last_name FROM Employee
EXCEPT
SELECT first_name, last_name FROM Person;

The following displays the result of the above query in pgAdmin.

The above result includes the rows from the first SELECT statement which is not available in the result set of the second SELECT statement.

The EXCEPT operator matches the value in each column in both the queries. If a value of any column does not match with the same column (in a sequence) in the second query then it includes that row in the result. For example, if the last_name value is ‘john’ where the first_name is ‘Annie’ then it would be considered as a different row than any of the rows in the Person table because the last_name in the Person table is ‘Smith’.

Let’s use ORDER BY to fetch the same result set with descending order of first_name.

Example: EXCEPT Operator
SELECT first_name, last_name FROM Employee
EXCEPT
SELECT first_name, last_name FROM Person ORDER BY first_name DESC;

It will raise an error if the number of columns and the types of columns are not matching. For example, the following query includes three columns in the first SELECT query whereas only two columns in the second SELECT query and so it will raise an error.

TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.