The except and INTERSECT operators of SQL Server 2005

Source: Internet
Author: User

1. Introduction

The except and INTERSECT operators allow you to compare the results of two or more SELECT statements and return values that are not duplicates.

2. Differences

The except operator returns all the distinct values returned by the query on the left of the except operator, but not the value returned by the query on the right.

Intersect returns all the distinct values returned by the query on the left and right of the Intersect operator.

3. Matters needing attention

(1). Result sets that are compared using except or intersect must have the same structure. They must have the same number of columns and the data type of the corresponding result set column must be compatible

(2). The Intersect operator takes precedence over the except

(3). SELECT into must be the first query in a statement containing the Intersect or except operator to create a table that holds the final result set

(4). The column name or alias in the ORDER BY clause must refer to the column name returned by the query on the left

4. Example:

--Create 3 tables, insert data separately

create table TableA(col1 int)
insert into TableA select 1
insert into TableA select 1
insert into TableA select 2
insert into TableA select 3
insert into TableA select 4
insert into TableA select 4
insert into TableA select 5
insert into TableA select null
insert into TableA select null
create table TableB(col2 int)
insert into TableB select null
insert into TableB select 1
insert into TableB select 2
insert into TableB select 3
create table TableC(col3 int)
insert into TableC select 1
insert into TableC select 5
insert into TableC select 6

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.