Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

The old INNER JOIN syntax vs. the new INNER JOIN syntax

I was in a presentation last week where the presenter was talking about the ANSI/ISO SQL standards. For the most part the information was correct, but one of the things that was left ambiguous was whether or not the old INNER JOIN syntax was valid (and thus, the audience would be left to assume that it wasn't):

SELECT C.FirstNameC.LastName E.Title
FROM Person.Contact C
HumanResources.Employee E
WHERE C.ContactID 
E.ContactID
  
AND C.LastName 
'Smith'
ORDER BY LastNameFirstName;
    

The old syntax is where the tables are specified in the FROM clause but the JOIN clause is specified in the WHERE clause, like the query above (WHERE C.ContactID = E.ContactID). It is valid ANSI/ISO SQL, and you can find a bit of confirmation for this in Books Online. The reason it's valid is because it's not ambiguous, unlike the OUTER JOIN syntax that was previously used. Now most DBAs and DB developers that I know much prefer the new syntax where the JOIN clause is specified explicitly in the FROM clause using the ON operator like so:

SELECT C.FirstNameC.LastNameE.Title
FROM 
Person.Contact C
  
INNER JOIN 
HumanResources.Employee E
    ON C.ContactID 
E.ContactID
WHERE C.LastName 
'Smith'
ORDER BY LastNameFirstName;
  

The reason I prefer the new syntax is it's cleaner and easier to read, at least IMHO. I can immediately tell there's an INNER JOIN operation going on and I can clearly see the JOIN clause. When the JOIN clause is in the WHERE clause, I have to sift through that portion to determine which exactly is the JOIN clause. On the first query I've given above, that's not terribly difficult, but when the WHERE clause has a lot more to it, it can be a chore to piece together the query properly.

When I queried on Twitter to see who was using the old syntax, I got back a lot of "Kill it! Kill it!" type of responses like someone had just seen a cockroach headed for the 2 year-old's birthday cake. Others pointed out that they see it, but in legacy code, because there's a standing ban on the old syntax for new code. A few acknowledged that it still goes on in their shops (and they were usually quick to point out that it drove them nuts). I'm interested in seeing if there's anyone that still codes using the old syntax and if you do, is there a reason you haven't moved on to the new syntax? Is it because it's an ingrained habit? Or maybe that way is more clear to you because it's what you're used to? Another reason?

 

Comments

Posted by Aaron Nelson on 30 September 2009

allow me to reiterate that sentiment:

"Kill it! Kill it!"

Posted by david.stein on 30 September 2009

A related question Brian.  Does it make any difference whether you use INNER JOIN as opposed to simply JOIN?

Posted by K. Brian Kelley on 30 September 2009

David, as per BOL, if no join type is specified [see the FROM (Transact-SQL) topic] , INNER is used by default. I prefer to use INNER JOIN instead of just JOIN, again, because it makes it clearer and more readable.

Posted by Kerry Tyler on 30 September 2009

Kill it with fire

Posted by David B on 30 September 2009

well I know one problem where I live.  That being that the local University still teaches the old method.  

I have had to correct this behaviour in a few people now :)

Posted by Andy Smith on 1 October 2009

Realistically I'd imagine that syntax will live on forever, or at least until the syntax is no longer valid (and I can't see that happenning any time soon). In most of the shops I've worked certainly new code is as mandated, but existing code wouldn't be changed for the sake of a syntax change. Why would the business sanction a syntax change when there the code works already, there are limited budgets and time, and there are business critical issues to resolve?

Other than that, yes, Nuke it from orbit... and as for teching it that way (!!?)

Posted by Marius Els on 1 October 2009

Brian: From a performance point of view; the syntax is as quick (if not quicker) as the old syntax?

Posted by daniel.surridge on 1 October 2009

Basically the old syntax is equivalent to a CROSS JOIN - why would you code a CROSS JOIN when you want an INNER JOIN?

Posted by Kevin Lewis on 1 October 2009

There's a saying in the UK

Catch it, bin it, kill it...

