How does joining without 'JOIN' work?

[pdbowling]

Hello, Everyone.

I am using SQL Server 2008.

I am reviewing code from a previous developer and I do not understand something I am seeing. I get the concept but the specifics of how it works are escaping me. I was trained to use JOIN to join tables, not list tables and force comparison in a where clause.

SELECT myFieldList
  FROM table1 t1, table2 t2, table3 t3
 WHERE t3.Foreign_Key_1 = t1.Primary_Key_1
   AND t3.Foreign_Key_2 = t2.Primary_Key_2

The tables don't appear to be in the right order. How is the compiler sorting this out?

table1 and table2 are listed first and aren't even being tied together. Is it doing a cross-product first and then narrowing by table3 conditions? Seems a horrible waste of resources.

Why would someone do this?
Is there an advantage doing it this way?
Is is good/bad form?

I would right thus:

SELECT myFieldList
  FROM table3 AS t3
  JOIN table1 AS t1
    ON t3.Foreign_Key1=t1.Primary_Key1
  JOIN table2 AS t2
    ON t3.Foreign_Key2 = t2.Primary_Key2

but since I don't understand the first one, I am not convinced 100% that it would be doing the same thing.

Thought?
Thanks all.
Patrick

 

[yelworcm]

The short answer: Poorly.

The longer answer: The first syntax is a hangover from years ago, when not all implementations of SQL supported JOIN syntax. You will find that syntax will not allow you to do outer joins, and makes it much more difficult to detect unintended cartesian joins.

 

[gmmastros]

Previous versions of SQL Server supported outer joins with that syntax, but newer versions do not, unless you set the compatibility level of the database appropriately.

For example, in SQL2000, you could run this query.

Select  *
From	Student, School
Where	Student.CurrentSchoolId =* School.SchoolId

The query above behaves like an outer join.

In SQL2005, the same query would generate this error.

[tt][red]Msg 4147, Level 15, State 1, Line 4
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.[/red][/tt]

Regardless, the syntax using an equal operator is still supported.

Without a where clause, the query performs as a cross join (a cartesian product that yelworcm mentioned).

Select *
From	Student, School

is the same as:

Select *
From	Student
        Cross Join School

When you use a where clause with an = operator, it's the equivalent of an inner join:

[code]
Select *
From	Student, School
Where   Student.CurrentSchoolId = School.SchoolId

is the same as:

Select *
From	Student
        Inner Join School
          On Student.CurrentSchoolId = School.SchoolId

Also that same as....

Select *
From	Student
        Cross Join School
Where   Student.CurrentSchoolId = School.SchoolId

So.... yes... your two original queries are guaranteed to return the same data. They will also have an identical execution plan, which means they will perform the same. Personally, I ALWAYS use the join syntax because I find it easier to understand the code. For me, the ON clause follows the join clause so the relationship between the tables is obvious. You don't need to look in the where clause to find it.

SQL allows for some weird queries sometimes. For example, this code...

Select *
From	Student
        Inner Join School
        Inner Join District
          On School.DistrictId = District.DistrictId
          On Student.CurrentSchoolId = School.SchoolId

The code shown above actually runs and generates the expected results. I don't recommend it because it is more difficult to understand. Writing queries that are easily understood is important.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

No matter if the compatibility level is set to allow outer joins with =* or not, in this case it's not used, it's an inner join of all the tables, which is also a reason the order of the tables doesn't matter at all. You can also write your query beginning with t1 and would get the same result. Otherwise, what the others already said explains this usage. Perhaps it's just habit.

The optimisation of this is not necessarily worse nor better than a JOIN query. It's not, that the where caluse only is applied after the records are joined or, as in this case, form a cartesian product. The conditions can already limit the creation of the resultset during building it, no matter if they are join or where conditions.

Bye, Olaf.

 

[r937]

Writing queries that are easily understood is important.

cannot emphasize this enough

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[lameid]

Just a little more information...

The way the query is written is ANSI-89 SQL compatible (as in 1989) and the version you are using is ANSI-92 compliant. So the difference is about 3 years from about 20 years ago...

Clearly there is a consensus having the Join and ON in the from clause is easier to read by humans and if you have a reason to touch it, you might as well update it.