Tuesday , March 19 2024

EXCEPT Operator in SQL Server(TSQL)

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.

Then, using the EXCEPT operator, we find the records in the first table but not in the second table..

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.

Rewrite the EXCEPT query as follows:

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.

Rewrite the EXCEPT query as follows:

As you can see, although the names are different, we did not receive any errors because the data types are the same.

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories