SQL Server 2017

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

CREATE TABLE table1 (
	studentid	INTEGER,
	bankaccount	INTEGER,
	gpa	INTEGER,
	semester	VARCHAR(20)
);

INSERT INTO table1 (studentid,bankaccount,gpa,semester) VALUES 
 (1,123456,NULL,'Fall'),
 (1,123456,NULL,'Spring'),
 (1,123456,NULL,'Summer'),
 (2,456789,NULL,'Fall'),
 (2,456789,NULL,'Spring'),
 (2,456789,NULL,'Summer'),
 (3,321654,NULL,'Fall'),
 (3,321654,NULL,'Spring'),
 (3,321654,NULL,'Summer');

CREATE TABLE table2 (
	studentid	INTEGER,
	bankaccount	INTEGER,
	routing	VARCHAR(20)
);

INSERT INTO table2 (studentid,bankaccount,routing) VALUES (1,123456,'abc'),
 (2,456789,'def'),
 (3,321654,'ghi');
12 rows affected

select t1.*, t2.routing 
from table1 t1 left join table2 t2
on t2.studentid = t1.studentid


studentid
bankaccount
gpa
semester
routing


1
123456
null
Fall
abc

1
123456
null
Spring
abc

1
123456
null
Summer
abc

2
456789
null
Fall
def

2
456789
null
Spring
def

2
456789
null
Summer
def

3
321654
null
Fall
ghi

3
321654
null
Spring
ghi

3
321654
null
Summer
ghi


select t1.*, t2.routing 
from table1 t1 left join table2 t2
on t2.bankaccount = t1.bankaccount


studentid
bankaccount
gpa
semester
routing


1
123456
null
Fall
abc

1
123456
null
Spring
abc

1
123456
null
Summer
abc

2
456789
null
Fall
def

2
456789
null
Spring
def

2
456789
null
Summer
def

3
321654
null
Fall
ghi

3
321654
null
Spring
ghi

3
321654
null
Summer
ghi

studentid	bankaccount	gpa	semester	routing
1	123456	null	Fall	abc
1	123456	null	Spring	abc
1	123456	null	Summer	abc
2	456789	null	Fall	def
2	456789	null	Spring	def
2	456789	null	Summer	def
3	321654	null	Fall	ghi
3	321654	null	Spring	ghi
3	321654	null	Summer	ghi