Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Swede
Yak Posting Veteran
74 Posts |
 Posted - 2002-04-08 : 04:35:23
|
| What are the differences between all the different joins. Inner, Outer...Any links that could help me? I am currently working on a SP gathering info from lots of tables and I can't get my joins to work the way I want to...Here it is in case you were wondering what it is...SELECT l.le_leads_id, c.le_customer_name, o.le_office_name, w.country_name, p.le_project_nameFROM le_leads lINNER JOIN le_customer c ON l.le_leads_customer_id = c.le_customer_idINNER JOIN le_office o ON c.le_customer_id = o.le_office_idINNER JOIN countries w ON o.le_office_country = w.country_idINNER JOIN le_project p ON l.le_leads_project_id = p.le_project_idORDER BY p.le_project_name, o.le_office_name, w.country_name, c.le_customer_nameI am trying to list all the leads, with all the added information from the other tables where they connect. But with this query it will not work properly...Edited by - Swede on 04/08/2002 05:06:08Edited by - Swede on 04/08/2002 10:51:44 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-04-08 : 05:56:41
|
| inner --> means 1 row will be returned if there is a common record in both tables either side of the join...left --> means that 1 row will be returned from the left (1st) table, along with any common information in the right table...difference from inner is that something will be returned (ie the left table) even if no common information between the 2 tables...outer --> not 100% sure of this....but think it's the reverse of left...ie the info from table 2 will always be returned, with any available info from table 1....if it exists...sample data....table1 table2cola colb cola colba1 7 a1 davida2 11 a3 1 a3 colma4 5 a4 brian a5 andrewa6 7 a6 toman inner join using cola as the joining field will return records a1,a3,a4,a6a left inner join will return a1,a2 (with no table2 data),a3,a4,a6a left outer join will return a1,a3,a4,a5 (with no table1 data), a6.create some sample tables, and sample data...and experiment!!!....it's the best learning exercise!!! |
 |
|
|
Swede
Yak Posting Veteran
74 Posts |
Posted - 2002-04-08 : 06:18:05
|
| Sooo....The INNER JOIN returns all rows from both tables where there is a match.The LEFT JOIN returns all the rows from the first table, even if there are no matches in the second table.The RIGHT JOIN returns all the rows from the second table, even if there are no matches in the first table.So is RIGHT JOIN the same as OUTER JOIN?=====================================Why not try and do the impossible? |
|
|
|
Teroman
Posting Yak Master
115 Posts |
Posted - 2002-04-08 : 06:54:05
|
| the word Outer is optional, soLeft join is the same as Left Outer JoinRight join is the same as Right Outer Jointhere are a couple of others too,Full Outer join is a combination of left and right joins, will return all rows from both tables, and match the ones that fit the join clause.Cross join returns all possible combinations of rows between the 2 tables. Eg if you had a table with "Bob" and "Pete" in it, and one wiht "Smith" and "Jones", cross joined you would getBob SmithBob JonesPete SmithPete Jonesmaybe a poor example, but i hope you get the ideacol |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-04-08 : 07:29:28
|
| yup.....in the left/right joins....NULLS are returned for the missing columns on the relevent rows....again....try some experimentation....seeing will be believing!!! |
|
|
|
Swede
Yak Posting Veteran
74 Posts |
Posted - 2002-04-08 : 10:51:25
|
| Haha, you are gonna laugh your bananas blue. I was actually trying to join to the wrong field. That was the reason it didn't work. Typical.Thanks anyway though. Needed an explanation of joins anyway ;)=====================================Why not try and do the impossible? |
|
|
|
|
|
|
|
|
Joins *solved*