join information 2

[crystalized]

I am wondering what the difference is if I use the join statement or leave the join statement out.

For example joining table1 and table2 on id1 and id2 can be done as:

Select table1.*,table2.*
from table1,table2
where table1.id1=table2.id2

This achieves the result set that I want, but I know that you can also use a join. Can anyone tell me what advantage I gain by using the join (especially for composite keys to be joined on).

I would also point out that I prefix all table field names with an identifier that lets me easily recognize the table (ie tblFirm has frID). This means that none of the joined fields have the same names. Would this mean that I still have to identify the joined fields in the join statement.

Does the join recognize the relationships that I have created and join on the fields identified in the relationship?

I know this is actually a series of questions but I am hoping to start improving my stored procedures. Thanks in advance for any insight that anyone can offer. [sig]<p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>
[/sig]

 

[foxdev]

where table1.id1=table2.id2
is a join, just different syntax than the [tt]...join Table2 on ...[/tt] method.

No difference in effect, but one is more standard (ANSI) than the other. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[crystalized]

Which is more standard ansi
my example or using the join syntax?

Also do you know if there are any performance differences when using the different syntax. I have to admit I am more comfortable using the syntax I showed, but if the performance is better with the join syntax then I will endeavour to learn the join syntax. [sig]<p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>
[/sig]

 

[foxdev]

Putting the join condition in the FROM clause is the SQL-92 standard method. In SQL Server 7, there is not supposed to be any difference in performance between this method and specifying the join condition in the WHERE clause. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[foxdev]

And, if you need to specify an outer join, the FROM clause syntax is probably the only way to do it (I know Oracle allows the += and similar operators). [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[crystalized]

Thanks a bunch Robert, I appreciate the info [sig]<p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>
[/sig]

 

[jnicho02]

foxdev,
you can do an outer join with *=

So far I have only found one difference between using '=' or the 'join' statement. I seem to remember that you can only specify multiple outer joins using the join statement (or something like that).

I think maybe that using 'join' to specify joins and 'where' to specify limits on the selection is quite logical. It might help to avoid cartesian products when someone forgets a join. [sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]

 

[foxdev]

Thanks...I knew Oracle supported the &quot;*=&quot; syntax, but I've never tried it in MS SQL Server.

For you ODBC folks, you can specify an outer join that is interpreted by the ODBC driver using the {oj} syntax. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[Ruairi]

actually *= is not the same as the outer join syntax. It was an earlier attempt to support outer joins in SQL Server, and is still recognized but it does not always give the same results. it should NOT be used. [sig][/sig]

 

[jcrivera]

Did Oracle change the syntax for outer joins? I always used (+) at the right side of the parent table's column.

On my opinion, if you get used to specify an inner join by writing JOIN table2 ON... it's a good practice, because sometimes u would like to change the query to an outer join very easily (specially when using the Query Analyzer.

Also, I would like to answer Crystal's question on specifying the join condition even if the column names are not called the same... if you're joining two tables, you MUST specify by which fields you're joining them. Otherwise, you'll get a very huge number of records as the rowset. For example, let's say table1 has 20 records and table2 has 30. table1 has id1 and table2 has id2 and table2 is a child table on table1 that is related by id1 and id2.
If you attempt
SELECT id1, id2 FROM table1, table2

you will get 600 records as rowset, while if you say
SELECT id1, id2 FROM table1, table2
WHERE id1 = id2
(which is actually the same as saying
SELECT id1, id2 FROM table1 JOIN table2 ON (id1 = id2) )

your rowset will be reduced to those you want.

The only advantage of having this field names called distinct is that you dont have to specify the table name that correspond to each field, for example
SELECT table1.id1, table2,id2
FROM table1, table2 WHERE table1.id1 = table2.id2
(this is unnecessary)

J.C. [sig][/sig]