SQL NOT IN转LEFT JOIN

[暗夜之王]

有查询如下

IF OBJECT_ID('DBO.STUDENT') IS NOT NULL 
	 DROP TABLE DOB.STUDENT
GO	
CREATE TABLE STUDENT (
	S_ID INT,
	S_NAME NVARCHAR(20),
	S_CLASS NVARCHAR(20)
	)
GO
IF OBJECT_ID('DBO.EXAM') IS NOT NULL 
	 DROP TABLE DOB.EXAM
GO	
CREATE TABLE EXAM (
	S_ID INT,
	S_CLASS NVARCHAR(20),
	S_TYPE NVARCHAR(10)
	)
GO

INSERT INTO STUDENT SELECT 1001,'ZHANG','A' UNION ALL
					 SELECT 1001,'ZHANG','B' UNION ALL
					 SELECT 1001,'ZHANG','C'
					
INSERT INTO EXAM SELECT 1001,'A','Y' UNION ALL
				 SELECT 1001,'A','N' UNION ALL
				 SELECT 1001,'B','N' UNION ALL
				 SELECT 1001,'B','N'
GO 

SELECT * FROM STUDENT WHERE CONVERT(NVARCHAR(5),S_ID)+S_CLASS NOT IN 
	(SELECT CONVERT(NVARCHAR(5),S_ID)+S_CLASS FROM EXAM WHERE S_TYPE='Y' )

请问用左连接该如何写？

[xdashewan]

SELECT b.* FROM STUDENT b 
left join  (SELECT S_ID FROM EXAM WHERE S_TYPE='Y' ) a 
       on CONVERT(NVARCHAR(5),b.S_ID)+b.S_CLASS = CONVERT(NVARCHAR(5),a.S_ID)+a.S_CLASS
where a.S_ID is null

[reenjie]

 select b.* from (select * from  EXAM where S_TYPE='Y') as a left join STUDENT as b 
    on a.S_ID=b.S_ID and a.S_CLASS!=b.S_CLASS where b.S_CLASS is not null

[还在加载中灬]

考虑到EXAM可能重复，我觉得需要去重，

SELECT T1.* FROM STUDENT T1
	LEFT JOIN(SELECT S_ID,S_CLASS,S_TYPE FROM EXAM GROUP BY S_ID,S_CLASS,S_TYPE) T2
		ON T1.S_ID=T2.S_ID AND T1.S_CLASS=T2.S_CLASS AND T2.S_TYPE='Y'
WHERE T2.S_ID IS NULL

[暗夜之王]

引用 1 楼 xdashewan 的回复:

SELECT b.* FROM STUDENT b 
left join  (SELECT S_ID FROM EXAM WHERE S_TYPE='Y' ) a 
       on CONVERT(NVARCHAR(5),b.S_ID)+b.S_CLASS = CONVERT(NVARCHAR(5),a.S_ID)+a.S_CLASS
where a.S_ID is null

这位大哥少了个S_CLASS