INteresting... I'll go for some testing on this.........
Specially on the part Ann states that using such a
subquery transforms the left join on a inner join.....

My test results gave me very strange results indeed.
I do really separate join conditions of search conditions.

Firebird Server SS WI-V1.5.0.4306
Database created under the charset ISO8859_1;
Dialect 3

Administration Tool: IBExpert Personal 2004.06.17

Script (only the interesting parts):

CREATE TABLE TABLE1 (
ID INTEGER NOT NULL,
VALOR1 VARCHAR(50)
);


CREATE TABLE TABLE2 (
ID_TABLE2 INTEGER NOT NULL,
ID INTEGER,
VALOR_T2 VARCHAR(50),
POS_T2 VARCHAR(50)
);
/* View: NEW_VIEW */
CREATE VIEW NEW_VIEW(
ID_TABLE2,
ID,
VALOR_T2,
POS_T2)
AS
select * from TABLE2 b WHERE B.VALOR_T2 = 'VALOR1_T2'
and B.POS_T2 = 'POSICAO1_T2'
;

ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (ID);
ALTER TABLE TABLE2 ADD CONSTRAINT PK_TABLE2 PRIMARY KEY (ID_TABLE2);
ALTER TABLE TABLE2 ADD CONSTRAINT FK_TABLE2 FOREIGN KEY (ID) REFERENCES
TABLE1 (ID);

Data:


INSERT INTO TABLE1 (ID, VALOR1) VALUES (1, 'VALOR1_T1');
INSERT INTO TABLE1 (ID, VALOR1) VALUES (2, 'VALOR2_T1');
INSERT INTO TABLE1 (ID, VALOR1) VALUES (3, 'VALOR3_T1');

COMMIT WORK;


INSERT INTO TABLE2 (ID_TABLE2, ID, VALOR_T2, POS_T2) VALUES (1, 1,
'VALOR1_T2', 'POSICAO1_T2');
INSERT INTO TABLE2 (ID_TABLE2, ID, VALOR_T2, POS_T2) VALUES (2, 1,
'VALOR2_T2', 'POSICAO2_T2');
INSERT INTO TABLE2 (ID_TABLE2, ID, VALOR_T2, POS_T2) VALUES (3, 2,
'VALOR3_T2', 'POSICAO3_T2');

COMMIT WORK;


Queries ran and results:
1)Ann-Style

select a.*, a2.*
from TABLE1 a
left join TABLE2 a2
on a."ID" = a2."ID" and
a2.valor_t2 = :Val and
a2.pos_t2 = :Pos

Parameters:
:val = 'VALOR1_T2'
:pos = 'POSICAO1_T2'

Result:
ID VALOR1 ID_TABLE2 ID1 VALOR_T2 POS_T2

1 VALOR1_T1 1 1 VALOR1_T2 POSICAO1_T2
2 VALOR2_T1 <NULL> <NULL> <NULL> <NULL>
3 VALOR3_T1 <NULL> <NULL> <NULL> <NULL>


(If columns scramble, change the font to a monospaced one)

2)Arno-Style
SELECT
*
FROM
TABLE1 a
LEFT JOIN TABLE2 a2 ON (a."ID" = a2."ID_ref" and
a2."Val" = :Val and a2."Pos" = :Pos)

Parameters:
Same as above

Results:
ID VALOR1 ID_TABLE2 ID1 VALOR_T2 POS_T2

1 VALOR1_T1 1 1 VALOR1_T2 POSICAO1_T2
2 VALOR2_T1 <NULL> <NULL> <NULL> <NULL>
3 VALOR3_T1 <NULL> <NULL> <NULL> <NULL>

3)Common-Sense, Helen-Style
select a.*, a2.*
from TABLE1 a
left join TABLE2 a2
on a."ID" = a2."ID"
where a2.valor_t2 = :Val
and a2.pos_t2 = :Pos

Parameters:
Same as above

Results
ID VALOR1 ID_TABLE2 ID1 VALOR_T2 POS_T2

1 VALOR1_T1 1 1 VALOR1_T2 POSICAO1_T2


4) Original query - ran on Microsoft SQL Server
Environment: same as above, same data
Parameters: same as above
SQL Batch Text:
declare @p1 varchar(50);
declare @p2 varchar(50);

set @p1 = 'VALOR1_T2';
SET @P2 = 'POSICAO1_T2';

select * from TABLE1 a
left join (
select * from TABLE2 a2 where a2.VALOR_T2 = @P1
and a2.POS_T2 = @P2 ) b
on a."ID" = b."ID"

Result (I abbreviated the trailing spaces):
ID VALOR1 ID_TABLE2 ID VALOR_T2 POS_T2

----------- ---------- ----------- ----------- ----------- -----------
1 VALOR1_T1 1 1 VALOR1_T2 POSICAO1_T2
2 VALOR2_T1 <NULL> <NULL> <NULL> <NULL>
3 VALOR3_T1 <NULL> <NULL> <NULL> <NULL>


