Sql Except | Minus Operator


The SQL EXCEPT operator is used to compare and get only unique results from two result sets.

It is used to combine two SELECT statements and returns records from the first SELECT statement that are not available by the second SELECT statement.

The two queries must result in the same number of columns and compatible data types in order to combine.


Search Keys

  • sql except example
  • sql except query
  • sql except all
  • sql except vs minus
  • sql except vs not in
  • sql except w3schools
  • sql except oracle
  • sql except intersect
  • sql except not working
  • sql except vs not exists
  • sql except exclude column
  • sql except example oracle
  • sql except efficiency
  • sql except equivalent
  • sql exclude except
  • sql server except example
  • sql except mysql
  • sql except minus
  • sql except multiple columns

Sql Except | Minus Syntax

The below syntax is used to create except operator with select statements.


SELECT column-name1, column-name2, ... column-nameN 
FROM table-name 
[WHERE conditions]

EXCEPT

SELECT column-name1, column-name2, ... column-nameN 
FROM table-name 
[WHERE conditions]

Note:-
  • The number and the order of the columns must be the same in both queries.
  • The data types must be the same and compatible.

Sample Database Table - EmpInfo

ID EmpName Gender Age City
1 Harish Karthik Male 31 Hyderbhad
2 Nirmala Female 21 Delhi
5 Geetha Female 30 Chennai
6 Chandra Female 29 Madurai

Sample Database Table - EmpInfo2

ID EmpName Gender Age City
1 Harish Karthik Male 31 Hyderbhad
2 Nirmala Female 21 Delhi
3 Keshavan Male 21 Pune
4 Ranjani Mai Female 33 Mysore

SQL Except | Minus Operator Example

The following SQL statement will display records from "EmpInfo" tables and It collects unique records from "EmpInfo" table which are not available in the "EmpInfo2" table.


SELECT * FROM EmpInfo
EXCEPT
SELECT * FROM EmpInfo2

The result of above query is:

ID EmpName Gender Age City
5 Geetha Female 30 Chennai
6 Chandra Female 29 Madurai

Another SQL statement with where condition.


SELECT ID, EmpName, Age FROM EmpInfo
WHERE ID < 4

EXCEPT

SELECT ID, Empname, Age FROM EmpInfo2 
WHERE Gender='Male'

The first select statement will returns rows(1 and 2) and the second select statement will returns rows(1 and 3).

The main result will come from only first select statement with unique rows after compare to second result set.

The result of above query is:

ID EmpName Age
2 Nirmala 21

Search Keys

  • sql except multiple tables
  • sql except mariadb
  • sql except ms access
  • sql except msdn
  • sql merge except
  • sql server except minus
  • sql except vs merge
  • sql except join
  • sql except join example
  • sql join except null
  • sql except vs join
  • sql except inner join
  • t sql except join
  • sql except with join
  • sql except with multiple columns
  • sql except with nulls
  • sql except with order by
  • sql except where examples
  • sql except with group by