Combine 2 different tables data using select query

Question

I am using postgresql 9.3. I have 2 tables like following:

create table table1 (col1 int, col2 int);
create table table2 (col2 int, col4 int);

insert into table1 values
(1,2),(3,4);

insert into table2 values
(10,11),(30,40),(50,60);

My expected resultset is as follow:

COL1    table1_COL2 table2_COL2 COL4
1              2                10           11
3              4                30           40
  (null)    (null)              50           60

I have tried to use with, join but not getting the expected result. I am not intending to join these two tables. Only I want the results should come in one resultset so that I don't need to query in database for 2 times.

What, if you have thought about it, is your joining condition? — Rachcha
– Rachcha, Commented Dec 16, 2013 at 4:52
The best way to do this is -- not to do it. It's a bad idea. You have two separate and unrelated queries, you should just run one and then the other. (If you really feel strongly about running them simultaneously and fake-combining the results, you can use subqueries with row_number() and then FULL OUTER JOIN on that. So you can. But you shouldn't.) — ruakh
– ruakh, Commented Dec 16, 2013 at 5:16
@ruakh your comment helped a lot. Can you please add your comment as answer so that I can accept it. — ray
– ray, Commented Dec 16, 2013 at 5:50

ruakh · Accepted Answer · 2013-12-16 06:03:45Z

1

[moved from a comment to an answer, at OP's request]

The best way to do this is — not to do it. It's a bad idea. You have two separate and unrelated queries, you should just run one and then the other. (If you really feel strongly about running them simultaneously and fake-combining the results, you can use subqueries with row_number() and then FULL OUTER JOIN on that. So you can. But you shouldn't.)

answered Dec 16, 2013 at 6:03

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steven Wexler · Accepted Answer · 2013-12-16 05:15:55Z

If you're purely going for optimization by not making multiple round trips to the database, you're probably better off with results with the structure shown below. This structure and query better illustrates that you are really making two different queries than your expected result structure and the necessary query would illustrate.

COL1    TABLE1_COL2    TABLE2_COL2      COL4
1            2            (null)       (null)
3            4            (null)       (null)
(null)     (null)           10           11
(null)     (null)           30           40
(null)     (null)           50           60

SELECT col1
     , col2 AS table1_COL2
     , NULL AS table2_COL2
     , NULL AS col4
FROM table1
UNION ALL
SELECT NULL AS col1
     , NULL AS table1_COL2
     , col2 AS table2_COL2
     , col4 AS col4
FROM table2

SQL Fiddle

Elvin · Accepted Answer · 2013-12-16 06:22:23Z

0

As if you have not provided any join condition you can do like this.

SELECT table1.col1 AS table1_COL1
    , table1.col2 AS table1_COL2
   , table2.col1 AS table2_COL3
   , table2.col2 AS table2.col4
FROM table1,table2

answered Dec 16, 2013 at 6:22

Elvin

2,3118 gold badges25 silver badges26 bronze badges

Collectives™ on Stack Overflow

Combine 2 different tables data using select query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related