See below example. ID_1
and ID_A
are primary keys for table_1
and table_3
respectively. Table_2
, the primary key is ID_1
and ID_A
. ID_A
is joined to one and only one ID_1
via table_2
due to business rules.
I need a query that will return all rows from table_1
where the value_1 = 11
and joined to that, I need only the rows from table_3
that have a value_a = 'a'
if it joins to a row in table_1
that has a value_1 = 11
.
Please see results required. Note there is no row
|1 | a |
in the result set because value_a
for id_a = 'b'
;
I've tried a left outer join but all ID_1
are returned. I've also tried CTE, to no avail. Here's one SQL, I tried.
Select ID_1, Value_1, ID_A, VALUE_A
From TABLE_1 t1
Join TABLE_2 t2 on t1.ID_1 = t2.ID_1
Left Outer Join TABLE_3 t3 on t2.ID_A = t3.ID_A;
I've also tried using a temporary table which include only the results from TABLE_1
that I need, but the left outer join using the temp table still returns the above mentioned row.
The DB is Oracle, but I prefer a standard solution. Also, performance is a consideration, given that TABLE_1
has over 200 million rows and TABLE_3
has about 4 times that many.
TABLE_1
| ID_1 | Value_1 |
-------------------
| 1 | 11 |
| 2 | 12 |
| 3 | 11 |
| 4 | 13 |
TABLE_2
| ID_1 | ID_A |
----------------
| 1 | a |
| 1 | b |
| 3 | c |
| 4 | d |
TABLE_3
| ID_A | Value_A |
------------------
| a | b |
| b | a |
| c | e |
| d | a |
Results Required
| ID_1 | ID_A | Value_1 | Value_A |
----------------------------------------
| 1 | b | 11 | a |
| 3 | <NULL> | 11 | <NULL> |
Best Answer
First, join
TABLE_2
andTABLE_3
using an inner join and additionally filtering onValue_A = 'a'
:This will give you the following result set:
Now use the above as a derived table and join it, using an outer join this time, to
TABLE_1
, additionally filtering the results onValue_1 = 11
:That will give you the output you want:
However, nesting a query is not the only way to solve your problem – you can also use a nested join, which is much more concise:
The last query implements exactly the same logic as the previous query: first, tables
TABLE_2
andTABLE_3
are joined and the result is filtered, then it is joined toTABLE_1
and the final set is filtered again. Some people also add brackets around a nested join:to make it clearer (perhaps both for themselves and for future maintainers) that the nested join takes place before the outer-level one, logically, although the syntax is unambiguous enough without them.
Nevertheless, many people find it confusing even with brackets, and if you find yourself struggling with it as well, there is another option – right outer join:
Again, the logical sequence of events specified by the FROM clause repeats that of the derived table solution: a join between
TABLE_2
andTABLE_3
, filtered, is followed by a join withTABLE_1
. The different syntax does not alter the outcome and the results produced still match your requirements.