... T1 FULL JOIN T2 .... * is equal to ... T2 FULL JOIN T1 ....On VFP this is not true:
CREATE CURSOR T1 (F1 I ) CREATE CURSOR T2 (F2 I ) INSERT INTO T1 VALUES (1) INSERT INTO T2 VALUES (2) SELECT * FROM T1 FULL JOIN T2 ON .T. * --------- * 1 2 * NULL 2 this is bad records SELECT * FROM T2 FULL JOIN T1 ON .T. * --------- * 2 1 * NULL 1 this is bad recordsExample 2
CREATE CURSOR T1 (F1 I ) CREATE CURSOR T2 (F2 I ) INSERT INTO T1 VALUES (1) INSERT INTO T2 VALUES (2) SELECT F1,F2 FROM T1 FULL JOIN T2 ON .T. * -F1---F2-- * 1 2 * NULL 2 nulls are on F1 fields && next force VFP to change loop join pivot to T2 INSERT INTO T1 VALUES (3) ble SELECT F1,F2 FROM T1 FULL JOIN T2 ON .T. * -F1---F2-- * 1 2 * 3 2 * 1 NULL null are on F2 fields * 3 NULL null are on F2 fields * if i force pivot looping to T1, result is obvious now SELECT F1,F2 FROM FORCE T1 FULL JOIN T2 ON .T. * -F1---F2-- * 1 2 * 3 2 * NULL 2 nulls are on F1 fieldsWith 3 tables results is more clear:
CREATE CURSOR T1 (F1 I ) CREATE CURSOR T2 (F2 I ) CREATE CURSOR T3 (F3 I ) INSERT INTO T1 VALUES (1) INSERT INTO T2 VALUES (2) INSERT INTO T3 VALUES (3) SELECT F1,F2,F3 FROM T1 FULL JOIN T2 ON .T. FULL JOIN T3 ON .T. * 4 Records INSERT INTO T1 VALUES (3) && this force VFP to change loop join pivot table SELECT F1,F2,F3 FROM T1 FULL JOIN T2 ON .T. FULL JOIN T3 ON .T. * 8 records SELECT F1,F2,F3 FROM FORCE T1 FULL JOIN T2 ON .T. FULL JOIN T3 ON .T. * 4 recordsIf this is by design, then it is very different of SQL Server FULL JOIN.