EXISTS

IF OBJECT_ID('tempdb..#Students') IS NOT NULL
	BEGIN
		DROP TABLE #Students;
	END
CREATE TABLE #Students
(
	[Id] BIGINT NOT NULL,
	[Name] VARCHAR(MAX) NOT NULL,
	[Gender] VARCHAR(MAX) NULL,
)
INSERT INTO #Students VALUES(1,'Lucio','man')
INSERT INTO #Students VALUES(2,'Lisa','woman')
INSERT INTO #Students VALUES(3,'Michael','man')
INSERT INTO #Students VALUES(4,'Vic','man')
INSERT INTO #Students VALUES(5,'June','woman')
INSERT INTO #Students VALUES(6,'Lillian','woman')
INSERT INTO #Students VALUES(7,'Jane','woman')
INSERT INTO #Students VALUES(8,'Walter','man')
INSERT INTO #Students VALUES(9,'Jesse','man')

SELECT * FROM #Students

Id	Name	Gender
------- ------- --------
1	Lucio	man
2	Lisa	woman
3	Michael	man
4	Vic	man
5	June	woman
6	Lillian	woman
7	Jane	woman
8	Walter	man
9	Jesse	man

IF OBJECT_ID('tempdb..#Scores') IS NOT NULL
	BEGIN
		DROP TABLE #Scores;
	END
CREATE TABLE #Scores
(
	[StudentId] BIGINT NOT NULL,
	[Score] INT NOT NULL,
)
INSERT INTO #Scores VALUES(1,100)
INSERT INTO #Scores VALUES(2,60)
INSERT INTO #Scores VALUES(3,80)
INSERT INTO #Scores VALUES(4,80)
INSERT INTO #Scores VALUES(5,90)
INSERT INTO #Scores VALUES(6,80)
INSERT INTO #Scores VALUES(7,60)
INSERT INTO #Scores VALUES(8,80)
INSERT INTO #Scores VALUES(9,70)

SELECT * FROM #Scores

StudentId	Score
--------------- --------
1	        100
2	        60
3	        80
4	        80
5	        90
6	        80
7	        60
8	        80
9	        70

使用 IN 和 EXISTS

SELECT * FROM #Students WHERE Id IN (SELECT StudentId FROM #Scores WHERE Score >= 90)
SELECT * FROM #Students A WHERE EXISTS (SELECT * FROM #Scores B WHERE Score >= 90 AND A.Id = B.StudentId)
SELECT * FROM #Students A WHERE Id = ANY (SELECT StudentId FROM #Scores WHERE Score >= 90)

Id	Name	Gender
------- ------- -------
1	Lucio	man
5	June	woman