Delphi Pages Forums  

Go Back   Delphi Pages Forums > Delphi Forum > General

Lost Password?

Reply
 
Thread Tools Display Modes
  #1  
Old 03-06-2001, 05:13 AM
Anton_bg Anton_bg is offline
Junior Member
 
Join Date: Feb 2001
Posts: 4
Default Different result with different consistency of JOIN clauses

Hi all,
I am working with Delphi 4 and local SQL.
I don't understand what is the precedence of JOIN clauses in SQL statements.
I notice that I receive the different result for the next two sql statements:
The difference between those two statements is only in consistency of JOIN clauses.
First statement:
SELECT Table1."Field1", Table2."Field2_2" , Table3."Field3_2",
Table1."Field2
FROM "Oborot.DB" Table1
INNER JOIN "Protokol.DB" Protokol
ON (Table1."Field0" = Protokol."Field0)
LEFT OUTER JOIN "Document.DB" Table2
ON (Table1."Field4" = Table2."Field2_1")
LEFT OUTER JOIN "Document.DB" Table3
ON (Table1."Field5" = Table3."Field3_1")
WHERE
(Protokol.Type = 2) AND
( Table1.Data < "01/01/2001") AND
( Table1.Data > "12/31/1999")
Second statement:
SELECT Table1."Field1", Table2."Field2_2" , Table3."Field3_2",
Table1."Field2
FROM "Oborot.DB" Table1
LEFT OUTER JOIN "Document.DB" Table2
ON (Table1."Field4" = Table2."Field2_1")
LEFT OUTER JOIN "Document.DB" Table3
ON (Table1."Field5" = Table3."Field3_1")
INNER JOIN "Protokol.DB" Protokol
ON (Table1."Field0" = Protokol."Field0)
WHERE
(Protokol.Type = 2) AND
( Table1.Data < "01/01/2001") AND
( Table1.Data > "12/31/1999")
I know the precedence of logical operations but I don't know the precedence of JOIN clauses.
Table1 has records with no matching records in Table2 an in Table3. That is the reason because I must use LEFT OUTER JOIN

Please help me
Anton
Reply With Quote
  #2  
Old 03-06-2001, 07:35 AM
Claude Claude is offline
Senior Member
 
Join Date: Jan 2001
Posts: 178
Default RE: Different result with different consistency of JOIN clauses

I will cite Joe Celko's "SQL for smarties", an excellent book from a skilled author. This is from page 196.

SQL engines that use special operators in the WHERE clause very often get strange results. The same table can be marked for preservation in one predicate, but not in another. Regular theta operators can remove rows that might have been part of an OUTER JOIN, and so forth. The order of predicates can also make a difference as to which is executed first, ans that is often in the hands of the optimizer, not the programmer.

Until these products have implemented the SQL-92 version of OUTER JOINs, the programmer will have to experiment, make heavy uses of VIEWs in which only pairs of tables are used, or avoid OUTER JOIN queries.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump


All times are GMT. The time now is 08:21 PM.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2019, vBulletin Solutions, Inc.