Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We can use the SQL EXCEPT Operator between two SQL Statement. It allows us to return records that exist in the first SQL Statement, but not in the second SQL Statement. This allows TSQL code developers to simplify their work in some cases.
In the two SQL Statement, where the EXCEPT operator is used, matching columns must have the same data type. For example, if the first column of the first Select Statement is int, the first column of the second SQL Statement must be int.
In fact, even if the data type is not the same, we can run the query without any errors between the data types that are compatible with each other. For example, varchar and nchar are compatible data types. or int and small int.
Example:
First, we create two tables and we add a few records to these tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | USE [TestDB] GO CREATE TABLE [dbo].[MyTable1]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[MyTable2]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[MyTable1] VALUES ('Nurullah CAKIR'),('Faruk ERDEM') INSERT INTO [dbo].[MyTable2] VALUES ('Nurullah CAKIR'),('Faruk ER'),('Hakan GURBASLAR') |
Then, using the EXCEPT operator, we find the records in the first table but not in the second table..
1 2 3 | SELECT Name FROM [dbo].[MyTable1] EXCEPT SELECT Name FROM [dbo].[MyTable2] |
If the column numbers in the two SQL Statement are different, you will get an error as follows.
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Let’s detail our example a little more. Let’s add a column named typeofint to both tables by running the following script. One of the column’s type is varchar (50), the other column’s type is int. And let’s update two tables as follows.
1 2 3 4 5 6 7 8 9 | ALTER TABLE MyTable1 ADD [typeofint] varchar(50) NULL ALTER TABLE MyTable2 ADD [typeofint] int NULL UPDATE MyTable1 SET [typeofint]='A' WHERE ID=1 UPDATE MyTable1 SET [typeofint]='B' WHERE ID=2 UPDATE MyTable2 SET [typeofint]=1 WHERE ID=1 UPDATE MyTable2 SET [typeofint]=2 WHERE ID=2 UPDATE MyTable2 SET [typeofint]=3 WHERE ID=3 |
Rewrite the EXCEPT query as follows:
1 2 3 | SELECT typeofint FROM [dbo].[MyTable1] EXCEPT SELECT typeofint FROM [dbo].[MyTable2] |
Although the names of the two columns are the same, we will get the error as follows because the data types are different.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘A’ to data type int.
This time we will add two columns of the same data type with different names and update these columns with some data.
1 2 3 4 5 6 7 8 9 | ALTER TABLE MyTable1 ADD [testcolumn1] varchar(50) NULL ALTER TABLE MyTable2 ADD [testcolumn2] varchar(50) NULL UPDATE MyTable1 SET [testcolumn1]='A' WHERE ID=1 UPDATE MyTable1 SET [testcolumn1]='B' WHERE ID=2 UPDATE MyTable2 SET [testcolumn2]='A' WHERE ID=1 UPDATE MyTable2 SET [testcolumn2]='C' WHERE ID=2 UPDATE MyTable2 SET [testcolumn2]='D' WHERE ID=3 |
Rewrite the EXCEPT query as follows:
1 2 3 | SELECT testcolumn1 FROM [dbo].[MyTable1] EXCEPT SELECT testcolumn2 FROM [dbo].[MyTable2] |
As you can see, although the names are different, we did not receive any errors because the data types are the same.
