Skip to Main Content
  • Questions
  • Conditionally join (left or inner) two table

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Andris.

Asked: August 25, 2016 - 11:19 am UTC

Last updated: August 26, 2016 - 3:49 pm UTC

Version: Oracle Database 11g 11.2.0.3.0

Viewed 10K+ times! This question is

You Asked

Hi!

First of all thanks for taking time to read and answer my question.

So, I got two set of rows which needs to be joined. In some cases it should be outer left join, but in rest it should be inner.

Is it possible make such query?

Small sample with input (table_1, table_2) and desired output (t_result_set)


--source table_1
CREATE TABLE table_1 (id_1 NUMBER, value_1 VARCHAR2(10));

INSERT INTO table_1 VALUES (100, 'Value1');
INSERT INTO table_1 VALUES (100, 'Value2');
INSERT INTO table_1 VALUES (100, 'Value3');

INSERT INTO table_1 VALUES (200, 'Value1');
INSERT INTO table_1 VALUES (200, 'Value2');
INSERT INTO table_1 VALUES (200, 'Value3');

--table which should be joined
CREATE TABLE table_2 (id_1 NUMBER, value_1 VARCHAR2(10));

INSERT INTO table_2 VALUES (100, 'Value2');

--table with result set
CREATE TABLE t_result_set (t1_id1 NUMBER, t1_value_1 VARCHAR2(10), t2_id1 NUMBER, t2_value_1 VARCHAR(10));

INSERT INTO t_result_set VALUES (100, 'Value2', 100, 'Value2');
INSERT INTO t_result_set VALUES (200, 'Value1', NULL, NULL);
INSERT INTO t_result_set VALUES (200, 'Value2', NULL, NULL);
INSERT INTO t_result_set VALUES (200, 'Value3', NULL, NULL);


-- basically I want to join table_2 to table_1 in manner that if there is a mach (inner join) between table_1 and table_2 then it is returned
SELECT *
  FROM table_1 t_1
  JOIN table_2 t_2 ON t_1.id_1 = t_2.id_1
                  AND t_1.value_1 = t_2.value_1;

--but if there is no mach then the left side of join is returned
SELECT *
  FROM table_1 t_1
  LEFT JOIN table_2 t_2 ON t_1.id_1 = t_2.id_1
                       AND t_1.value_1 = t_2.value_1
 WHERE t_1.id_1 = 200; 
--added where so it would skip t_1.id_1 = 100 where inner join is possible

-- pseudo logic it looks like this 
/*
IF EXISTS (SELECT 1
             FROM table_2 t_2
            WHERE t_2.id_1 = t_1.id_1
              AND t_2.value_1 = t_1.value_1) THEN
  INNER JOIN
ELSE
  LEFT JOIN
*/

--when the two previous statements are combined the desirable result should look like this
SELECT *
  FROM t_result_set;


Thanks!

and Chris said...

You mean something like this:

CREATE TABLE table_1 (id_1 NUMBER, value_1 VARCHAR2(10));

INSERT INTO table_1 VALUES (100, 'Value1');
INSERT INTO table_1 VALUES (100, 'Value2');
INSERT INTO table_1 VALUES (100, 'Value3');

INSERT INTO table_1 VALUES (200, 'Value1');
INSERT INTO table_1 VALUES (200, 'Value2');
INSERT INTO table_1 VALUES (200, 'Value3');

--table which should be joined
CREATE TABLE table_2 (id_1 NUMBER, value_1 VARCHAR2(10));

INSERT INTO table_2 VALUES (100, 'Value2');

select * 
from   table_1 t1
left join table_2 t2
on     t1.id_1 = t2.id_1
where  t1.value_1 = t2.value_1 or t2.id_1 is null
order  by 1;

ID_1  VALUE_1  ID_1  VALUE_1  
100   Value2   100   Value2   
200   Value2                  
200   Value1                  
200   Value3


By placing "t1.value_1 = t2.value_1" in the where, this becomes an inner join. But if there's no matching id_1 value in t2 it remains an outer join.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 26, 2016 - 5:56 pm UTC

Ah, now i see where the trick is.
Thx for response.