CODEWITHSUNDEEP
sqloracle-database
l0r3nz4cc10
l0r3nz4cc10

Reputation: 1283

Simple outer join between two tables not working

I have a very simple query , and yet I can't get it to work like I want it to.

I have 2 tables, A and B which are very similar and look like this :

A :

+------+----------+---------+
| a_id | a_cnt_id | a_value |
+------+----------+---------+
| 1    | 848      | 0.5     |
| 2    | 848      | 3       |
| 3    | 848      | 4       |
| 4    | 848      | 65      |
+------+----------+---------+

B :

+------+----------+---------+
| b_id | b_cnt_id | b_value |
+------+----------+---------+
| 1    | 849      | 36      |
| 2    | 849      | 42      |
| 3    | 849      | 8       |
+------+----------+---------+

B has more records than A for a given set of {a_cnt_id, b_cnt_id}.

I would like my query to return this :

+------+------+---------+---------+
| a_id | b_id | a_value | b_value |
+------+------+---------+---------+
| 1    | 1    | 0.5     | 36      |
| 2    | 2    | 3       | 42      |
| 3    | 3    | 4       | 8       |
| 4    | NULL | 65      | NULL    |
+----+--------+---------+---------+

My (not working) query, because it returns only the first 3 rows :

select distinct a.a_id, b.b_id, a.a_value, b.b_value
from b
full join a on b.b_id = a.a_id
where a.a_cnt_id = 849
and b.b_cnt_id = 848;

Upvotes: 0

Views: 50

Answers (3)

Oğuz Sezer
Oğuz Sezer

Reputation: 320

I don't remember where I found this but here you go: enter image description here

EDIT: The link of the image belongs to Visual-Representation-of-SQL-Joins. Thanks @jyparask

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

Adding a WHERE clause will filter the results to what the where suggests. So if you have where a.a_cnt_id = 849 it will get only these rows, not the ones with the null. Move your filters to the join:

select distinct a.a_id, b.b_id, a.a_value, b.b_value
from b
full join a on b.b_id = a.a_id
and a.a_cnt_id = 849
and b.b_cnt_id = 848;

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

Move the cnt_id checks to the ON clause instead to preserve the OUTER JOIN, like so:

select distinct a.a_id, b.b_id, a.a_value, b.b_value
from b
full join a on b.b_id = a.a_id
and a.a_cnt_id = 849
and b.b_cnt_id = 848;

Upvotes: 1

Related Questions