CODEWITHSUNDEEP
sqlamazon-redshiftouter-join
Sidhant Gupta
Sidhant Gupta

Reputation: 149

full outer join in redshift

I have 2 tables A and B with columns, containing some details of students (all columns are integer):
A:
st_id,
st_subject_id,

B:
st_id,
st_subject_id,
st_count1,
st_count2

st_id means student id, st_subject_id is subject id.
For student id 15, there are following entries:
A:
15 | 1
15 | 2
15 | 3

B:
15 | 1 | 31 | 11
15 | 2 | 30 | 14
15 | 4 | 21 | 6
15 | 5 | 26 | 9

3 subjects in table A and 4 subjects(2 matching with table A and 2 extra) in table B.

I want to display the final result as:
15 | 1 | 31 | 11
15 | 2 | 30 | 14
15 | 3 | null | null
15 | 4 | 21 | 6
15 | 5 | 26 | 9

Can this be done using full outer join in SQL, or by another method?

Upvotes: 0

Views: 1849

Answers (1)

Bashton
Bashton

Reputation: 339

I think something like this would suffice, but I can't test right now. Coalesce means that the first non-null value will be selected from both tables.

select
coalesce(A.st_id, B.st_id) st_id,
coalesce(A.st_subject_id, B.st_subject_id) st_subject_id,
B.st_count1,
B.st_count2

from A

full outer join B
on A.st_id = B.st_id and A.st_subject_id = B.st_subject_id

Upvotes: 1

Related Questions