CODEWITHSUNDEEP
phpmysqlsqlinner-joinunion
rehman ali
rehman ali

Reputation: 15

how to fetch data from 2 tables in SQL with null Entries

My Tables and look like this

-- t1
id  col_1
1   Tim
2   Marta

-- t2
id  col_2
1   Tim
3   Katarina

I want my results like this?

--Result
id   col_1       col_2
1    Tim           Tim
2    Marta        *Null*
3   *Null*      Katarina

if someone knows how can I do this using SQL then please let me know?

Upvotes: 1

Views: 54

Answers (3)

nbk
nbk

Reputation: 49375

MySQL doesn't have a FULL OUTER JOIN, so you need to simulate it, but be warned that is slw

CREATE tABLE t1(id int, col_1 varchar(50))

INSERT INTO t1 VALUES (1,'Tim'),(2,'Martqa')

CREATE tABLE t2(id int, col_2 varchar(50))

INSERT INTO t2 VALUES (1,'Tim'),(3,'Katarina')

SeLECT t1.id, t1.col_1,t2.col_2
FROM t1 LEFT JOIN t2 USING(id)
UNION 
SeLECT t2.id, t1.col_1,t2.col_2
FROM t1 RIGHT JOIN t2 USING(id)

id | col_1  | col_2   
-: | :----- | :-------
 1 | Tim    | Tim     
 2 | Martqa | null    
 3 | null   | Katarina

SeLECT t1.id, t1.col_1,t2.col_2
FROM t1 LEFT JOIN t2 USING(id)
WHERE t1.id > 1
UNION 
SeLECT t2.id, t1.col_1,t2.col_2
FROM t1 RIGHT JOIN t2 USING(id)
WHERE t2.id > 1

id | col_1  | col_2   
-: | :----- | :-------
 2 | Martqa | null    
 3 | null   | Katarina

SELECT * FROM
(SeLECT t1.id, t1.col_1,t2.col_2
FROM t1 LEFT JOIN t2 ON t1.id = t2.id 
UNION 
SeLECT t2.id, t1.col_1,t2.col_2
FROM t1 RIGHT JOIN t2 ON t1.id = t2.id) t1
WHERE id > 1

id | col_1  | col_2   
-: | :----- | :-------
 2 | Martqa | null    
 3 | null   | Katarina

db<>fiddle here

Upvotes: 1

Amit Verma
Amit Verma

Reputation: 2490

Try this

select t1.id, t1.col_1, t2.col_2
FROM t1 LEFT OUTER JOIN t2 ON (t1.id=t2.id)
UNION
SELECT t2.id, t1.col_1, t2.col_2
FROM t2 LEFT OUTER JOIN t1 ON (t2.id = t1.id)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269913

You want a full join, which MySQL does not support One way is to get all the ids and use left join:

select *
from (select id from t1
      union    -- on purpose to remove duplicates
      select id from t2
     ) i left join
     t1
     using (id) left join
     t2
     using (id);

Upvotes: 0

Related Questions