Reputation: 8043

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

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.

Upvotes: 1

Answers (5)

Donald Byrd

Reputation: 7778

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.

Upvotes: 2

Amy B

Reputation: 110101

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.

Upvotes: 2

manji

Reputation: 47978

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.

Upvotes: 2