|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
difference between inner join and 'where table1.id = table2.idI want to return all rows from two tables where the primary key of one table
equals the foreign key of another. So if CustomerID in the customer table has CustomerID 1 - n, I want to only return rows where CustomerID's 5, 9 and 766 are found in the EmerilLiveKickitUpaNotch Tasters table where there are CustomerID foreign key matches. What is the difference between an inner join of those two tables and a simple select query using the where clause... where Customer.CustomerID = Tasters.CustomerID. Thanks, -hazz ANSI JOIN vs. OUTER JOIN
http://support.microsoft.com/newsgroups/default.aspx ANSI Joins http://www.databasejournal.com/features/mssql/article.php/1438001 AMB Show quote "hazz" wrote: > I want to return all rows from two tables where the primary key of one table > equals the foreign key of another. > So if CustomerID in the customer table has CustomerID 1 - n, I want to only > return rows where CustomerID's 5, 9 and 766 are found in the > EmerilLiveKickitUpaNotch Tasters table where there are CustomerID foreign > key matches. > > What is the difference between an inner join of those two tables and a > simple select query using the where clause... where Customer.CustomerID = > Tasters.CustomerID. > > Thanks, -hazz > > > Thank you Alejandro. Thanks for your help on such a basic question. -hazz
Show quote "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message news:C8A4EE3D-4B58-4E6B-BEE9-6C050C21E0C5@microsoft.com... > ANSI JOIN vs. OUTER JOIN > http://support.microsoft.com/newsgroups/default.aspx > > ANSI Joins > http://www.databasejournal.com/features/mssql/article.php/1438001 > > > AMB > > "hazz" wrote: > >> I want to return all rows from two tables where the primary key of one >> table >> equals the foreign key of another. >> So if CustomerID in the customer table has CustomerID 1 - n, I want to >> only >> return rows where CustomerID's 5, 9 and 766 are found in the >> EmerilLiveKickitUpaNotch Tasters table where there are CustomerID foreign >> key matches. >> >> What is the difference between an inner join of those two tables and a >> simple select query using the where clause... where Customer.CustomerID = >> Tasters.CustomerID. >> >> Thanks, -hazz >> >> >> The inner join will allow you to return data from *both* of the tables
"joined" together. ie. SELECT table1.id, table2.id, table1.name, table2.numberoforanges FROM table1 INNER JOIN table2 ON table1.id = table2.id Show quote "hazz" <greghazzard@nospamcomcast.net> wrote in message news:OPVIg1LfFHA.3256@TK2MSFTNGP12.phx.gbl... >I want to return all rows from two tables where the primary key of one >table equals the foreign key of another. > So if CustomerID in the customer table has CustomerID 1 - n, I want to > only return rows where CustomerID's 5, 9 and 766 are found in the > EmerilLiveKickitUpaNotch Tasters table where there are CustomerID foreign > key matches. > > What is the difference between an inner join of those two tables and a > simple select query using the where clause... where Customer.CustomerID = > Tasters.CustomerID. > > Thanks, -hazz > I assume you're referring to the difference between:
select * from tbla, tblb where tbla.id = tblb.id and: select * from tbla inner join tblb on tbla.id = tblb.id .... The former is an older syntax, but logically they are equivalent. Personally, I like the newer syntax a lot more. I find it easier to read and understand, especially when working with complex queries. In addition, the older syntax does not play well with outer joins. I recommend getting used to the new syntax as soon as possible. Show quote "hazz" <greghazzard@nospamcomcast.net> wrote in message news:OPVIg1LfFHA.3256@TK2MSFTNGP12.phx.gbl... > I want to return all rows from two tables where the primary key of one table > equals the foreign key of another. > So if CustomerID in the customer table has CustomerID 1 - n, I want to only > return rows where CustomerID's 5, 9 and 766 are found in the > EmerilLiveKickitUpaNotch Tasters table where there are CustomerID foreign > key matches. > > What is the difference between an inner join of those two tables and a > simple select query using the where clause... where Customer.CustomerID = > Tasters.CustomerID. > > Thanks, -hazz > > Thank you Adam. I really had no idea they were logically equivalent! I do
use the new syntax but since I didn't appreciate the distinction, or lack thereof, I used them sometimes in one mood and sometimes another. Thank you for the clarification. -hazz Show quote "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:ODxS$7LfFHA.3936@tk2msftngp13.phx.gbl... >I assume you're referring to the difference between: > > select * > from tbla, tblb > where tbla.id = tblb.id > > and: > > select * > from tbla > inner join tblb on tbla.id = tblb.id > > > ... The former is an older syntax, but logically they are equivalent. > Personally, I like the newer syntax a lot more. I find it easier to read > and understand, especially when working with complex queries. In > addition, > the older syntax does not play well with outer joins. I recommend getting > used to the new syntax as soon as possible. > > > -- > Adam Machanic > SQL Server MVP > http://www.datamanipulation.net > -- > > > "hazz" <greghazzard@nospamcomcast.net> wrote in message > news:OPVIg1LfFHA.3256@TK2MSFTNGP12.phx.gbl... >> I want to return all rows from two tables where the primary key of one > table >> equals the foreign key of another. >> So if CustomerID in the customer table has CustomerID 1 - n, I want to > only >> return rows where CustomerID's 5, 9 and 766 are found in the >> EmerilLiveKickitUpaNotch Tasters table where there are CustomerID foreign >> key matches. >> >> What is the difference between an inner join of those two tables and a >> simple select query using the where clause... where Customer.CustomerID = >> Tasters.CustomerID. >> >> Thanks, -hazz >> >> > > See "Join Fundamentals" in BOL
-- Show quoteThanks Ravi "hazz" wrote: > I want to return all rows from two tables where the primary key of one table > equals the foreign key of another. > So if CustomerID in the customer table has CustomerID 1 - n, I want to only > return rows where CustomerID's 5, 9 and 766 are found in the > EmerilLiveKickitUpaNotch Tasters table where there are CustomerID foreign > key matches. > > What is the difference between an inner join of those two tables and a > simple select query using the where clause... where Customer.CustomerID = > Tasters.CustomerID. > > Thanks, -hazz > > > There's no difference.
For readability and consistency reasons I usually put any inner join on the foreign key columns in the ON clause and the criteria for other, non-foreign key columns into the WHERE clause. That's just a convention I use though. Logically it makes no difference with an INNER join. OUTER joins are a different matter. With outer joins there's a big difference in the meaning of a predicate placed in the WHERE clause versus the same predicate placed in the ON clause. -- David Portas SQL Server MVP -- Thank you David for clarifiying that! I realized the difference in the outer
joins. I like your convention. -hazz Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1120059128.410612.298670@g14g2000cwa.googlegroups.com... > There's no difference. > > For readability and consistency reasons I usually put any inner join on > the foreign key columns in the ON clause and the criteria for other, > non-foreign key columns into the WHERE clause. That's just a convention > I use though. Logically it makes no difference with an INNER join. > > OUTER joins are a different matter. With outer joins there's a big > difference in the meaning of a predicate placed in the WHERE clause > versus the same predicate placed in the ON clause. > > -- > David Portas > SQL Server MVP > -- >  |
|||||||||||||||||||||||
Other interesting topics