VFP 6 - SQL Guidance 4

[wetton]

I'be been using SQL extensively in my applications for the last few years - in particular to filter a set of relational tables based on the value of a combo on a form leading to the result displaying in another combo on the same form. Sometimes there are 3 levels of filtration.

My SQL is really trial and error to get a result particularly with 3 or more tables. I now find that some work sometimes and not at others.

I have had a practice put to me that SQL is like an onion where joins mate the layers together.

When using say

SELECT *;
	FROM ;
	(TEMP_GG AS G inner JOIN;
	(TEMP_EE AS E 	inner join;
	(TEMP_LL AS A	inner JOIN;
	TEMP_NN AS N	;
	ON N.nper = A.Per_no;
	AND N.primary;
	);
	ON E.eper = A.Per_no;
	);
	ON G.per1 = E.eper;
	AND G.etype= 1926;
	)

are there any 'rules' for

- the order in which the joins are made
- which 'side' comes first in the ON statement.

Is there any other practical advice to make the job a bit more than hit or miss?

Many thanks

PW

 

[JimWWinter]

My understanding is the joins will be made in the order they are declared, ie., in your example above E will be joined to G, then A will be joined to E, and finally N will be joined to A.

AFAIK, the only situation where it would matter which side of the join condition comes first would be with a join on character fields where they were not identical in length and SET EXACT is OFF, the default. In this case the comparison is made only to the end of the right hand field. So, 'Test' = 'Te' is .T. and 'TE' = 'Test' is .F.

Just an observation, I find it more readable to list the join condition right after each join statement, but YMMV.

Regards,
Jim

 

[Mike Lewis]

Jim,

the joins will be made in the order they are declared

I don't think that's right. In PW's example, the joins are nested (at least, I think they are -- the code is a little unclear). In a nested join, the first ON matches the last JOIN, the second ON matches the second-to-last JOIN, and so on.

However, I agree that putting the JOIN right after the ON is more readable. That's the sequential style, and is much easier to read if the join is anything under than a parent - child - grandchild hierarchy.

As for which side of the equals sign comes first, it's SET ANSI that affects the way that strings are compared in a Select, not SET EXACT.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[JimWWinter]

Mike,

I'll take your word on the first part.

On SET EXACT and ANSI I went back and checked the Hacker's Guide and you're partly right. According to the Hacker's Guide, it doesn't matter which side of the expression is shorter, they just evaluate to the length of the shorter string. 'So, in SQL commands, "Smithsonian"="Smith" and "Smith"="Smithsonian."'

Regards,
Jim

 

[Mike Lewis]

Jim,

I just checked the Hacker's Guide as well. You're right, of course. My point was that it's SET ANSI rather than SET EXACT that affects SQL SELECT (at least, I think that's right; Tamar is the person to answer these points).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

Hi, guys. You called?

PW-Mike is absolutely right that joins are evaluated from the inside out. So when you have code like:

FROM Table1 ;
  JOIN Table2 ;
    JOIN Table3 ;
      JOIN Table4 ;
        ON Table3.Field = Table4.Field ;
      ON Table2.Field = Table3.Field ;
    ON Table1.Field = Table2.Field

the first join performed is Table3 and Table4 using the first ON condition. Then, Table2 is joined to that temporary result using Table2.Field = Table3.Field. Finally, Table1 is joined to the new temporary result using the last ON clause.

Like the others, I find this style of join (called nested) hard to read and prefer to read and write the sequential style.

For your query, I'd use:

SELECT *;
    FROM TEMP_GG AS GG ;
      JOIN TEMP_EE AS EE ;
        ON GG.per1 = EE.eper;
      JOIN TEMP_LL AS AA ;
        ON EE.eper = AA.Per_no;
      JOIN TEMP_NN AS NN ;
        ON NN.nper = AA.Per_no;
    WHERE NN.primary;
      AND GG.etype= 1926;

You'll notice that I've made a number of other changes as well:

1) I switched to an indentation style I think is more readable. Of course, you should use whatever your coding standards call for.

2) I changed your local aliases to two letters. Single letter aliases can be confused with the default names of the first 10 work areas. I know that in the most recent versions of VFP, any problems related to that have been resolved. I don't remember whether that's a problem in VFP 6.

3) I moved the two conditions based on a single table to the WHERE clause because they don't impact the joins. With inner joins, the JOIN and WHERE clauses are pretty much interchangeable. With outer joins, you do sometimes need to include single table conditions in a join clause, but that's a lesson for another day.

4) I removed the optional Inner keyword because I find it adds clutter.

Finally, whichever style you use for joins, the VFP engine will actually perform the joins in whatever order it deems most efficient. In that case, also, inner vs. outer joins makes a different, but in a query that contains only inner joins, you don't really know what order the tables will be joined in. (To find out, use SYS(3054).

As for sides in the ON, it doesn't matter. VFP will determine which one to use for optimization, and as Mike pointed out, SET ANSI determines partial vs. full string matching and evaluation is to the end of the shorter string.

Tamar

 

[Stella740pl]

Tamar,

Single letter aliases can be confused with the default names of the first 10 work areas. ... I don't remember whether that's a problem in VFP 6.

It could be.

If you use only single-letter local aliases and don’t forget to assign them to the tables in the select statement, it's not a problem.

If you use more than one letter for local aliases and forgot to actually assign one of them to a table, VFP will remind you ("AA alias is not found").

But if you use single-letter local aliases (one of the first 10) and forgot to assign one of them to a table, now you will have a problem, as it won't remind you, it will just use the table open in the corresponding work area.

 

[wetton]

My thanks to everyone who answered - many valuable points not easily worked out alone.

PW

 

[Mike Lewis]

Mike,

You might be right. In fact, the only reason I started to use those abbreviated local aliases is because everyone else seems to.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[JimWWinter]

Yeah, I never used them until recently. But then I started working with a third party SQL Server database where table names of 20 or more characters are the rule rather than the exception. All of a sudden those abbreviations look good when I'm typing out a field list.

Regards,
Jim

 

[Stella740pl]

Mike Yearwood,

If your tables called "Payroll" and "Employee", that you might be right, you can just go without local aliases.

But if your tables are called something like "Easy_Pay0607_tmp" and "LocalCodes2006" and you got to join them, and have to specify some lengthy conditions in the JOIN...ON clause and WHERE clause, then using the full names leads to a somewhat cluttered SELECT statement. But of course, you can shorten it to somewhat meaningful, like "EPay" and "Codes".

If you have table names that start with a digit (and I inherited a couple of those, but was allowed to rename them eventually), you just absolutely must open them with an explicit alias.

 

[TamarGranor]

The issue with single-letter aliases is the third problem Stella mentions. However, at some point, it went away, and I can't replicate it in recent versions of VFP. I just don't remember when they fixed it.

As for using local aliases or not, I don't generally do so unless either:

a) I need the same table more than once in the query (the most likely);
b) a table name is very long;
c) the alias will make the code easier to read than the table name for some reason.

Tamar

 

[Stella740pl]

I said meaningful from the start.
Yes, it was the part I was agreeing with you about. Didn't you notice?