查询两个表中某几个字段的值不等或相等

[zzxiaoma]

表1
id,name,mid,pid，ww
1 aa 1 1 3
2 bb 2 1 4
3 cc 2 2 4
表2
id,name,ee,mid,pid,gg
1 aa 3 1 2 3
2 cc 2 2 2 3
3 b 2 2 2 2
4 bb 2 2 1 2
表1和表2没有设置关联关系，现在要求如果选出表1里面的name，mid，pid只要有一个和表2的不一样
结果就是
1 aa 1 1 3
3 cc 2 2 4

选出表1里面的name，mid，pid和表2完全一样的
2 bb 2 1 4

[ACMAIN_CHM]

select * from 表1
where not exists (
	select 1 from 表2 where name=表1.name and mid=表1.mid and pid=表1.pid
)

[CatNetMouse]

CREATE TABLE DB2INST2.CAT_CMPCOLMN_A
 (ID    BIGINT          NOT NULL  GENERATED BY DEFAULT
    AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, NO CYCLE, NO ORDER),
  NAME  VARCHAR(100),
  MID   INTEGER,
  PID   INTEGER,
  WW    INTEGER
 )
  DATA CAPTURE NONE;


  
CREATE TABLE DB2INST2.CAT_CMPCOLMN_B
 (ID    BIGINT          NOT NULL  GENERATED BY DEFAULT
    AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, NO CYCLE, NO ORDER),
  NAME  VARCHAR(100),
  MID   INTEGER,
  PID   INTEGER,
  EE    INTEGER,
  GG    INTEGER
 )
  DATA CAPTURE NONE;  
  

INSERT INTO DB2INST2.CAT_CMPCOLMN_A(ID,NAME,MID,PID,WW) VALUES
                                                            (1, 'AA', 1, 1, 3),
															(2, 'BB', 2, 1, 4),
															(3, 'CC', 2, 2, 4);

INSERT INTO DB2INST2.CAT_CMPCOLMN_B(ID,NAME,EE,MID,PID,GG) VALUES
                                                             (1, 'AA', 3, 1, 2, 3),
               										         (2, 'CC', 2, 2, 2, 3),	 
                                                             (3, 'B' , 2, 2, 2, 2),
                                                             (4, 'BB', 2, 2, 1, 2);



--相等的
---方法一
SELECT * FROM CAT_CMPCOLMN_A A
WHERE  EXISTS ( SELECT 1 FROM CAT_CMPCOLMN_B B WHERE NAME=A.NAME AND MID=A.MID AND PID=A.PID);
---方法二
SELECT * FROM CAT_CMPCOLMN_A A ,  CAT_CMPCOLMN_B  B 
 WHERE A.MID=B.MID AND A.PID=B.PID AND A.NAME=B.NAME  ;
                             
        
--不等的		
---方法一
SELECT * FROM CAT_CMPCOLMN_A A
WHERE NOT EXISTS (SELECT 1 FROM CAT_CMPCOLMN_B B WHERE NAME=A.NAME AND MID=A.MID AND PID=A.PID);
---方法二                                  
SELECT * FROM CAT_CMPCOLMN_A WHERE ID NOT IN (
	   	 	  				 	   		        SELECT A.ID FROM CAT_CMPCOLMN_A A ,  CAT_CMPCOLMN_B B
													   WHERE  A.MID=B.MID AND A.PID=B.PID AND A.NAME=B.NAME 
											   );