| Author |
Topic |
|
notsosuper
Posting Yak Master
190 Posts |
 Posted - 2005-09-14 : 11:42:17
|
| which web site can explain (entry level) difference between inner join and outer joins? I have looked many of them all the levels and their explanation is not easy to understand. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-09-14 : 11:54:10
|
Have you looked here?Sql Server Books OnlineAccessing and Changing Relational Data Query Fundamentals Join Fundamentals Using Joins Be One with the OptimizerTG |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-09-14 : 11:58:21
|
| INNER JOIN: All Rows that show equality between table A And BLEFT OUTER JOIN: All Rows from table A and only those from B that are equalRIGHT OUTER JOIN: All Rows from table B and only those from A that are equalIt's really easy to create some sample data and test it in Query AnalyzerWant to talk about FULL and CROSS?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-14 : 13:26:35
|
| what about the ANSI 92 natural join that sql server does not impliment?====================================================Regards,Sean Roussy"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-09-14 : 14:06:47
|
| >> what about the ANSI 92 natural join that sql server does not impliment?What about it? It's just an inner join except you cannot specify the relation yourself, you must have identically named columns in both tables. Not too useful in my opinion -- it is like using SELECT * from a table, which makes it tough to decipher a SQL statement w/o the DDL in front of you.Word of advice as you learn about joins: Ignore FULL OUTER and RIGHT OUTER joins. Pretend they don't exist, as least when you write your own SQL. All you need are: INNER JOIN, UNION ALL, LEFT OUTER JOIN and CROSS JOIN. RIGHT OUTER JOINS = messy, messy SQL and FULL OUTER JOINS are even worse. UNION is used way too often -- 99% of the time people really should be using UNION ALL which is much more efficient. |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-09-14 : 15:05:43
|
I have found a few good uses for FULL JOINS  but for the most part I agree  Corey Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now." |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-09-14 : 15:11:34
|
| >> I have found a few good uses for FULL JOINSReally? I have found that using UNION ALL to combine two tables that might have matches in either is much more efficient and easier to write/understand than using FULL OUTER JOIN. Whenever you use a full outer join, the values returned from EVERY column from both tables involved is potentially equal to NULL and must be surround by COALESCE -- even primary key columns. Ugh! FULL OUTER JOINS are just plain silly! |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-09-14 : 15:25:11
|
| One use was looking for errors discrepencies in two tables...ie: drops from archive1 and adds in archive2the most straight forward way is a full join between the two archives.the other use:consider searching in a very normalized db (contacts table, locations table, contact_locations table, addresses table, phones table)if you want your results in either contacts or locations (with the opposite's info available), and you will be searching on addresses & phones as well, a cpl of full joins made this searchable either way with one query... it just became a matter of displaying the right columns on the results page.CoreyCo-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now." |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-09-14 : 15:34:27
|
| >> One use was looking for errors discrepencies in two tables...>>>>ie: drops from archive1 and adds in archive2>>the most straight forward way is a full join between the two archives.I think a UNION is the easiest way to do this; seehttp://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspxAs for the other situation, it would depending on the DDL and the results needed, but my first instinct is most likely a select FROM the entity table and LEFT OUTER JOINS to each contact table is the way to go. That's a common part of confusion regarding FULL OUTER and RIGHT OUTER JOINS -- you start from the wrong table as your primary source and then are forced to use something other than LEFT OUTER JOINS. |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-09-14 : 16:28:49
|
I think that your article is great for determining that they are different, but I've found it easier to work through if they are lined up next to each other.While its simple to implement the union, I don't find the full join scary either:I like this method (though it is more typing) because I can work with the results (and the query) to focus in on a specific issue more easily. I don't think I'm really explaining this very well, but I tried .Select key01 = A.key01, keynn = A.keynn, aCol01 = A.col01, aColmm = A.colmm, bCol01 = B.col01, bColmm = B.colmmFrom tableA A,Full Join tableB BOn A.key1 = B.key1and A.keyn = B.keynWhere A.col01 <> B.col01or A.colmm <> B.colmm CoreyCo-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now." |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-14 : 20:23:36
|
I've used full joins this way also. We needed to see the both results in the case I used it. It can be very simple to implement and understand. FULL OUTER JOIN is just a tool that is often abused.  Now cursors???????? lolMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
joins