Curious: the winner is?

"EXISTS" with absolute less reads.

SET STATISTICS IO ON

GO

SELECT DISTINCT x AS 'EXISTS'

FROM A

WHERE exists(SELECT * FROM B WHERE Y=x )

GO

SELECT x AS 'Intersecting'

FROM A

INTERSECT

SELECT Y

FROM B

go

SELECT DISTINCT x AS 'JOIN'

FROM A

JOIN B

ON Y=x

go

Result:

EXISTS

1

2

20

(3 row(s) affected)

Tabella 'B'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Tabella 'A'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Intersecting

1

2

20

(3 row(s) affected)

Tabella 'B'. Conteggio analisi 1, letture logiche 5, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Tabella 'A'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

JOIN

1

2

20

(3 row(s) affected)

Tabella 'Worktable'. Conteggio analisi 3, letture logiche 160, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Tabella 'B'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Tabella 'A'. Conteggio analisi 1, letture logiche 1, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.