Simple Aggregation/ joining of tables

Question

I have two tables say, A and B of account_numbers. B being a subset of A. How do I join the two tables such that I add an additional field in the output table with the common elements (here account_numbers) being flagged as 1 and rest 0

Table A
Account_Number
11
13
15
16
17
20

Table B
Account_Number
13
16
20

Output Table    
Account Flag
11  0
13  1
15  0
16  1
17  0
20  1

Have you tried anything or are you expecting others to write code for you? — Aleks G, Jul 23 '13 at 23:31
possible duplicate of [Difference between inner and outer join](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join) — Klas Lindbäck, Jul 23 '13 at 23:34
Aleks G, I am pretty much new to SQL. Hence the ask. When I did a left join to have all the elements included according to the requirements of the output table, I am not sure how to add a field where I can flag the common elements. — SouravD, Jul 23 '13 at 23:38

score 3 · Answer 1 · answered Jul 23 '13 at 23:56

3

I think the best way to approach this is with a left outer join:

select a.account_number,
       (case when b.account_number is null then 0 else 1 end) as flag
from A left outer join
     B
     on A.account_number = b.account_number;

answered Jul 23 '13 at 23:56

Gordon Linoff

1,242,037
58
646
786

Gordon this too works fine. Helped in clarifying some doubts. Thank you and Yang both for your help. – SouravD Jul 24 '13 at 00:00
@Yang's solution is quite clever. On the other hand, this will generally make better use of indexes, be faster, retains duplicates in table A, and allows you to easily include more columns from with `A` or `B`. – Gordon Linoff Jul 24 '13 at 00:03
Yes, I too had this question; if there were more columns probably count(*)-1 won't work. But then Yang's was extremely clever and was precise to my query. Thanks again @Gordon – SouravD Jul 24 '13 at 00:10

score 2 · Answer 2 · answered Jul 24 '13 at 00:02

2

Try an outer join instead of group by and union all, it will perform much bettter, particularly with large tables.

select a.account_number
  , case 
      when b.account_number is not null then 1
      else 0
    end as flag
from a, b
where a.account_number *= b.account_number

answered Jul 24 '13 at 00:02

jmarkmurphy

11,030
31
59

Yes, Gordon too gave this solution. Valuable tips on optimisation. Thanks. – SouravD Jul 24 '13 at 00:11

score 1 · Accepted Answer · answered Jul 23 '13 at 23:47

1

SELECT account_number, COUNT(*)-1 AS flag
FROM (
  SELECT account_number FROM A
    UNION ALL
  SELECT account_number FROM B
) AB
GROUP BY account_number;

Checkout this demo. Let me know if it works.

answered Jul 23 '13 at 23:47

Yang

7,712
9
48
65

Thank you so much for this. Exactly how I wanted the table to look like. – SouravD Jul 23 '13 at 23:55

Simple Aggregation/ joining of tables

3 Answers3