CREATE TABLE #student_attendance (student_attendance_id INT IDENTITY(1,1), student_id INT, school_ID INT);

INSERT INTO #student_attendance(student_id, school_ID)
SELECT student_id, school_ID
FROM (VALUES(1,2),(1,3),(2,3),(3,4),(4,5))a(student_id, school_ID);

DECLARE @user_input INT = 3;

SELECT final_result_set.student_id, final_result_set.school_ID
FROM (SELECT student_id, school_ID
      FROM #student_attendance
      WHERE school_ID = @user_input) school_filter(student_id, school_ID)
OUTER APPLY (SELECT student_id, school_ID
             FROM #student_attendance
             WHERE student_id = school_filter.student_id 
             AND school_ID <> school_filter.school_ID) other_schools(student_id, school_ID)
OUTER APPLY (SELECT school_filter.student_id, school_filter.school_ID
             UNION SELECT other_schools.student_id, other_schools.school_ID
             WHERE other_schools.student_id IS NOT NULL) final_result_set;

student_id  school_ID
----------- -----------
1           2
1           3
2           3

SELECT final_result_set.student_id, final_result_set.school_ID
FROM (SELECT student_id, school_ID
      FROM #student_attendance
      WHERE school_ID = @user_input) school_filter(student_id, school_ID)
OUTER APPLY (SELECT student_id, school_ID
             FROM #student_attendance
             WHERE student_id = school_filter.student_id 
             AND school_ID <> school_filter.school_ID) other_schools(student_id, school_ID)
OUTER APPLY (SELECT school_filter.student_id, school_filter.school_ID
             UNION SELECT other_schools.student_id, other_schools.school_ID
             WHERE other_schools.student_id IS NOT NULL) final_result_set;

SELECT student_id, school_ID
FROM #student_attendance
WHERE student_id IN (SELECT student_id
                     FROM #student_attendance
                     WHERE school_ID = @user_input
                     );

=================================
OUTER APPLY
=================================

Table '#student_attendance'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

=================================
IN SUBQUERY
=================================

Table '#student_attendance'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

IF object_id('tempdb..#student_attendance') IS NOT NULL
BEGIN
    DROP TABLE #student_attendance;
END;

SELECT TOP 1000000 IDENTITY(INT,1,1) AS student_attendance_id, 
(ABS(CHECKSUM(NEWID())) % 500000) + 1 AS student_id,
(ABS(CHECKSUM(NEWID())) % 50000) + 1 AS school_ID
INTO #student_attendance
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

=================================
OUTER APPLY
=================================
(70 row(s) affected)
Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 186 ms,  elapsed time = 46 ms.

=================================
IN SUBQUERY
=================================
(50 row(s) affected)
Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 34 ms.

SELECT DISTINCT final_result_set.student_id, final_result_set.school_ID
FROM (SELECT student_id, school_ID
      FROM #student_attendance
      WHERE school_ID = @user_input) school_filter(student_id, school_ID)
OUTER APPLY (SELECT student_id, school_ID
             FROM #student_attendance
             WHERE student_id = school_filter.student_id 
             AND school_ID <> school_filter.school_ID) other_schools(student_id, school_ID)
OUTER APPLY (SELECT school_filter.student_id, school_filter.school_ID
             UNION SELECT other_schools.student_id, other_schools.school_ID
             WHERE other_schools.student_id IS NOT NULL) final_result_set;

=================================
OUTER APPLY
=================================
(40 row(s) affected)
Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 140 ms,  elapsed time = 36 ms.

=================================
IN SUBQUERY
=================================
(40 row(s) affected)
Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 173 ms,  elapsed time = 35 ms.