Left join expression and number of rows returned by Oracle

Question

A query with a left join does not return records, although a where clause from the left table should find one record. In this case, it should return a record with fields from the left table containing the values, and from the right table null, since there is no match between them.

There seems to be a problem with using a case that references the right table in a join expression.

In SQL Server, the same query worked as expected.

select t1.Description, t2.Description from A t1 left join B t2 on t1.Id = t2.Id and 1 = case when ( t2.Id = t2.Id and (select t3.Flag from C t3 where t3.ID_B = t2.Id) = 'S' ) then 1 else 0 end where t1.Id = 1 

Result: no rows returned.

Then I moved the expression t2.Id = t2.Id (that is, here only to demonstrate the problem and should always return true, apparently) from the case expression.

 select t1.Description, t2.Description from A t1 left join B t2 on t1.Id = t2.Id and t2.Id = t2.Id and 1 = case when ( (select t3.Flag from C t3 where t3.ID_B = t2.Id) = 'S') then 1 else 0 end where t1.Id = 1 

Result: one row is returned.

The queries above are only to demonstrate the problem, are not useful in a real situation and are not optimized.

I want to know if anyone knows any Oracle limitation related to this case. So far, we believe that this is a mistake.

Used data:

• A: Id = 1, Description = Item A1;
• B: Id = 1, description = element B1;
• C: Id = 1, Id_B = 2, flag = S.

Answer 1

Your assumption that t2.id = t2.id always true is false. If the value was NULL , which would be considered false. I do not think this is relevant for this particular example, but just for clarification.

The question is how left join handled. The idea is simple. The on clause is being processed. If there are no matches, then the row from the first table is saved. This is whatever is in the on clause. (This is a functional description; there are many possible implementations.)

Based on your example data, Oracle is wrong. One row should be returned. The SQL Server example should also return a single row. I suspect the data may be slightly different; I personally have never had a problem with left join in SQL Server (or Oracle).

Answer 2

 CREATE TABLE t1 AS (SELECT 1 ID FROM dual); CREATE TABLE t2 AS (SELECT 2 ID FROM dual); CREATE TABLE t3 AS (SELECT 2 id_b, 's' flag FROM dual); SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.ID = t2.ID AND 1 = CASE WHEN t2.id = t2.id and (SELECT flag FROM t3 WHERE t3.id_b = t2.ID) = 's' THEN 1 ELSE 0 END where t1.id = 1; 

Output: no rows selected

The result looks strange, I suppose it could be a mistake.

For Oracle documentation only https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#SQLRF52337

You cannot compare a column with a subquery in the WHERE clause of any outer join, no matter what shape you specify.

Having studied the plan of the above request, I see that this condition:

  AND 1 = CASE WHEN t2.id = t2.id and (SELECT flag FROM t3 WHERE t3.id_b = t2.ID) = 's' THEN 1 ELSE 0 END 

interpreted as:

  CASE WHEN (T2.ID(+)=T2.ID(+) AND (SELECT FLAG FROM T3 T3 WHERE T3.ID_B=:B1)='s') THEN 1 ELSE 0 END =1 

and is calculated after the connection .

I believe that Oracle cannot calculate CASE until a connection is made (due to T2.ID(+)=T2.ID(+) )

Answer 3

Using SQLFiddle Oracle 11g R2 (thanks to Shannon Severance), your first query gives the number of records: 0, but just removing CASE, we get the number of records: 1. (Note the renaming of t2Description.)

 create table A (ID number(38), Description varchar(10)); create table B (ID number(38), Description varchar(10)); create table C (ID number(38), ID_B number(38), Flag varchar(10)); insert into A values(1, 'Item A1'); insert into B values(2, 'Item B1'); insert into C values(1, 2, 'S'); select t1.Description, t2.Description as t2d from A t1 left join B t2 on t1.Id = t2.Id and t2.Id = t2.Id and (select t3.Flag from C t3 where t3.ID_B = t2.Id) = 'S' where t1.Id = 1 

This suggests that this is due to an erroneous CASE calculation.

Note that in ON t2.Id at least sometimes (correctly) a value is taken from the FROM cross-product, and not NULL, which it is after ON:

 select t1.Description, t2.Description as t2d from A t1 left join B t2 on -- for above data t2.id should be 1 here t2.id is null where t1.Id = 1 -- for above data t2.id should be null here DESCRIPTION T2D Item A1 (null) 

I found this link: Outer Join Error in Oracle 12c?