SQL Problem: Item falls in which group boolean result

Scenario
Consider scenario where students data is present in two tables

1) Students in A group have entry in table A_group
  student_id
101
102
103
108

2)Students in B group have entry in table B_group

  student_id
101
103
109
111
Condition : Student can belong to either or both groups.

Requirement

Result is required in the form

student_id    A_group_flag  B_group_flag

101                      1               1
102                      1               0
103                      1               1
108                      1               0
109                      0               1
111                      0               1

which shows student_id and Boolean values indicating if the student is present in that group or not.

*flag value 1 indicates student is present in that group

*flag value 0 indicates student is not present in that group

Solution

Here is one of possible solution that I thought of on first shot

Step 1 :

First we combine results from both tables without filtering any

SELECT DISTINCT student_id, 1 AS flag
FROM   a_group
UNION ALL
SELECT DISTINCT student_id, 4 AS flag
FROM   b_group

This query will give all the student ids from both tables with a flag indicating student's group

*flag value 1 indicates A group
flag value 4 indicates B group

Step 2 :
Now we have data of the form

student_id   flag

101             1

102             1

103             1

108             1

101             4
103             4
109             4
111             4

Now comes the logic part
If we group data by student_id and perform aggregate sum on flag like

SELECT student_id,
SUM(flag) AS newflag
FROM   (SELECT DISTINCT student_id,  1 AS flag
FROM   a_group
UNION ALL
SELECT DISTINCT student_id, 4 AS flag
FROM   b_group)
GROUP BY student_id

We will get following values of new flag

1  for students present in A group
4  for students present in B group
5 for student present in both groups

Result of above query would be of form

student_id   newflag
101                5
102                1
103                5
108                1
109                4
111                4

Step 3 :
Next we need to use 'IF conditionals'  to show required data with query like

SELECT student_id,
( CASE
WHEN newflag = 1
OR newflag = 5 THEN 1
ELSE 0
END ) a_group_flag,
( CASE
WHEN newflag = 4
OR newflag = 5 THEN 1
ELSE 0
END ) b_group_flag
FROM
(SELECT student_id,
SUM(flag) AS newflag
FROM   (SELECT DISTINCT student_id,  1 AS flag
FROM   a_group
UNION ALL
SELECT DISTINCT student_id, 4 AS flag
FROM   b_group)
GROUP BY student_id )

This would give us required result of the form

student_id  A_group_flag  B_group_flag
101                 1                                  1
102                 1                                 0
103                 1                                 1
108                 1                                 0
109                 0                                 1
111                 0                                  1