Why would a SQL query JOIN on the same table twice with the same condition?

Question

I'm working on changes to a SQL Server v8 database developed by someone else, and have found something that seems (based on my limited SQL knowledge) strange and pointless.
One of the views has a query that does a LEFT OUTER JOIN twice on the same table with the same condition. Is there any reason for doing this?

The query is below. See the second- and third-last lines that both join the "te_SDE_Survey" table on the "SDE_ID" field. Also note these lines set two different aliases for the table, and both aliases are used in the SELECT part of the query.

SELECT     vs.SLMS_Code, vs.Retail_Date, vs.TagNo, vs.Rego, vs.Model, vs.Company,
           vs.AccountType, viqdp.SDE_ID, bd.Debit_Date, isu.Survey_Date, 
           CASE
             WHEN isu.Q6 IS NOT NULL THEN isu.Q6
             ELSE CASE WHEN returned_surveys.survey_date IS NULL THEN
               CASE WHEN (viqdp.expiryDate < getdate() AND cs.sup1 IS NULL AND cs.sup2 IS NULL
                   AND cs.sup3 IS NULL AND cs.sup5 IS NULL AND cs.sup8 IS NULL AND cs.sup9 IS NULL) THEN 'E'
                 WHEN (viqdp.expiryDate < getdate() AND cs.sup1 = 'F' AND cs.sup2 = 'F' AND cs.sup3 = 'F'
                   AND cs.sup5 = 'F' AND cs.sup8 = 'F' AND cs.sup9 = 'F') THEN 'E'
                 WHEN cs.sup1 = 'T' THEN 'S'
                 WHEN cs.sup2 = 'T' AND (cs.sup8 = 'F' AND cs.sup9 = 'F') THEN 'D'
                 WHEN cs.sup3 = 'T' AND (cs.sup8 = 'F' AND cs.sup9 = 'F') THEN 'D'
                 WHEN cs.sup5 = 'T' AND (cs.sup8 = 'F' AND cs.sup9 = 'F') THEN 'D'
                 WHEN cs.sup8 = 'T' AND (cs.sup2 = 'F' AND cs.sup3 = 'F' AND cs.sup5 = 'F') THEN 'E'
                 WHEN cs.sup9 = 'T' AND (cs.sup2 = 'F' AND cs.sup3 = 'F' AND cs.sup5 = 'F') THEN 'E'
                 WHEN (cs.sup8 = 'T' OR cs.sup9 = 'T') AND (cs.sup2 = 'T' OR cs.sup3 = 'T' OR cs.sup5 = 'T') THEN 'S'
               END
               WHEN (tey.survey_expire_method = 'pre2008') THEN
                 CASE WHEN (datediff(month, viqdp.generate_date, returned_surveys.survey_date) > 1) THEN 'E' END
               WHEN (tey.survey_expire_method = 'expiryDateColumn') THEN
                 CASE WHEN (returned_surveys.survey_date > viqdp.expiryDate) THEN 'E' END
             END
           END AS score_or_exclusion_status,
           CASE WHEN (bd.explanation IS NULL) THEN '' ELSE bd.explanation END AS explanation,
           tey.te_Year
FROM       dbo.te_Vehicle_Sale vs INNER JOIN
           dbo.te_Year tey ON vs.Retail_Date >= tey.Start_Date AND vs.Retail_Date <= tey.End_Date LEFT OUTER JOIN
           dbo.Bad_Data bd ON vs.TagNo = bd.TagNo LEFT OUTER JOIN
           dbo.te_Vehicle_SDESurvey viqdp ON vs.TagNo = viqdp.TagNo LEFT OUTER JOIN
           dbo.te_SDE_Survey isu ON viqdp.SDE_ID = isu.SDE_ID LEFT OUTER JOIN
           dbo.te_SDE_Survey returned_surveys ON viqdp.SDE_ID = returned_surveys.SDE_ID LEFT OUTER JOIN
           dbo.te_SDE_Contact_Suppression cs ON viqdp.SDE_ID = cs.SDE_ID

Answer 1

There is no reason to do this. This is probably the result of a combination of poor and lazy coding practices.

Joins should not be placed at the end of a line of SQL code. Preferred method:

 SELECT *
 FROM Main_Table
 INNER JOIN Secondary_Table as Sec on Main_Table.ID = Sec.FK

There was probably a secondary comparison in the ON clause of one of the joins for te_SDE_Survey, then a programmer figured out that was no longer needed. The programmer probably removed the second part of the ON clause, and did not notice (in haste) that the table was already joined up with in an equivalent fashion due to the sloppy code

Answer 2

Additionally to hamlin11's answer, it could be that it was done to highlight the huge wall'o'text in the middle was separate from the required field from the same table. Just a different form of highlighting a difficult block of text.

While it's tedious on the join, depending on the size of the tables involved this might not even be a noticeable concern. Does that join cause lots of grief in the profiler?

Answer 3

It might be incorrect. Perhaps it should have been joining on a different FK. If it is correct, it is possibly redundant, however, it may be because originally it joined to a different table/view for that data or joined on a different key and inorder to avoid having to update prefixes, this was done.

In addition, if this is not a one-to-one relationship, you will get different results, since each LEFT JOIN would result in multiplication of results. That would be very difficult to see being right in this case, but it does mean that the one-join and two-join versions are not strictly equivalent in the face of one-to-many relationship.

I have never seen SQL Server Management Studio reformat my code unless it is in the view designer. Certainly, ALTER VIEW/PROCEDURE/FUNCTION preserves formatting, because I see it all the time in INFORMATION_SCHEMA.ROUTINES or syscomments.

Typically, I use SQL Prompt to reformat my code consistently.