table join best practice

[jcombs_31]

I'm no DB admin, but I've always written my queries like this to join say 2 tables:

 

SELECT *
FROM tbl1, tbl2
WHERE tbl1.key = tbl2.key

 

What is the difference in using something like INNER JOIN

 

SELECT *
FROM tbl1 INNER JOIN tbl2
ON tbl1.key = tbl2.key

 

There are a few different options with joins but by habit I've always used the first method. What are the benefits of using joins and when is it best to use one over another?

[fenway]

Don't use the first method (also called theta style). -- edit: typo

 

First, it's very hard to mix comma with other JOINs.

Second, commas have different predence than JOINs, which is a nightmare.

Third, you end up munging the where clause with join conditions, which is hard to read.

Fourth, you can't leave off a join condition easily... but leaving off part of a where clause it very easy to do by accident.

Fifth, it's much easier to add extra tables to the second style.

 

Basically, it causes problem and is of no benefit whatsoever.

[bluejay002]

@fenway

 

First, it's very hard to mix comma with other JOINs.

 

I don't get what you are trying to say. Of course, with JOINs, you will not use comma for specifying the joining of tables because that's the way it is. And its not even hard at all.

 

Second, commas have different predence than JOINs, which is a nightmare.

 

you mean precedence? You do not need to use comma separation in specifying tables when you do JOIN.

 

Third, you end up munging the where clause with join conditions, which is hard to read.

 

I never have a problem of having hard to read at all, actually its even more readable (for my case) than without JOIN. Why? Cause you need to specify everything in WHERE clause to see the connection/association where with JOIN you can see right away after you specify the table to connect to.

 

Fourth, you can't leave off a join condition easily... but leaving off part of a where clause it very easy to do by accident.

 

I don't get your point? If you are to specify the connection/association of the tables, why put it on WHERE? you specify it after JOIN with ON. And that's the trouble you get with not using JOIN since you will accidentally get the wrong set of information.

 

Fifth, it's much easier to add extra tables to the second style.

 

Yes, it maybe easier to write since you only need to add a comma and the table name (and alias) but I never had trouble doing some (I don't think it's really that hard).

 

 

Also, you are doing with INNER JOIN for that certain example. The trouble is that when you try switching to another type of JOIN, say LEFT JOIN. doing that with the previous one is more tedious compared with theta style (I didn't know it was called this way, another input, thanks), you only need to replace INNER with LEFT or with anything else (so long as JOIN).

 

Am doing no offense here, am only giving my opinion.

[fenway]

Sorry, I meant to use the second method, not the first... typo.

[bluejay002]

i see... the post has been mod now...

 

ahhh... so the first one was the theta style, copied... thanks for the input!

[fenway]

i see... the post has been mod now...

 

ahhh... so the first one was the theta style, copied... thanks for the input!

No prob, sorry for the confusion.

[jcombs_31]

Well, I appreciate the feedback.  I never paid much attention to the difference between all the joins, but I'll read up on it and see if it makes more sense.  I usually work in relatively small databases so it has never become an issue, but I see a lot of people with all these different join statements so it sparked some curiosity. 

 

 

[bluejay002]

yeah... with such a big databases with normalized tables, JOINs are very useful and burns my brain indeed.