SQL Server Performance Forum – Threads Archive
View Creation Statement – Joins
Hello Everyone… Following is code for a view that I use in a database that someone else wrote. I need help in figuring out how the joins work, and I can’t really tell from teh execution plan. I would liek to add several fields to this view (create another one based upon it), and I want to be sure that I get all the information in. Thanks for looking. Tomislav Here we go: SELECT dbo.Constituents.DisplayName, db
bjComponents.ComponentNumber,db
bjComponents.ComponentName, dbbjects.Dated,db
bjComponents.ReceivedDate, dbbjects.Medium, dbbjects.Dimensions,db
bjTitles.Title, dbbjects.ObjectID, dbbjects.ObjectName, db
bjects.ObjectNumber,dbo.Constituents.ConstituentID,db
bjComponents.SortNumber AS CompSortNumber,db
bjects.SortNumber, CurLoc.Site AS CurSite, CurLoc.Room AS CurRoom,CurLoc.UnitType AS CurUnitType, CurLoc.UnitNumber AS CurUnitNumber,
CurLoc.UnitPosition AS CurUnitPosition, PrevLoc.UnitType AS PrevUnitType,
PrevLoc.UnitNumber AS PrevUnitNumber, PrevLoc.Site AS PrevSite,
PrevLoc.Room AS PrevRoom, PrevLoc.UnitPosition AS PrevUnitPosition,
db
bjects.DepartmentID, CurObjLoc.Handler, CurObjLoc.TransStatusID, CurObjLoc.TransCodeID, CurObjLoc.TempText AS CurrentLocRemarks,
PrevObjLoc.TempText AS PreviouslocRemarks FROM dbo.Locations CurLoc RIGHT OUTER JOIN
dbo.Locations PrevLoc RIGHT OUTER JOIN
db
bjLocations PrevObjLoc ON PrevLoc.LocationID = PrevObjLoc.LocationID RIGHT OUTER JOIN
db
bjects INNER JOINdb
bjComponents ON dbbjComponents.ObjectID = dbbjects.ObjectIDINNER JOIN
db
bjLocations CurObjLocON db
bjComponents.CurrentObjLocID = CurObjLoc.ObjLocationID ONPrevObjLoc.ObjLocationID = CurObjLoc.PrevObjLocID
ON CurLoc.LocationID = CurObjLoc.LocationID LEFT OUTER JOIN
db
bjTitles ON dbbjects.ObjectID = dbbjTitles.ObjectID ANDdb
bjTitles.DisplayOrder = 1 LEFT OUTER JOINdbo.Roles INNER JOIN
dbo.ConXrefs ON dbo.Roles.RoleID = dbo.ConXrefs.RoleID AND
dbo.Roles.RoleTypeID = 1 INNER JOIN
dbo.Constituents
ON dbo.ConXrefs.ConstituentID = dbo.Constituents.ConstituentID ON
db
bjects.ObjectID = dbo.ConXrefs.ID AND dbo.ConXrefs.DisplayOrder = 1
You can view the view in graphics form. In that way you can find out how is working.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
You can make the list of all tables use in Joins and then check the schema for those tables and then you can add them in your select statement to add any more relevant field. Or as Luis suggested open EM and goto views and then open the corresponding view in design mode there you will get the graphical plan of your view.
I’m with Luis- have a look at the view design in Enterprise manager…a picture is worth a thousand words! (plus it makes it easy to add any new columns!!) ‘I reject your reality and substitute my own’ – Adam Savage
Thank you for your replies. I opened EM and looked at the graphic display of the view, and at least to me, it is still very confusing. What I am trying to figure out (rightly I hope) is the order of execution of the joins. How do the joins work without having an ON statement after each? How do multiple ON statements together work? I am so confused!
Well, the view is probably shown as a graphic with the joins intact, but if you try to open (or run) this view, you should get error messages like "The column prefix ‘<tbl_name>’ does not match with a table name or alias name used in the query." This is because in T-SQL you must have the ON clause immediately after the relevant JOIN. In any ON clause, you can refer to any table/view that is mentioned in the same FROM clause, but strictly before this ON clause: if the table is JOINed after this ON clause then you get the same error message. That should give you some idea of the order in which JOINs are being evaluated by the database engine.
Well, I wrote out how each table is joined and what the filters are for each join (since there are a couple). Adriaan, when I run this script in Query Analyzer, it comes up with the correct number of rows, which is basically all of the rows in the Objects Table, since no parameters have been passed to it via our application. I have seen the execution plan, done the showplan_all thing, but it still doesn’t make sense. I’m a relative newbie to SQL programming, even though I’ve been exposed to it for over two years. I wish this was as simple as using nested if-then-else loops in understanding the logic of the script. Perhaps I am approaching understanding this script in the wrong manner. Thanks for your all of your help, and I will continue to try and understand this view script, until I can alter it to include a few more fields form other tables in our database.
Strange – what version of Microsoft SQL Server are you using?
SQL Server 2000
Typically even experienced TSQL programmers try to avoid joining more than 5-6 tables together because of the pain that it causes in the brain trying to understand it all. I can’t imagine what it would be like to be new and trying to understand exactly what is happening in this query with 9 joins. The general reason that joins are used is to pull information from multiple tables. I want to see the employees and the name of the department they are in so I JOIN the employee table to the department table. With 9 of those going on it would be enough to pull your hair out. In your case the coder made it more complicated by using the same table (Locations) twice: FROM dbo.Locations CurLoc RIGHT OUTER JOIN
dbo.Locations PrevLoc RIGHT OUTER JOIN What would help you is if you sat down and printed out some data from each of the tables and get to know what they contain first. Then start using some examples and say "Ok I’m going to start with this value from the Locations table. Then look at the ON conditions "ON CurLoc.LocationID = CurObjLoc.LocationID" and see which field of your Locations row is needed, what the value is, and then what row in the other table you’ll be looking at. Don’t think about finding them all, just thinking about tracing 1 row through the entire thing, starting 1 JOIN at a time.
Thanks for the information druer. I’ve started looking at the output from a SHOWPLAN_TEXT setting, and I think that I’m starting to understand some stuff (of course, this is wishful thinking on my part). One of the many things that confuses me about this script is the use of multiple Right/Left Outer Joins and the Inner Joins. I wish that I could somehow redo the entire script with only Left Outer or Right Outer Joins and Inner Joins…I think that it would be much simpler to read and add other tables if necessary. One question for everyone…If the script has a Right Outer Join between two tables, can it be changed to a Left Outer Join between the two tables? In this script, there is a spot where that happens. the original script is: Locations PrevLoc Right Outer Join ObjLocations PrevObjLoc ON PrevLoc.LocationID=PrevObjLoc.LocationID and the changed script is: Locations PrevLoc Left Outer Join ObjLocations PrevObjLoc ON PrevLoc.LocationID=PrevObjLoc.LocationID I am curious why this happenned. Thanks again for looking. Tomislav
The script changes are shown in the SHOWPLAN_Text output.
You use an inner join when you know there is a full match between tables, or when you want matching rows only. You use an outer join if you can’t be certain there is a match, for example when the column on the inner table is nullable. If you select an inner join in this case, then you won’t see rows returned from the inner table that have no match: with an outer join these rows are returned, with a NULL value on columns from the outer table. This is a technique you can use to find unmatched rows: WHERE OuterTable.Column IS NULL. RIGHT or LEFT depends on which table is the inner table, and which one is mentioned first in the FROM clause. Imagine that the join is an arrow pointing from the inner table towards the outer table, hence RIGHT or LEFT. I personally tend to write everything as LEFT – you’re creating branches, so working in one direction is both natural and clear. Once you stop using the graphical view editor in EM, and just write your queries in QA, you’ll also be free of the redundant keyword OUTER.
]]>