Inner Join Vs Where

stauf
05-28-2002, 10:06 AM
In SQL Books online I don't see any difference between the clause where and the clause inner join.
Is there an issue with inner join if the format is not the same between the 2 compared fields .. like if 1 field is string(30) and the other is string(32) is there a problem ?

pinster
05-28-2002, 10:15 AM
I don't understand your question but Inner Join is used when you want to retrieve data from more than 1 table... and it's commonly use when you have a relational database that share a common field (Key)...

while the Where clause is used when you want to retrieve data that match certain criteria...

Anis
05-28-2002, 10:18 AM
Check out this Article (http://www.devguru.com/Technologies/jetsql/quickref/inner_join.html)

10stone5
05-28-2002, 10:31 AM
Inner join is used to set 'one-to-many' relationship between two tables. You can also do 'Outer' joins - 'many-to-one' and 'many-to-many'.

'Where' clause is used to set querying criteria in as little as one table.

Ogey
05-28-2002, 11:34 AM
I believe what Stauf is referring to is the fact that you can get the same results from a Where clause (old way) as you can from an Inner Join (new way).
For example, these two queries would give you the same thing...SELECT *
FROM TableA, TableB
WHERE TableA.SomeId = TableB.SomeIdSELECT *
FROM TableA
INNER JOIN TableB
ON TableA.SomeId = TableB.SomeId
Stauf, essentially there is no difference in creating a join in either of these ways. Your specific question relating to comparing two fields of different length (varchar(30) and varchar(32)) has the same answer for both methods. The answer is that the query will still run fine. Of course, if there are values in the varchar(32) field that have more than 30 characters, it would be impossible for them to match up with the other field.

All that being said, I think it is MUCH better to use an INNER JOIN clause instead of a WHERE to get your results. Joins are the preferred way of dong things now because it is more obvious what you are trying to accomplish, it is compatible with OUTER JOIN clauses (which you can not easily duplicate with a WHERE clause), and probably some other reasons I can't think of. In terms of performance, I believe it won't make a difference either way. If it does, it would be negligible unless you're creating an extremely fast application.

10stone5
05-28-2002, 11:38 AM
Originally posted by Ogey
I believe what Stauf is referring to is the fact that you can get the same results from a Where clause (old way) as you can from an Inner Join (new way).

Right - the above is from SQL '92.

lebb
05-28-2002, 12:38 PM
Just an additional comment about the "old" vs. "new" syntax. I believe (somebody correct me if I'm wrong) that the INNER JOIN/OUTER JOIN syntax is exclusive to Microsoft's database products (SQL Server & Access), and is not standard SQL. For instance, using Oracle, you would need to use the WHERE clause for your joins. That's something you might need to consider if your app needs to run across multiple database platforms.

10stone5
05-28-2002, 12:41 PM
Yes SQL '92 [as well as Access SQL] is not ANSI SQL >>

Thinker
05-28-2002, 04:29 PM
Yes SQL '92 is not ANSI SQL

HUH? the correct term would be ANSI SQL 92. It is the
ANSI standard. There is also an ANSI SQL 99 standard.
There are three levels of conformity to the 92 standard, and very
few database vendors conform to the highest level. As far as I
know, Inner Join is a part of this ANSI SQL 92 standard. I can't
confirm it, because ANSI charges for copies of its standards. But
everything I read indicates it is part of the standard, and using
the where clause is the older syntax.

Thinker
05-28-2002, 05:02 PM
Apparently, with Oracle9i, the standard inner and outer join
syntax is now supported. Look here...
http://download-east.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/queries2.htm#2054064

10stone5
05-28-2002, 05:18 PM
Thanks for clearing that up Thinker ! -> here's an informative one page synopsis on developing ANSI SQL standards.

http://www.il.postgresql.org/users-lounge/docs/7.1/tutorial/sql.html

Thinker
05-28-2002, 05:28 PM
Good link! :)

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum