Two tables filter the same data and different data

Source: Internet
Author: User

The project often compares data from two database tables to select the same data or different data. In SQL Server 2000, only exists can be used to determine that, after SQL Server 2005, the data of two tables could be compared using the except and intersect operators.

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.

For example, there are tables A and B, and their tables and data scripts are as follows:

If object_id (' [A] ') is not null drop table [a]
go 
CREATE TABLE [a] ([tel_no] bigint,[cost] int)
Insert [a
] Select 13800000000,38 UNION ALL
Select 13823400000,56 UNION ALL SELECT
13800056400,88 UNION ALL
Select 13800230000,28 UNION ALL
Select 13802300000,18 UNION ALL
Select 13822220000,68 UNION ALL
Select 13844400000,98 UNION ALL
Select 13833330000,35 UNION ALL
Select 13822220000,31 UNION ALL
Select 13811110000,32
--> test data: [b]
if object_id (' [b] ') is not null drop table [b]
go 
CREATE TABLE [b] ([ TEL_NO] bigint
Insert [b]
select 13800000000 UNION ALL
Select 13823400000 UNION ALL
Select 13800051230 UNION ALL
Select 13800230123
Now you have to figure out two tables with the same data and two different data, if you have more than SQL SERVER 2005:

--Same Data Select
tel_no from  
a
intersect
select tel_no from 
b

--Different data
select Tel_no From  
b
except
select Tel_no 
A

If you are SQL SERVER 2000

SELECT * from B where EXISTS (select 1 to a where tel_no=b.tel_no)
 
select * from B where not EXISTS (select 1 from a W Here Tel_no=b.tel_no)

The results of both methods are the same:


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.