两个表JOIN，排除已经存在的组合

Question

SELECT *
FROM A
  CROSS JOIN B
WHERE NOT EXISTS
      (
       SELECT *
       FROM C
       WHERE A.id=col1
          AND B.vb=col2
      )

Answer 1

--> liangCK小梁 于2008-10-22
 
--> 生成测试数据: @A
DECLARE @A TABLE (id INT)
INSERT INTO @A
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
 
--> 生成测试数据: @B
DECLARE @B TABLE (vb VARCHAR(1))
INSERT INTO @B
SELECT 'x' UNION ALL
SELECT 'y' UNION ALL
SELECT 'z'
 
--> 生成测试数据: @C
DECLARE @C TABLE (col1 INT,col2 VARCHAR(1))
INSERT INTO @C
SELECT 1,'x' UNION ALL
SELECT 2,'y'--SQL查询如下:SELECT * 
FROM @A AS A
  CROSS JOIN @B AS B
WHERE NOT EXISTS 
      ( 
      SELECT * 
      FROM @C 
      WHERE A.id=col1 
          AND B.vb=col2 
      )/*
id          vb
----------- ----
2           x
3           x
1           y
3           y
1           z
2           z
3           z(7 行受影响)
*/

Answer 2

DECLARE @a TABLE(id INT)
-----
INSERT @a SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
DECLARE @b TABLE(vb VARCHAR)
-----
INSERT @b SELECT 'x' UNION SELECT 'y' UNION SELECT 'z'
DECLARE @c TABLE(c1 INT,c2 VARCHAR)
INSERT @c SELECT 1 ,   'x'
UNION ALL SELECT 2,    'y' 
SELECT a.*,b.* FROM @a a,@b b
WHERE NOT EXISTS(SELECT 1 FROM @c WHERE c1=id AND c2=vb)

Answer 3

------------------------------------
-- Author:  happyflsytone  
-- Date:2008-10-22 15:56:02
-------------------------------------- Test Data: sblx
IF OBJECT_ID('ta') IS NOT NULL 
    DROP TABLE ta
Go
CREATE TABLE ta(id  INT)
Go
INSERT INTO ta
 SELECT 1 UNION ALL
 SELECT 2 UNION ALL
 SELECT 3 
GO
-- Test Data: sblx
IF OBJECT_ID('tb') IS NOT NULL 
    DROP TABLE tb
Go
CREATE TABLE tb(vb  char(2))
Go
INSERT INTO tb
 SELECT 'x' UNION ALL
 SELECT 'y' UNION ALL
 SELECT 'z' 
GO
-- Test Data: sblx
IF OBJECT_ID('tc') IS NOT NULL 
    DROP TABLE tc
Go
CREATE TABLE tc(col1 int,col2  char(2))
Go
INSERT INTO tc
 SELECT 1,'x' UNION ALL
 SELECT 2,'y'
GO
--Start
select *
from ta a cross join tb b 
where not exists(select 1 from tc where col1 = a.id and col2 = b.vb)
--Result:
/*
id          vb
----------- ----
2           x 
3           x 
1           y 
3           y 
1           z 
2           z 
3           z (7 行受影响)*/
--End 

Answer 4

set nocount on
declare @a table(id int) 
----- 
insert @a select 1 
insert @a select 2 
insert @a select 3  
declare @b table(vb varchar(10))
insert @b select 'x' 
insert @b select 'y' 
insert @b select 'z'
declare @c table(col1 varchar(10), col2 varchar(10)) 
insert @c select 1,    'x' 
insert @c select 2,    'y' 
select * from (select * from @a cross join  @b) b where not exists(select 1 from @c where col1=b.id and col2=b.vb)
/*
id          vb         
----------- ---------- 
2           x
3           x
1           y
3           y
1           z
2           z
3           z*/