HI,

ive just been shown a sql query which works fine but i can seem to get my head arorund it.
Basically, i look at it, and it's quite obvious it's creating relationships between tables but it's so confusings.
My question is , why has my friend coded this was and not used joins?
Also.. if this is a way of coding sql, what reference would it be under?

cheers

SELECT groups.groupID,
			group_types.groupName,
	        group_types.groupImageURL,
	        group_types.groupImageHeight,
	        group_types.groupImageWidth,
            revisions.itemHeading,
	        revisions.itemContent,
	        revisions.itemExtra,
	        revisions.itemImageURL,
	        revisions.itemImageHeight,
	        revisions.itemImageWidth,
	        revisions.itemImageAlt,
	        item_types.typeClass
	FROM    news_item_groups groups,
	        news_item_group_types group_types,
	        news_category_columns cols,
	        news_categories cats,
	        news_items items,
	        news_item_revisions revisions,
		    news_item_types item_types
	WHERE   groups.issueID = '$issueID'
	AND     groups.typeID = group_types.typeID
	AND     group_types.columnID = cols.columnID
	AND     item_types.columnID = cols.columnID
	AND     cols.categoryID = cats.categoryID
	AND     cats.categoryName = 'Sunday Papers'
	AND     cols.columnName = 'News'
	AND     cats.isLIVE
	AND     cols.isLIVE
	AND     items.groupID = groups.groupID
	AND     items.isLIVE
	AND     items.itemID = revisions.itemID
	AND     revisions.isCurrent = 1
	AND     revisions.typeID = item_types.typeID
	ORDER BY groups.displayOrder,
    	     groups.groupID,
	         items.displayOrder,
	         items.itemID

It is an implied JOIN.

In effect it is an INNER JOIN designed to pull a recordset from 3 tables using criteria from various columns in tables.
You should be careful when using an implied JOIN as different databases may treat the query differently depending on the optimisation engine and you could end up with some unexpected results.
So if you are building a cross SQL database platform you should use an explicit JOIN syntax

great thanks for your quick response.

personally. i would never have done like this and would have used explicit JOIN. i dont actually know any differnt. this is in fact given by someone new in the office. He comes from a oracle background.

When you mention,

different databases may treat the query differently depending on the optimisation engine and you could end up with some unexpected results.

could you elaborate a little by what mean? We have a mysql database, with multiple tables but unsure about when you statement would effect me

The database "engine" is how the DBMS "talks" to the database and each engine has different ways of storing and indexing the data.

Jet, InnoDB & MyISAM are engine types. The SQL command text has to be converted into the native commands for the engine. So depending on how the engine algorithm is written for data access the order of the implied JOIN may not be the same in each engine.

Thanks chrishirst,
Any idea what what the unexpected results might be?
Also, Why I should necessarilly use explicit join with MYSQL? By this I mean , isnt there the possibliity that implied joins are more than usable in mySQL if optimisation engine create algorithm suited to implied joins.

Im trying to figure if i should rewrite this query for the sake of getting it right

What may happen with some versions is that you would get more (or less) records returned than expected.
If it gets treated as a CROSS JOIN you could get a recordset with many duplicated entries where the criteria matched.

If it works as expected in MySQL, then it shouldn't be a problem as far as recordsets go, although there may be a performance degredation between the implied and explicit syntax. This may or may not be a major concern depending on how your app is going to scale up in the future.

Personally I prefer to use the explicit syntax. That way I know how the recordset will be returned. There is no ambiguity in what may be interpreted on cross SQL platforms and when I come back to the app months later I will know what was required from the query.
It also improves the "readability" of the code, so if someone else has to alter the code they can see what it does.

thanks chrishirst
youve helped me loads here and much appreciated.
Merry xmas

Jet, InnoDB & MyISAM are engine types. The SQL command text has to be converted into the native commands for the engine. So depending on how the engine algorithm is written for data access the order of the implied JOIN may not be the same in each engine.

There must be an echo in here.

here


here

here