SQL - What type of join is it and the best way to rewrite?

Question

Our 3rd Party app has a custom View with the following:

FROM dbo.vwPositionAssetSubWeight 
INNER JOIN dbo.vwPositionAssetSubTotal 
    ON dbo.vwPositionAssetSubWeight.AssetID = dbo.vwPositionAssetSubTotal.AssetID 
FULL OUTER JOIN dbo.vwPositionAssetPendingTrades 
    ON dbo.vwPositionAssetSubWeight.AssetID = dbo.vwPositionAssetPendingTrades.AssetID
, dbo.vwPositionPortfoliosTotal

Note: There is no where clause.

Some of this mess makes sense, but I'm not familiar with how the last view is joined or maybe not joined. Is dbo.vwPositionPortfoliosTotal a cross join on everything? That is my guess. Also, dbo.vwPositionPortfoliosTotal returns one row.

What is the best way to rewrite this, so it makes sense to the next developer?

Oh, no aliases, 111 returned fields, no documentation, remarks, hints or even a trail of bread crumbs.

Answer 1

Using a comma in the FROM clause of a SELECT statement is the same as a Cartesian product (cross join).

For example,

SELECT *
FROM a, b

will match every record from a with every record from b. It is equivalent to

SELECT *
FROM a
CROSS JOIN b

Answer 2

The dbo.vwPositionPortfoliosTotal's join criteria will be found in the WHERE clause so in order to tell what it is doing you will have to post that part. Look for where it says things like dbo.vwPositionPortfoliosTotal.[column] = dbo.[table].[column]

Edit:

As the accepted answer says, without a WHERE this is a CROSS JOIN.

Answer 3

This is indeed a cross join. If you really want to make it clear, just replace the comma by "CROSS JOIN".

Answer 4

Yes the last part ', dbo.vwPositionPortfoliosTotal' is a cross join on everything between what's before the comma and dbo.vwPositionPortfoliosTotal, but it depends also on the WHERE part.

To see if you can rewrite this cross join (remove it for example), you have to show what fields are retirved from vwPositionPortfoliosTotal and what conditions are used.

Answer 5

These two statements are equivalent.

SELECT *
FROM a, b

SELECT *
FROM a CROSS JOIN b

Examine your where clause... there might be joining criteria hiding in it that would cause you to choose inner/outer joins instead.