SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE
  StudentTable (
    firstname NVARCHAR(32),
    lastname  NVARCHAR(32),
    emailaddress NVARCHAR(32)
  )
INSERT INTO
  StudentTable
VALUES
  ('x', 'y', 'a_b@school.edu'),
  ('a', 'b', NULL)
2 rows affected

SELECT 
  firstname + '_' + lastname + '@school.edu' AS ProposedEmailAddress,
  (
    SELECT MAX(emailaddress)
      FROM StudentTable 
     WHERE emailaddress = s.firstname + '_' + s.lastname + '@school.edu'
  )
    AS DuplicateCheck
FROM
  StudentTable  AS s


ProposedEmailAddress
DuplicateCheck


x_y@school.edu
null

a_b@school.edu
a_b@school.edu


SELECT 
  s.firstname + '_' + s.lastname + '@school.edu' AS ProposedEmailAddress,
  duplicate.*
FROM
  StudentTable  AS s
OUTER APPLY
(
  SELECT TOP 1 *
    FROM StudentTable 
   WHERE emailaddress = s.firstname + '_' + s.lastname + '@school.edu'
)
  AS duplicate


ProposedEmailAddress
firstname
lastname
emailaddress


x_y@school.edu
null
null
null

a_b@school.edu
x
y
a_b@school.edu

ProposedEmailAddress	firstname	lastname	emailaddress
x_y@school.edu	null	null	null
a_b@school.edu	x	y	a_b@school.edu