Let's assume two simple tables:

create table t1 (id number(2), name varchar2(12), primary key(id)); and
create table t2 (t2_id number(2), t1_id number(2), primary key(t2_id));

and add some values to them:

insert into t1 values (1,'test');
insert into t1 values (2,'test2');
insert into t2 values (1, 1);
insert into t2 values (3,null);

I want to emulate a left join, i.e.

select * from t1 left join t2 on t1.id=t2.t1_id;

I have written the query as:

select *
from t1, t2
where t1.id=t2.t1_id or
(t2.t1_id is null and id not in (select t1_id from t2 where t1_id is not null));

This works, but because of the cross-join it is pulling in the other values of t1 with which the cross-join happened. Is there a way to say all t1 attributes should display as null in the result set if the second condition in the query is true?

Best Regards, Gee

Let's assume two simple tables:

create table t1 (id number(2), name varchar2(12), primary key(id)); and
create table t2 (t2_id number(2), t1_id number(2), primary key(t2_id));

and add some values to them:

insert into t1 values (1,'test');
insert into t1 values (2,'test2');
insert into t2 values (1, 1);
insert into t2 values (3,null);

I want to emulate a left join, i.e.

select * from t1 left join t2 on t1.id=t2.t1_id;

I have written the query as:

select *
from t1, t2
where t1.id=t2.t1_id or
(t2.t1_id is null and id not in (select t1_id from t2 where t1_id is not null));

This works, but because of the cross-join it is pulling in the other values of t1 with which the cross-join happened. Is there a way to say all t1 attributes should display as null in the result set if the second condition in the query is true?

Best Regards, Gee

• oracle-database
• join

• Why do you want to do this at all? Your query looks wrong. t2 rows where t1_id is null should be completely irrelevant. Such rows may or may not exist; they are ignored in the outer join, so they should get ignored in your query, too. Maybe you have a misunderstanding as to what a left outer join does. – Thorsten Kettner Commented yesterday
• Why do you want to emulate it, if you can do a proper left join? – The Impaler Commented 16 hours ago
• Your last query is a right join, not a left join. Please include the expected result – The Impaler Commented 16 hours ago

1 Answer 1

You do not have a LEFT JOIN, you have an INNER JOIN:

SELECT *
FROM   t1, t2
WHERE  t1.id = t2.t1_id

Which is identical to:

SELECT *
FROM   t1
       INNER JOIN t2
       ON t1.id = t2.t1_id

UNION ALL with a filtered CROSS JOIN:

SELECT *
FROM   t1, t2
WHERE  t2.t1_id is null
AND    id not in (select t1_id from t2 where t1_id is not null);

Which is effectively the same as:

SELECT *
FROM   (
         SELECT *
         FROM   t1
         WHERE  NOT EXISTS (SELECT 1 FROM t2 x WHERE t1.id = x.t1_id)
       )
       CROSS JOIN (
          SELECT *
          FROM   t2
          WHERE  t1_id is null
       );

The whole point of a LEFT OUTER JOIN is that you get the rows from the driving table where there are no matching rows in the other table. The only reason that you get a second row of output is because you are joining to a NULL value in t2 using the filtered CROSS JOIN - if you did not have that NULL value then you would not get a second row of output.

If you want a LEFT OUTER JOIN then it needs to work even when there is nothing for the driving table to JOIN to.

If you want a LEFT OUTER JOIN without using that join then, use a correlated sub-query:

SELECT t1.id,
       t1.name,
       (SELECT t2_id FROM t2 WHERE t1.id = t2.t1_id) AS t2_id
FROM   t1;

or an INNER JOIN that is UNION ALL to an anti-join between the driving table and the other table:

SELECT t1.*, t2.*
FROM   t1
       INNER JOIN t2
       ON t1.id = t2.t1_id
UNION ALL
SELECT t1.*, NULL, NULL
FROM   t1
WHERE  NOT EXISTS (SELECT 1 FROM t2 WHERE t1.id = t2.t1_id)

or, using a LATERAL JOIN and provide a default row of NULL values when t2 does not have a matching value (which is not the same as having a NULL row contained in the t2 table):

SELECT t1.id,
       t1.name,
       t2.t2_id,
       t2.t1_id
FROM   t1
       CROSS JOIN LATERAL(
         SELECT t2_id, t1_id, 1 AS priority
         FROM   t2
         WHERE  t1.id = t2.t1_id
         UNION ALL
         SELECT NULL, NULL, 2 AS priority
         FROM   DUAL
         ORDER BY priority
         FETCH FIRST ROW WITH TIES
       ) t2;

Then for the sample data:

create table t1 (id number(2), name varchar2(12), primary key(id));
create table t2 (t2_id number(2), t1_id number(2), primary key(t2_id));

insert into t1 values (1,'test');
insert into t1 values (2,'test2');
insert into t2 values (1, 1);

(Note: no second row in t2.)

Then LEFT OUTER JOIN, the correlated sub-query, the INNER JOIN unioned with the anti-join and the CROSS JOIN LATERAL all output:

Your query doesn't output the same and only outputs the first row.

fiddle