Actually that's for swine flu but I think the same could apply. It will have to remain supported forever more, but if you're working on a piece of code that's got it in, why not change it.

P.S I always specify INNER JOIN too... other dev's at my work don't though. I think it's more of a personal preference but I like to see it explicitly doing what's it's doing.

Posted by pandeharsh on 1 October 2009

Hi,

All these days,I prefered writing implicit join query i.e.

select a.*, b.*from table a, table b where a.id = b.id;

Now what I understand is explicit join query i.e.

select * from table a inner join table b on a.id = b.id;

is ok,performance wise also.

Posted by andre.myburgh on 1 October 2009

Has anyone tried to work with a query with 10 tables and 30 joined fields and tried to answer the question:

Which tables are being selected from :)

I think the ANSI syntax fails in these situations.

Other than that it is a huge help to understand the joins between tables, but nothing stops you from using both and then causing the person to wrongfully assume there is no other inner joins.

Posted by Azeem on 1 October 2009

Please discuss creation of Reporting Services, Analysis Services,Notification Services & Integration Services in SQL SERVER 2005 step by step by demonstrating an Example on each of these

Posted by sean redmond on 1 October 2009

One should not try to fix something that isn't broken.

If one still has a coworker who still uses it, one would need a good reason to ask them to change it.

The performance is no worse, so this reason won't work. If the younger DBAs who only learnt the new syntax require more time to decipher it, then one might try and ask on the grounds of consideration. It is however good for people to see different of accomplishing tasks.

One could always impose stylistic requirements on the writing of SQL scripts, although take grave exception to being told how their SQL should look.

Posted by Soren Werk on 1 October 2009

I would like to see an example that's a bit more complicated - in the "new" syntax. Something with 3 or 4 joined tables.

Posted by jlewisp on 1 October 2009

I prefer ANSI SQL but when you move the join logic from the where clause to the join clause(s) you are also specifying a join sequence as well.

Posted by salmonelle on 1 October 2009

There's one place, where you must use it: Hibernate HQL if the objects are not mapped to each other. You can only join there if an object is a property of the other. One of those silly quirks that makes me dislike Hibernate...

Posted by dmattison on 1 October 2009

Grow up.  If both work, learn both, use whichever you prefer.  Too many people in this field try to enforce what they like on to others.  There are hundreds of ways to do things in software development(for a reason) why do so many try to enforce the way they chose as "best practice"?

Posted by ToddO on 1 October 2009

The only reasons I can think of to keep it are :

1) legacy scripts

2) The old syntax was more "English-like" and therefore more readable to the novice.

In old syntax, it states both (all) of the data sources up front, directly behind the FROM clause.  It may seem confusing and arbitrary to new SQL users to introduce new data source(s) after the JOIN clause  Most new users do not even think of OUTER JOINs until these are introduced to them, unless they have had previous need to use them.

Posted by Peter W on 1 October 2009

Ok...I confess...yes I still use the old syntax because it's what I've been using since 6.5

The old syntax works and makes sense to me but I will now change my evil ways.

For me this issue is out there with "when you write a letter/email do you start 'Dear XXXXXXX'"

e.g. Old syntax

Dear Burt,

or new syntax

Burt,

either syntax works but Burt might feel more respected with the old syntax however Burt might sense a touch of bromance if the author is male.

Posted by Mike Byrd on 1 October 2009

I agree that the new syntax is more readable, and that is what I use.  Unfortunately almost all of the developers I work with use the old syntax.  When I eventually get their stored procs to tune, I usually end up changing their queries to the new syntax -- alot of work for me, but I've given up trying to change their style.  It is really interesting to look at their queries when there is a mixture of old syntax for inner joins and new syntax for outer (left, right) joins -- now that is hard to read!

Posted by chris on 1 October 2009

All of the MS SQL code that I have worked on uses the new syntax. The majority of MS SQL developers I have worked with just seem to agree that the new syntax is easier to read.

