SQL query to get rows from two tables

Question

I have two tables with ID and value columns.

I want to union these two tables but if ID exists in second table, I want to discard all same IDs in first table and retrieve only these IDs from second table. How can I create the query for this?

First table:

ID    Value
100   1
100   2
101   3
102   4

Second table:

The result I want to achieve:

I tried to do as suggested but it still returns only values from table 1:

String selectQuery = "SELECT * FROM " + TABLE1_NAME
                +" UNION ALL"
                +" SELECT * FROM " + TABLE2_NAME
                +" WHERE "+id+" NOT IN (SELECT "+id+" FROM "+TABLE2_NAME+ ")";

Tim3880 · Accepted Answer · 2015-06-24 23:57:33Z

3

try

  select id,value from table1 
  union ALL
  select id , value from table2 
  where id not in (select id from table1)

edit as suggested by Ormoz :

To use table2's result if both tables have the ids :

  select id,value from table2
  union ALL
  select id , value from table1 
  where id not in (select id from table2)

My tests:

 create table table1 (id int not null, value int not null);

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



create table table2 (id int not null, value int not null);

insert into table2 values
(100,  5),
(100,  6),
(100,  7),
(102,  5);


select id,value , 't2' as t from table2
 union ALL
 select id , value, 't1' from table1 
 where id not in (select id from table2);

this is the output:

# id, value, t
'100', '5', 't2'
'100', '6', 't2'
'100', '7', 't2'
'102', '5', 't2'
'101', '3', 't1'

edited Jun 24, 2015 at 23:57

answered Jun 24, 2015 at 19:54

Tim3880

2,5831 gold badge13 silver badges14 bronze badges

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

2 Comments

Ormoz Over a year ago

you should change table1 to table2 to get the desired result because he wants the value of the second table if the id is in both.

Figen Güngör Over a year ago

this one still returns values from table 1 and discard table 2

Jasper N. Brouwer · Accepted Answer · 2015-06-24 22:44:16Z

0

Maybe this could do the trick?

(
    SELECT id, value FROM table2
    UNION ALL
    SELECT id, value FROM table1
) GROUP BY id

answered Jun 24, 2015 at 22:44

Jasper N. Brouwer

21.9k4 gold badges55 silver badges77 bronze badges

Comments

Figen Güngör · Accepted Answer · 2015-06-24 22:44:45Z

0

My problem is solved by removing the where clause to first select:

String selectQuery = "SELECT * FROM " + TABLE1_NAME
                    +" WHERE "+id+" NOT IN (SELECT "+id+" FROM "+TABLE2_NAME)
                    +" UNION ALL"
                    +" SELECT * FROM " + TABLE2_NAME;

Special thanks to @Tim3880 and @Ormoz =)

answered Jun 24, 2015 at 22:44

Figen Güngör

12.6k14 gold badges71 silver badges114 bronze badges

Collectives™ on Stack Overflow

SQL query to get rows from two tables

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related