(3 row(s) affected)

5) Helen-style, on MSSQL
SQL Batch Text:
declare @p1 varchar(50);
declare @p2 varchar(50);

set @p1 = 'VALOR1_T2';
SET @P2 = 'POSICAO1_T2';

select * from TABLE1 a
left join TABLE2 a2
on a."ID" = A2."ID"
where a2.VALOR_T2 = @P1
and a2.POS_T2 = @P2

Parameters: need to say? :-)

Result: Same as 3)

Very interesting!!!!!! Is not a Firebird thing!!!!
MSSQL have the same behavior.

Let's go to the sixth test...

6) No where clause on Helen's query:
For this test, I'll not include query text, since is the
same as correspondent test either FB or MSSQL without the
WHERE clause
FB & MSSQL Results:

ID VALOR1 ID_TABLE2 ID VALOR_T2 POS_T2
----------- ------------ ----------- ----------- ------------
------------
1 VALOR1_T1 1 1 VALOR1_T2
POSICAO1_T2
1 VALOR1_T1 2 1 VALOR2_T2
POSICAO2_T2
2 VALOR2_T1 3 2 VALOR3_T2
POSICAO3_T2
3 VALOR3_T1 <NULL> <NULL> <NULL> <NULL>

(4 row(s) affected)

So what's happening is that the SQL processor is joining the sets
and AFTER that applying the WHERE clause... Indeed it behaves like
a INNER JOIN after all - reafirming Ann's assertive and Arno's
advice...

Just to say, I tried on SQL server to be able to know what was the
result set that the asker was wainting from the original (since MSSQL
does derived tables on SELECTs)

On Tue, 08 Mar 2005 12:16:46 +1100, Helen Borrie wrote:

>
>An interesting thing came up in the support list today, regarding explicit
>join semantics. Since both Ann and Arno seemed to be "coming from the same
>place" with respect to this issue, I'd be interested in their expanding on
>this question. If it makes sense to me, I'd like to write a FAQ sheet or
>similar with regard to it, since it news to me.
>
>The inquirer wanted to know how to write this query:
>
>select * from TABLE1 a
>left join (
> select * from TABLE2 a2 where a2."Val" = :Val
> and a2."Pos" = :Pos ) b
>on a."ID" = b."ID_ref"
>
>He seemed to be attempting to make derived table style of query. I replied
>as follows:
>
>Well, the derived table seems redundant for this example, which appears to
>be a straightforward left join.
>
>select a.*, a2.*
>from TABLE1 a
>left join TABLE2 a2
>on a."ID" = a2."ID_ref"
>where a2."Val" = :Val
>and a2."Pos" = :Pos
>
>Next, Ann jumped in with this:
>
>My understanding is that putting the conditions
>
> a2."Val" = :Val and a2."Pos" = :Pos
>
>into the where clause changes the query from a left join to an inner join,
>since missing values from the right table won't pass the where sieve.
>
>Putting the conditions into the "on" clause should give the expected result
>for a left join.
>
> select a.*, a2.*
> from TABLE1 a
> left join TABLE2 a2
> on a."ID" = a2."ID_ref" and
> a2."Val" = :Val and
> a2."Pos" = :Pos
>
>Arno confirmed this:
>
>I think it's late or very early for Helen ;-)
>
>The where clause should be in the on clause
>
>SELECT
> *
>FROM
> TABLE1 a
> LEFT JOIN TABLE2 a2 ON (a."ID" = a2."ID_ref" and
> a2."Val" = :Val and a2."Pos" = :Pos)
>
>OK. Now, ever since we had explicit join support in IB (5.1?) I've
>believed that
>
>1) the semantics were identical whether the search criteria were in the ON
>clause or the WHERE clause
>
>AND
>
>2) it's not good practice to merge search conditions with join conditions
>
>Now, it's obvious that I'm misinformed about the semantics, at least as far
>as Firebird is concerned. Both responses imply that WHERE conditions that
>are ANDed to an ON condition are handled in an intermediate stream in some
>way that affects the output.
>
>This is interesting to me for at least two reasons: first, in The Book, in
>the firm belief that the engine sifts out WHERE criteria that are ANDed to
>the join criteria and defers them to the WHERE clause, I've recommended NOT
>trying to combine search criteria with join criteria in this fashion,
>specifically because some abstraction layers have trouble parsing out
>parameters from such constructions. Secondly, if the semantics really are
>different it throws fresh possibilities into solving a problem one of my
>customers has currently with Firebird 1.0.
>
>For this discussion, I'd like Ann or Arno (or, better, both) to explain
>these semantics according to how the engine/optimizer actually processes
>the request.
>
>I'd also like to know at what point the semantics changed. Was this a Fb
>1.5 thing, or was it already done by the time we had Fb 1.0?
>
>cheers,
>Helen
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>