There are two things that immediately stand out to me when I see a query and create concern.
- cross joins
- functions in the where/on clause
While there are other things I might see, these two stand out and usually I can guess there will be issues.
Steve specifically calls out ANSI-89 style joins, which are awful. Before continuing, I agree with Steve’s points, but I figure I’d do a “yes, and” for this one.
When it comes to cross joins, there are specific circumstances in which I’ve written cross joins to great effect. The one that comes to mind most readily is when I need to create the raw data that will let you generate a matrix in a reporting tool.
Let’s say you want to know, for each sales territory and month, the number of new customers that month, the prior month, and the next month. The LAG() and LEAD() functions only work on rows, not intervals. Therefore, LAG() on the July row will show you the row prior to July. That might be June or it might be March, depending on how much data you have.
The solution, then, is to get the full set of months you care about from, say, a calendar table. Then, get the full set of sales territories. Cross join those two and you’ll have the entire range of relevant data. You can then left outer join to the actual data and fill in the gaps.
I have an example of this as a demo script on my Analyzing Business Data with T-SQL talk.