However, we have recently started integrating with Oracle data sources and all the code written by the Oracle consultants uses the old syntax. One day I asked one of them why he preferred the old syntax and he asked me "how else would you do it?" I showed him the new syntax and he told me "that is not valid in Oracle." As it turns out, the new syntax is valid but the database can have issues with it. Specifically, if the tables referenced by a query contain a total of more than 1050 columns then you receive a very misleading error message (ORA1445). So regardless of your preference, if you ever have the opportunity to work with Oracle you will save yourself some headaches by using the old syntax.

Posted by K. Brian Kelley on 1 October 2009

In SQL Server, it should not matter which format you use, as Jack Corbett covers here: wisemanwiseguy.wordpress.com/.../on-vs-where

But dmattison, to continue your line of arguing... most developers prefer proper indentation in order to make code readable. But it works perfectly well if you don't indent. So if you saw code that wasn't indented properly would that be okay to you? What about one that didn't have comments where the coding wasn't immediately obvious as to what that snippet did? Would that be okay?

The reason we include indentation and comments is for readability. The reason most folks prefer the new INNER JOIN format is it makes the SQL code more readable, especially in a complex query. Also, it is similar in structure to the OUTER JOIN syntax.

Posted by Kevin Landry on 1 October 2009

Devil's Advocate (attempt):

1 - Not that this really matters, but having to explain code to someone who does not code (for whatever reason), the old syntax is much easier for them to understand in my experience.

2 - While one could bury the "join" anywhere in the WHERE clause, you could stipulate that it must precede the other elements of the WHERE clause.

OK, enough of that point of view:

Is it not being phased out in a future version of SQL Server anyway (I thought I read that somewhere recently)?

Posted by harv sidhu on 1 October 2009

New join is certainly better for manageability/reading etc. But from a purist perspective, one of the underpinnings of relational databases was "you specify what data you want - not how to get it". The new joins have that navigational aspect that was abhored about the non-relational DBs. Theoretically, there may be multiple potential ways for the optimizer to join those tables depending on available indexes and specified where clauses. The optimizer may have a better method. Just a throwback to the past....!

Posted by Al Castro on 1 October 2009

I still use it because the new syntax seems more difficult and confusing to me when you have a large number of tables and more complex joins (joining to a table more than once or are joining to a subquery, etc.). Most training material or samples provide pretty simple join examples that don't help in those cases.  No doubt this is my "old dog new tricks" problem as I've used to old syntax for many years.

Posted by Kenneth Wymore on 1 October 2009

I agree with most that the new syntax is more readable and that development using the old syntax should cease. The way I handle this is to re-code these joins whenever I encounter them. I don't go out looking for this code nor do I track down the developers who employed it (most do not work for the company anymore anyway). Since the old way of coding these joins seems to be taught less and less and most SQL books teach the new method, it looks like the old style will eventually phase out anyway.

Posted by Cade Roux on 1 October 2009

The old style often opens itself up to accidental cross-joins, because actual join conditions in the WHERE clause can be mixed together in any order with filter-conditions.  If some are relaxed accidentally, a huge problem results.

In addition, changing from an implicit INNER JOIN to a explicit LEFT JOIN is more difficult (unless using the *= operator is used) as it requires rearranging a lot of code.

There are a number of advanced LEFT JOIN techniques where the ON expressions are complex that the implicit join mechanisms are extremely difficult.

From a maintenance and latent defect perspective, there is strong empirical evidence to show that the old syntax is not preferred in systems with large amounts of complex SQL.  I like to see SQL code that is relatively stable in form (evolution of the system appears natural and in proportion with requirements change), and readable, this reduces code smells and gives a high degree of confidence in the system.

Posted by Larry Ness on 1 October 2009

I prefer the new syntax because:

1. It keeps related things (what is being joined and how) together, which always makes code easier to understand, and

2. As Cade mentioned, it simplifies switching between LEFT and INNER join types, which is not uncommon.  I wonder how many people remember the old *= operator?

On a stylistic note, there is one other aspect of the example that I don't like: the use of one-letter aliases.  Single letter variables are the bane of poorly written programs, since the letters are almost completely non-descriptive.  Why should we tolerate them in queries, no matter how much typing they may save the author?  I like to spell everything out.  The only exception might be a well-recognized symbol that has meaning outside of the programming domain, such as X, Y, and Z in a geometric problem or E, I, and R in an electrical application.

Posted by AJ Mendo on 1 October 2009

I guess I am "old school" since when I first starting learing SQL and coding this is what I was taught. I used these types of joins up until 2006 when I was introduced to the "new" format. I agree it is much cleaner and easier to read.

I took some Oracle classes in the mid 90's and this was still being taught through the Oracle University curriculum.

I have not see in in legacy code in our shop but most of the code that is legacy is only from about 2004 to the present.

Posted by David Walker on 1 October 2009

I think that Joe Celko, or Erland Sommarskog, or someone, has given examples where certain things can't be done (or can't be done correctly or unambiguously) using the old syntax.  I don't know exactly where, but I recall reading that somewhere.

Posted by Hobbes on 1 October 2009

Cade Roux said:

"The old style often opens itself up to accidental cross-joins..."

Mod this comment up!  This is especially true when you are joining more than 2 tables.  Also, why mix your critical join conditions with your filters?  For example:

SELECT ...

FROM orders, orderItems

WHERE orders.approved = 1 and orderItems.order_id = orders.id and orders.shippingType = Fed Ex Ground'

Again, it's too easy to lose track of your join conditions.

Posted by Jeff Moden on 1 October 2009

>>> The old style often opens itself up to accidental cross-joins, because actual join conditions in the WHERE clause can be mixed together in any order with filter-conditions.  If some are relaxed accidentally, a huge problem results.

------------------------------------------------------------

Ya know, I've heard that a lot but my observation is that it's done nothing to prevent accidental cross joins.  I'm not even sure it makes them easier to find if there's 30 joins like andre.myburgh stated unless someone follows some pretty tight rules as to what goes where on each side of relational operator.  For example, is this really an INNER JOIN?  Heck no... it's actually an intentional CROSS JOIN and the only reason why I'll sometimes not use the words "CROSS JOIN" is if I need to get simple "splitter" code past a nervous code reviewer.  This is viable working code, by the way...

SELECT ROW_NUMBER() OVER (ORDER BY item.Data,t.Number) AS RowNum,

       item.Data,

       SUBSTRING(item.Data,t.Number,1) AS [Char],

       t.Number AS Pos

  FROM Master.dbo.spt_Values t

 INNER JOIN @t item ON t.Number BETWEEN 1 AND LEN(item.Data)

                   AND t.Type = 'P'

Don't get me wrong... I love the ANSI style as opposed to the old "equi-join" style especially since you can pre- or post-filter depending on whether the filter is in the ON or the WHERE clause.  But, it really doesn't do anything to help people from making bad joins.

Posted by dmattison on 1 October 2009

Readability is SUBJECTIVE.  What is more readable to you may NOT be more readable to others.  As far as indenting of code and comments, that has nothing to do with portability, that is personal style.  ANSI SQL is portable by design, as the ORACLE issues (not supporting inner join) tell you.  For the record, I use Inner join when working with SQL Server (unless it has to support any SQL database), I use the where clause when working with ORACLE.  My point was (and is) is that far too many people in this field try to impose their style and techniques as "best practice" when it is really a personal preference.  For example: Some people like hungarian notation, some people won't use it because Microsoft has now stated "never use it".. There's room for both camps, just be consistent and use what appropriate for the scenario you're in.

Posted by rja.carnegie on 1 October 2009

As you say (October 1, 2009 7:00 AM) Microsoft SQL Server's compiler makes no difference between an INNER JOIN's WHERE conditions and JOIN ON conditions.  And whereas you say "When the JOIN clause is in the WHERE clause, I have to sift through that portion to determine which exactly is the JOIN clause" - arguably it's -all- the JOIN clause.

However, it can be useful for readability if you divide (and can divide) a query into phases of "glue two or more tables together using a relevant key" and "filter rows in the joined table resultset".  And it pays off quite hugely if you decide later that you should write an OUTER JOIN instead.

