select * from ( select * from a where not exists(select * from b MM=a.MM)) left join b on a.id=b.id
A表:ID MM 1 1 2 22 3 4 B表:ID MM 1 2 22 -- select a.id,a.mm ,b.id ,b.mm from ta a left join tb b on a.id = b.id where b.mm is null
SELECT * FROM (SELECT * FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE A.ID=B.ID AND A.MM=B.MM) AS T INNER JOIN (SELECT * FROM B WHERE NOT EXISTS(SELECT 1 FROM A WHERE A.ID=B.ID AND A.MM=B.MM) AS T1 ON T.ID=T1.ID
--> 测试数据: @A declare @A table (ID int,MM int) insert into @A select 1,1 union all select 2,22 union all select 3,4 --> 测试数据: @B declare @B table (ID int,MM int) insert into @B select 1,null union all select 2,22select * from @A a left join @b b on a.id=b.id where a.id!=b.id or a.mm!=isnull(b.mm,'')
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('A') IS NOT NULL DROP TABLE A GO CREATE TABLE A(ID int,mm int) go insert A SELECT 1 , 1 UNION ALL SELECT 2 , 22 UNION ALL SELECT 3 , 4 IF OBJECT_ID('B') IS NOT NULL DROP TABLE B GO CREATE TABLE B(ID int,mm int) go insert B SELECT 1 ,NULL UNION ALL SELECT 2 , 22 GO select * from ( select * from a where not exists(select * from b WHERE MM=a.MM)) K left join b on K.id=b.id go /*-ID mm ID mm ----------- ----------- ----------- ----------- 1 1 1 NULL 3 4 NULL NULL------------------*/
--------------------------------- -- Author: htl258(Tony) -- Date : 2009-07-31 17:00:06 --------------------------------- --> 生成测试数据表:tbIf not object_id('[a]') is null Drop table [a] Go Create table [a]([ID] int,[MM] int) Insert a Select 1,1 union all Select 2,22 union all Select 3,4 Go --Select * from tb--> 生成测试数据表:bIf not object_id('[b]') is null Drop table [b] Go Create table [b]([ID] int,[MM] int) Insert b Select 1,null union all Select 2,22 Go --Select * from b-->SQL查询如下: select * from (select * from a where not exists(select 1 from b where id=a.id and mm=a.mm)) a left join (select * from b where not exists(select 1 from a where id=b.id and mm=b.mm)) b on a.id=b.id /* ID MM ID MM ----------- ----------- ----------- ----------- 1 1 1 NULL 3 4 NULL NULL(2 行受影响) */
(
select * from a where not exists(select * from b MM=a.MM)) left join b
on a.id=b.id
1 1
2 22
3 4 B表:ID MM
1
2 22 --
select a.id,a.mm ,b.id ,b.mm
from ta a
left join tb b on a.id = b.id
where b.mm is null
*
FROM
(SELECT * FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE A.ID=B.ID AND A.MM=B.MM) AS T
INNER JOIN
(SELECT * FROM B WHERE NOT EXISTS(SELECT 1 FROM A WHERE A.ID=B.ID AND A.MM=B.MM) AS T1
ON T.ID=T1.ID
declare @A table (ID int,MM int)
insert into @A
select 1,1 union all
select 2,22 union all
select 3,4
--> 测试数据: @B
declare @B table (ID int,MM int)
insert into @B
select 1,null union all
select 2,22select * from @A a left join @b b
on a.id=b.id
where a.id!=b.id or a.mm!=isnull(b.mm,'')
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GO
CREATE TABLE A(ID int,mm int)
go
insert A SELECT 1 , 1 UNION ALL SELECT
2 , 22 UNION ALL SELECT
3 , 4
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
GO
CREATE TABLE B(ID int,mm int)
go
insert B SELECT
1 ,NULL UNION ALL SELECT
2 , 22
GO
select * from
(
select * from a where not exists(select * from b WHERE MM=a.MM)) K left join b
on K.id=b.id
go
/*-ID mm ID mm
----------- ----------- ----------- -----------
1 1 1 NULL
3 4 NULL NULL------------------*/
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-31 17:00:06
---------------------------------
--> 生成测试数据表:tbIf not object_id('[a]') is null
Drop table [a]
Go
Create table [a]([ID] int,[MM] int)
Insert a
Select 1,1 union all
Select 2,22 union all
Select 3,4
Go
--Select * from tb--> 生成测试数据表:bIf not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([ID] int,[MM] int)
Insert b
Select 1,null union all
Select 2,22
Go
--Select * from b-->SQL查询如下:
select *
from (select * from a where not exists(select 1 from b where id=a.id and mm=a.mm)) a
left join (select * from b where not exists(select 1 from a where id=b.id and mm=b.mm)) b
on a.id=b.id
/*
ID MM ID MM
----------- ----------- ----------- -----------
1 1 1 NULL
3 4 NULL NULL(2 行受影响)
*/