Left Outer Join
-
In the following query for records where
G.CharField1 is Null and
SUBSTRING(D.VarcharField1, 1, 4) = 'GLLA'
the expression for the Left Join that D.VarcharField1 is joined to does not behave as I would expect it to. If any one part of the expression is commented out e.g.'GLLA' or G.CharField1 or G.CharField2 etc, the query returns data, when all 5
are not commented out, no data is returned? Any ideas?
SELECT ...
FROM D_table D
INNER JOIN #BP BP ON SUBSTRING(D.CharField1, 5, 6) = BP.CharField1
LEFT JOIN G_t G ON
(
D.VarcharField1 = 'GLLA' +
G.CharField1 +
G.CharField2 +
G.CharField3 +
UPPER(REPLACE(CONVERT(CHAR(11), G.DateField1,.106),' ',''))
 
WHERE G.CharField1 IS NULL AND
SUBSTRING(D.VarcharField1, 1, 4) = 'GLLA'
-
It seems a little backwards... Can you please post a few samples with tables, some data and a description of what you want to do and the desired output..?
You may also experiment by setting SET CONCAT_NULL_YIELDS_NULL to OFF and see what happens.. (though I think the query looks strange regarding your WHERE clause..)
/Kenneth
-
The problem could be that all rows in the table resulting from the left join, for which SUBSTRING(D.VarcharField1, 1, 4) = 'GLLA', fulfill the condition that G.CharField1 IS NOT NULL. But when you change the join condition, it is no longer true. Then G.CharField will be NULL in the table resulting from the left join (because you use a left join and not an inner join) and the where clause will suddenly be satisfied. Hope this makes sense
Apart from that, I agree with Kenneth - I don't think this join condition will perform well if G_t is large.
-
Hi,
Thanks for your prompt response.
I am trying to resolve a problem with a large system. I did not write the query, it is not documented and it is not clear what is trying to do.
I am not allowed to post data or to reference entities and attributes by their names.
I think it is trying to get records where there is no match in G_t hence the clause
WHERE G.CharField1 IS NULL.
In that case, because null propagates, the following expression should always be equivalent to D.VarcharField1 = NULL
(
D.VarcharField1 = 'GLLA' +
G.CharField1 +
G.CharField2 +
G.CharField3 +
UPPER(REPLACE(CONVERT(CHAR(11), G.DateField1,.106),' ',''))
)
and it should not eliminate records because it is a LEFT JOIN.
However when the full expression between the brackets is used, all records are eliminated.
When any of the constituent elements of the expression are commented out, it returns the required data.
And this is what is puzzling me.
Thanks.
-
It would probably be easier to figure out what's happening if you could build a small repro script and post that.
We need basic DDL of the tables involved (with accurate datatypes and nullability), and a few rows of data. (you can make up names if you'd like) Then you describe the steps necessary to reproduce the behaviour you describe, along with the desired result you want.
/Kenneth
-
Replace the join condition by the trivial statement "1=1" (such that you are left joining on 1=1). Then replace "1=1" by the (equally trivial) statement "1=2". What happens? I guess you only get data in the latter case, but I could be wrong...
-
Hi,
Yes for those cases it behaves as expected.
i.e.
Records not returned for 1=1
Records returned for 1=2
-
Exactly - so your join condition is true (at least for some rows) when you don't comment anything out, and false when you do..... I think
-
PK;
In your nested functions line in your join, is that a typo only in this posting, or do you have the same in your query? I am looking at the 3rd parameter to your CONVERT function, where it is listed ".106").
If that is there in your query, and depending on how you are commenting out sections of the clause, that could be what is leading to your disparate results.
Mark -
Hi Mark,
Sorry, thats a typo.
Regards,
Padraic.
-
I think I know what you are missing now. The "G.CharField1" in the join clause is not the same as the "G.CharField1" in the where clause. The former is a column in table G_t, the latter is a column in the left outer join. The latter is null when the former is null OR when you are looking at a row in the first table that doesn't satisfy the join condition. Therefore, the join condition
D.VarcharField1 = 'GLLA' +
G.CharField1 +
G.CharField2 +
G.CharField3 +
UPPER(REPLACE(CONVERT(CHAR(11), G.DateField1,.106),' ',''))
is NOT equivalent to D.VarcharField1 = NULL even though the where clause says that G.CharField1 is null (I guess you have SET CONCAT_NULL_YIELDS_NULL set to ON), because the "G.CharField1" in the where clause refers to a different table.
I hope this makes sense - left join are a bit tricky
-
Hi,
Yes Jesper, that's correct.
The key is the sequence of steps. First the join is performed, then the WHERE clause.
I was thinking that the WHERE clause would eliminate all records before the join was performed, except those where
WHERE GLLA.num_pol_glla IS NULL
in which case the join expression in brackets would always be equivalent to
D.VarcharField1 = NULL
But of course the query first does the join operation and only then eliminates the records that have a match in G_t
Thanks to everyone who took the time to respond.
Regards,
Padraic
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply