Query Building Tips (Need Them!)

Question

Post reply

Query Building Tips (Need Them!)

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

robertafricker Mr or Mrs. 500 Points: 561 More actions · Answer 1

Hello GS,

I apologize for the late response, I have been piled up big time...:exclamationmark:

I will look over your info tomorrow and respond.

Thank you again for your continued input...

-Roberta-

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

I think this is a nice little article illustrating a good, systematic method for attacking complicated queries:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx

---------------------------------------
elsasoft.org

robertafricker Mr or Mrs. 500 Points: 561 More actions · Answer 3

HI jezemine,

Thank you for the article, it is much closer to the idea that I've been having about "breaking it down" piece by piece.

At this moment, I am going to submit another post titled, "Interpreting the Query."

If you have a moment, please take a look and respond.

In the meantime, I will look over your article more closely, and I'd like to ask you a couple of questions about it.

Talk with you soon....;)

-Roberta-

jezemine SSCrazy Eights Points: 8059 More actions · Answer 4

however much I'd like to take credit for that article, it's not mine. 🙂

Jeff Smith wrote it - his blog is very good btw.

---------------------------------------
elsasoft.org

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 5

I have learned that if Gail says it, it must be true.

So when I trip over something she says, I worry that I am misunderstanding something. She said:

"It's not recommended to do joins in the where clause any more. Especially since, in 2005 and higher, the old style outer join (*=) does not work."

The original query used the form FROM tableA,tableB, which yields a cartesian product which gets pruned by the subsequent WHERE clause. (No debate on depreciating the old style outer join notation, and in the case under discussion, as you suggested, an INNER JOIN is clearer.)

But I think the phrase "joins in the where clause" is misleading, the join is being done in the FROM clause.

I find cartesian product joins extremely powerful and useful in many situations, and I don't understand why I should replace one keystroke (",") with 12 (" CROSS JOIN ".) Is the "," construct now being discouraged?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

I wasn't speaking about the occations when yuo need a cross join. If you need a full cartesian product, then just list the tables. Dunno about you though, but I very seldom have a query that it a true cartesian product.

By join, I'm refering to the Limiting of a potential cartesian product by specifying the linking columns.

What I meant is that this style is not recommended any more. Among other reasons, it can get difficult to read and it's all to easy to miss a join.

SELECT Column1, col2, col3

from Table1, table2 -- from clause has no joins. It's a straight list of tables

WHERE Table1.col1 = Table2.col2 -- join is done in the where clause

More encouraged is the following.

SELECT Column1, col2, col3

from Table1 INNER JOIN Table2 ON Table1.col1 = Table2.col2 -- join is explicit on what tables and is in the from clause

Make any sense?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 7

"Dunno about you though, but I very seldom have a query that it a true cartesian product."

I have many (well at least some) that are important.

Consider a table of employees e with start dates and end dates. If you need to calculate headcounts on the first of every month for the past 7 years, you build a table d of 84 dates (1/1/2002 ... 12/1/2008), cross join (I'd use a comma) the two tables, in the where clause, specify d.date between e.hireDate and e.lastDate, and use groupby and sum(*) to get the counts.

I would have changed your comments to:

SELECT Column1, col2, col3

from Table1, table2 -- cross join (cartisian product) of tables

WHERE Table1.col1 = Table2.col2 -- select matches of interest

Make any sense?

Understand, but are you suggesting that "," as a join operator is now discouraged?

Thanks for taking your time to contribute!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jim Russell (4/11/2008)
Understand, but are you suggesting that "," as a join operator is now discouraged?
Thanks for taking your time to contribute!

Actually - it has for some time, since it can lead to ambiguous statements. There is a slow but steady push towards using ANSI notation for joins, since it disambiguates what should be used as part of defining the relation/join, and what should be used to limit rows.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?