Joining 2 SQL SELECT result sets into one

Question

I've got 2 select statements, returning data like this:

Select 1
col_a   col_b

Select 2
col_a   col_c

If I do union, I get something like

col_a col_b

And rows joined. What i need is getting it like this:

col_a  col_b  col_c

Joined on data in col_a

Mark Byers · Accepted Answer · 2010-02-23T11:37:46.483

67

Use JOIN to join the subqueries and use ON to say where the rows from each subquery must match:

SELECT T1.col_a, T1.col_b, T2.col_c
FROM (SELECT col_a, col_b, ...etc...) AS T1
JOIN (SELECT col_a, col_c, ...etc...) AS T2
ON T1.col_a = T2.col_a

If there are some values of col_a that are in T1 but not in T2, you can use a LEFT OUTER JOIN instead.

edited Feb 23 '10 at 11:37

answered Feb 23 '10 at 11:32

Mark Byers

1

The problem is, i have select statements, not tables. – Vance Feb 23 '10 at 11:34
@Vance: Then use a derived table and an alias, as shown in my updated answer. – Mark Byers Feb 23 '10 at 11:35
1

if your selects are long and your SQL implements it, you can use the WITH statement. With Select1 AS (select ..), Select2 AS (select ...) SELECT COALESCE(T1.col_a, T2.col_a) AS col_a, T1.col_b, T2.col_c FROM Select1 T1 FULL JOIN Select2 T2 ON T1.col_a = T2.col_a – sergiom Feb 23 '10 at 11:44
@MarkByers your query works fine for smaller datasets. In my case T1 has 100,000 records & T2 has another 100,000 records. I wanted to use right outer join to get records in T2 which are not in T1. Can the query be optimized to fetch large number of records ? – Vinay Potluri Aug 22 '19 at 23:05

score 5 · Answer 2 · edited Feb 23 '17 at 13:49

5

Use a FULL OUTER JOIN:

select 
   a.col_a,
   a.col_b,
   b.col_c
from
   (select col_a,col_bfrom tab1) a
join 
   (select col_a,col_cfrom tab2) b 
on a.col_a= b.col_a

edited Feb 23 '17 at 13:49

CMedina

answered Feb 23 '10 at 11:37

p2u

You probably need something like `COALESCE(a.col_a, b.col_a) AS col_a` in the projection list. – araqnid Feb 23 '10 at 11:41

score 2 · Answer 3 · answered Feb 23 '10 at 11:33

2

SELECT table1.col_a, table1.col_b, table2.col_c 
  FROM table1 
  INNER JOIN table2 ON table1.col_a = table2.col_a

answered Feb 23 '10 at 11:33

Håvard S

Or use `LEFT JOIN` or `RIGHT JOIN`, if there are rows in Tables on the left or right side of `table1.col_a = table2.col_a` – rubo77 Jul 15 '13 at 06:30

3 Answers3