So while I used to strike a blow against the old syntax by putting all my conditions in a JOIN ON clause, now I'm more inclined to write both an INNER JOIN ON and a WHERE.

I use CROSS JOIN to intimidate code reviewers!  ;-)

Posted by sing4you on 1 October 2009

I used to do joins the old way.  My DBA at the time urged me to adopt the ANSI way.  Well, I was resistant and wouldn't do it.  Then, one day I had a query that simply would not work unless it was done the ANSI way.  I've been a believer ever since:-)

Posted by Steve Becker on 1 October 2009

OK, Devil's Advocate here...

Can someone show me a TESTED new-join equivalent of the following old-join Update statement?

update tableOne

set tableOne.columnX=tableTwo.columnX

from tableOne, tableTwo

where tableOne.commonID=tableTwo.commonID [And tableOne.columnY = Whatever And tableTwo.columnZ = Whatever...]

I am a relatively new user of SQL, so I only learned the new-join syntax. However, after many hours of beating my head on the wall trying to get a new-join statement to perform this kind of update, I gave up and was able to produce the desired results with this kind of old-join statement. In the past, I have posted this issue to three or four other on-line discussions, but none of the new-join suggestions I received in response will run without error, and some of them won't even get past the Query Analyzer or Query Editor parser. The new-join syntax seems to have a problem when you need to select both the records to update and the records from which to derive the new values. Thanks in advance for any TESTED suggestions.

Posted by oddiejbp on 1 October 2009

Yes there was 2 or 3 programmers using "The old way"... but now they no longer work with us... so... inner, left and right joins are the way to go.

I will like to hear experiences about what -dmattison- states. Readability vs Portability.

I didn't had many problems between mysql and sql server, and we used to make distributed queries using Linked Servers and everything was good... when we got rid of mysql and change everything to MSSQL 2005 things where smoother, faster and distributed queries were not that difficult to change. My point is, Portability might not be an issue if you have time to make the change.

Greetings.

Posted by Jorge Baroudi on 1 October 2009

Yes there was 2 or 3 programmers using "The old way"... but now they no longer work with us... so... inner, left and right joins are the way to go.

I will like to hear experiences about what -dmattison- states. Readability vs Portability.

I didn't had many problems between mysql and sql server, and we used to make distributed queries using Linked Servers and everything was good... when we got rid of mysql and change everything to MSSQL 2005 things where smoother, faster and distributed queries were not that difficult to change. My point is, Portability might not be an issue if you have time to make the change.

Greetings.

Posted by oddiejbp on 1 October 2009

sorry for the double post :(

Oh and.. yes there were *=,  >=, <= operators in our queries... so count me in... and i'm not THAT old.

but when we change to MSSQL Server 2005 (Compatibiliy set to 9.0) we had to change it.

:)

Posted by oddiejbp on 1 October 2009

try this:

-- BEGIN UPDATE QUERY

update tableOne

-- NO ALIAS FOR SET

set columnX=tableTwo.columnX

from tableOne, tableTwo

where tableOne.commonID=tableTwo.commonID [And tableOne.columnY = Whatever And tableTwo.columnZ = Whatever...]

-- END UPDATE QUERY

Regards

Posted by mike on 1 October 2009

You mention old and new syntax but both versions have been valid since at least SQL Server 2000... (so that means almost 10 years?)

I am fairly sure it worked in SQL Server 7 as well (the version before SQL 2000).

So what is 'new' here?

Posted by ray on 1 October 2009

I prefer the new syntax because the joins are completely specified in the 'from' clause, instead of partially in the 'from' and partially in the 'where'. To me this makes it easier to be sure my joins are correctly and completely specified, especially in a complex query.

The 'where' clause is then used only for filtering. Again, it is easier to see that the filtering is correct and complete when there is no join info mixed in.

Plus, it just seems more philosophically correct to separate joins and filters this way (though we shouldn't let philosophy get in the way of getting the job done...).

Posted by Frank Quintana on 1 October 2009

I was taught the old way but after a while you adjust to new things. Although habit is a powerful incentive in our profession you better embrace change

Now I do both no problem. Maybe habit makes me think like most of you and agree with Brian and everybody else the " new JOIN" is easier and cleaner.

Anyway the thing is that in real life you will find some legacy code written using the old syntax and you better know how to work with it.

Trust me 5 years from now people will criticize the way we do things today and substitute them with something different and hopefully also better.

Regards

Posted by Alberto Silva on 1 October 2009

SQL Server CE 2.0 used to be more performant with the 'old' joins in the WHERE clause, but since SQL CE 3.0/2005 there is no notticeable difference between JOINing in the FROM or the WHERE clause.

About being 'new' or not, since Access 2.0 that I'm used to do the JOINs in the FROM clause, which helps a lot when dealing with LEFT and RIGHT JOINs.

Posted by Hobbes on 1 October 2009

Update query with a join?  I like to do it this way because it looks like the SELECT version:

UPDATE table1

SET table1.column1 = table2.column2

FROM table1

INNER JOIN table2 ON table2 ...

That way, before you do the update, you can replace the first two lines with a "SELECT *" to see exactly what will happen.

Posted by Steve Becker on 1 October 2009

Hobbes -

Thanks for the feedback. I have gotten as far as your example goes, but your example stops short of what causes the problem, i.e., adding a WHERE clause to qualify either the UPDATE or the JOIN or even two WHERE clauses, with one qualifying the UPDATE and the other the JOIN. This is what I have been unable to do using the new-style join syntax.

Posted by rhouben on 1 October 2009

We have traditionally worked with middleware that depends on ODBC/JDBC/OLEDB/ADO.NET and other technology, often from 3rd party vendors. Also, many of our customers have older legacy databases (we specialize in connecting these to newer technology.) The old syntax works everywhere, the new syntax is, frankly, not portable, so I avoid it unless I know that the app will only need to work with SQL Server.

Posted by Brian Klippel on 1 October 2009

I can't believe this is still being discussed, transact SQL joins are archaic.  I think it's been almost a decade since i've needed to write them.  IMHO, ANSI syntax is much better.  You have better granular control, it reads better, and in some cases (because of the granularity) it optimizes better.  I doubt they will drop support for it anytime soon, but as best practice it should not be used unless you have to write dinosaur code.

Posted by K. Brian Kelley on 1 October 2009

Just for clarification, BOTH forms are valid as part of the ANSI/ISO standard.

Posted by Jane Matheson on 1 October 2009

We have moved from the old syntax even for simple queries.  We have a lot of outer joins and doing all the queries the same way makes the most sense.  It's easier to support and for others to support your code.  We also do it that way because it's much easier to spot where a join isn't completed properly.  We use a query formatting tool so that for most part the developers write the queries with the same syntax.  It is easier to look at a query you've never seen before and figure it out if it's written and styled in similar methods.  We have only changed old queries when needed.  It does take a while for someone who wrote joins the old way to "think" in the new style join.  I've been around long enough that I had to make the transition myself.  

Posted by Hobbes on 1 October 2009

@Steve Becker - This works for me:

create table employee (

ssn char(11) primary key

, officeCity varchar(50)

, email varchar(320)

)

insert employee (ssn, officeCity, email) values ('111-22-3333', 'Atlanta', 'should.update1@example.com')

insert employee (ssn, officeCity, email) values ('222-33-4444', 'Atlanta', 'should.update2@example.com')

insert employee (ssn, officeCity, email) values ('333-44-5555', 'Atlanta', 'should.not.update1@example.com')

insert employee (ssn, officeCity, email) values ('444-55-6666', 'Chicago', 'should.not.update2@example.com')

select * from employee

-- let's say we receive some new email address.

-- update our employee emails only for the

-- Atlanta office, and only those that should

-- be updated (doNotUpdate=0)

create table newData (

ssn char(11) primary key

, email varchar(320)

, doNotUpdate bit

)

insert newData (ssn, email, doNotUpdate) values ('111-22-3333', 'new.email1@example.com', 0)

insert newData (ssn, email, doNotUpdate) values ('222-33-4444', 'new.email2@example.com', 0)

insert newData (ssn, email, doNotUpdate) values ('333-44-5555', 'BAD1@example.com', 1)

insert newData (ssn, email, doNotUpdate) values ('444-55-6666', 'BAD2@example.com', 0)

update employee

set employee.email = newData.email

from employee

inner join newData on newData.ssn = employee.ssn and newData.doNotUpdate = 0

where employee.officeCity = 'Atlanta'

select * from employee

drop table employee

drop table newData

Posted by JoeD on 1 October 2009

I still prefer the old way because it fits the way I think. When I write a query I always picture a cross join between all of the tables in the FROM clause and then I add WHERE conditions to filter out the unwanted records. It works for me, probably because that was the way I was originally taught. Or may be I just have an unusual way of thinking. Anyway, it's faster for me to develop queries this way but I admit, if someone ever comes behind me and tries to read my SQL, they may have trouble if they prefer the new way.

One other thing - I agree with harvs. The new way seems more procedural somehow and I think the old way is truer to SQL's roots as a declarative language.

Posted by david.sirbasku on 1 October 2009

The nex syntax explicitly states the type of join, the objects used and the fields and rules of the join in a special section of the query.  In addition to being more readable to the query writing/maintenance team, this should also make it much easier for a specialist who didn't necessarily write the code to quickly tune performance with a shorter learning curve of the query.  It would seem that having a little more explicit information would be easier for an old school person to learn than a new school person trying to figure out what is going on with less explicit information.

Posted by Julie Breutzmann on 1 October 2009

I vastly prefer the new syntax. The report software that we use (InfoMaker) still uses the old. Sigh!

Posted by ridgie on 1 October 2009

I'm of the "Kill It!" camp but haven't seem any of the old stuff for quite a while now. I partly agree with jdinatal that the old way seems more declarative, and the way I was taught, but the new way is just much easier to read for larger queries.

A further question for you all -

If you have a parameter that affects only the second table in the query, do you put the condition in the WHERE clause or in the ON clause? eg:

SELECT C.FirstName, C.LastName, E.Title

FROM Person.Contact C

 INNER JOIN HumanResources.Employee E

   ON C.ContactID = E.ContactID

   AND E.DepartmentId = @DepartmentId

WHERE C.LastName = 'Smith'

ORDER BY LastName, FirstName;

-- or

SELECT C.FirstName, C.LastName, E.Title

FROM Person.Contact C

 INNER JOIN HumanResources.Employee E

   ON C.ContactID = E.ContactID

WHERE C.LastName = 'Smith'

AND e.DepartmentId = @DepartmentId

ORDER BY LastName, FirstName;  

I'm of two minds, one way logically groups it to the table whereas the other groups all conditions and doesn't "muddy" the join.

There seems to be no difference in performance. Personal preference?

Posted by LBond on 1 October 2009

I always say 'Code for 2AM and stupid'.  I don't want whoever is oncall to have to look at code and wonder what it is doing and why, and then call me for help.  When I'm looking at a 30 table join, I don't want to have to track down the details of a join in a where clause as long as my arm.  And please, please, please, always use a table alias, even if it's just a, b and c.  That way I don't have to figure out which table a particular column comes from.  Code explicitely, and then the next person looking at it doesn't have to guess what it is meant to do.

Posted by Ali Bajwa on 1 October 2009

I must say that sean has given a great example. I will go with option two that groups all conditions. Its more logical and follows a pattern if you understand what I mean!

I have recently started writing SQL with the NEW syntax as I have started working as a SQL Server DBA. Before, I used to work with Oracle. The new syntax seemed very confusing in the beginning but the more I use it the more comfortable I get with it.

I would strongly recommend everyone to learn both styles of SQL Syntax as that is easy (if one gives a real try) and would increase your armoury in the Database world. In my organisation, we have DB2, SQl Server, Oracle, IMS and few other DBMS, Who knows I may be working on DB2 in a couple of years as I have already in the past before.

Learn both styles mates! it is a changing world. (yeah Yeah Yeah, I know you think you will always work with SQL Server, won't you).

Cheers all!!!

Posted by Ron Kunce on 2 October 2009

Brian is correct in intimating that style and readability is all important.  I am all for standards, as long as they are acceptable to those doing the job and promotes ease of understanding when reviewing or maintaining someone elses work. But, that is just it!  

I use both styles depending on the simpilicity of of the code (many joins dictates using the "inner join" syntax) and mostly the preferences of the majoriety of the other coders in the shop.  When working with a bunch of "old-timers" (such as myself) I would opt for the original style of SELECT - FROM - WHERE join syntax, if that is what everyone else is doing.  Or in other words, I try to mimic the style most prevalent, within reason, in the shop I am working, and maybe show them a better way if there is an overriding need.  In this case, since there is little to be gained, I would stick to the accepted (defacto) standard.

Posted by sneman on 2 October 2009

I have seen the old code in legacy applications, whihc makes it very difficult when where caluse also has the boolen conditions in it.

Posted by jim.riedemann on 2 October 2009

One morning many years ago our SQL Server was brought to its knees by a user writing a query using the old syntax. Seems that misuse of parens in his WHERE clause placed all of the JOIN logic on one side of an OR, resulting in a cross join of 16+ tables, most of which contained multiple millions of rows.

We later did the math, and calculated that there would have been more records in the result set than there are atoms in the universe.

Posted by Siva on 2 October 2009

I started using ANSI since 2007 after my Team Lead forced me to use the new one.

But I still feel that something can not be achieved with the new ANSI standard. For ex. JOINING two tables with LIKE condition (not with "=").

Posted by Jack Corbett on 2 October 2009

srivishiva,

You can use LIKE in the ON clause of JOIN.

Posted by Joe Celko on 2 October 2009

I need to do a whole article on this topic.  

For OUTER JOINs you have no choice, but there is a history of propriety syntax that most SQL programmers do not know.  

Likewise the old syntax for INNER JOIN is better when there is an n-ary relationship.  Do not lock yourself into a binary mindset with simple infixed notation that looks like ACCESS (ugh!!)

Posted by Joe Celko on 2 October 2009

I need to do a whole article on this topic.  

For OUTER JOINs you have no choice, but there is a history of propriety syntax that most SQL programmers do not know.  

Likewise the old syntax for INNER JOIN is better when there is an n-ary relationship.  Do not lock yourself into a binary mindset with simple infixed notation that looks like ACCESS (ugh!!)

Posted by Noel McKinney on 3 October 2009

@LBond I love your 'Code for 2AM and stupid' phrase. I think readability can't be overstated. I use INNER JOIN and not just JOIN alone for that reason, it sticks out more if you are quickly looking over code.

Posted by nick.mcdermaid on 4 October 2009

To add to some points here:

1. ANSI syntax is NOT ambiguous with outer joins. Old syntax IS. Thats one obvious good reason for making the change

2. Older versions of Oracle have nasty bugs with ANSI syntax so I think its fun to chuck in some ANSI syntax just to show the Oracle dinosaurs what a painful platform it is.

Posted by theflin on 5 October 2009

I still use it occasionally but it is simply an old habit (I prefer to not say exactly how old). I do remember, usually, to catch it before production implementation but if I don't then code review will catch it and change it. I also have to mention that it was taught in the college here until this fall.

Posted by victor on 7 October 2009

Please kill it.

Try with 30 tables and think.

Posted by Thiru-1063239 on 8 October 2009

I am new to this sqlserver central site. I feel this site and blogs are very useful and i would like to share my knowledge.

thanks

Thiru

Posted by anatoli.jernakov on 20 October 2009

We use the old syntax in some special cases. I personally think it is difficult to read. The reason we still uses it is that it can be used in different versions of MS SQL Server or Oracle or Teradata databases. It is supported almost everywhere. You do not need to convert your query when you going from one server to another. But if we know that query is going to be used only on SQL Server than we prefer the new syntax.

Leave a Comment

Please register or log in to leave a comment.