| Author |
Topic |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2009-07-15 : 10:48:52
|
| Can someone teach me easier way to understand left and right join? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-15 : 11:12:13
|
select * fromtable_1left join table_2 on ...where ...means:give me all records from the left table (here it is table_1) even if there are not matching records in the joined table.The missing column values from the joined table are coming up with null value.That's the difference to an inner join (without left or right) because an inner join gives you only that records which have matching records in both tables.right join is exactly like left join - only the tables are changed...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-15 : 13:05:16
|
| also for left join table on left side is base table ie. whether or not it has matching value on right side, all records from table will be returned whereas for right join all values from right side will be returned regardless of match on left side. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-15 : 14:55:30
|
That's exactly what I wanted to say, but your words are much better than my stammering 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2009-07-16 : 09:51:39
|
| Thank you for help. Now, I will put base table on the left.I did a test as below"Table L has 100 recordsTable R has 30 recordsIn view window, L was put on left and R was right.I found that the output for "full outer join" is the same as "left outer join", they are 100 records.What is difference of them? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-16 : 09:57:43
|
If there is a record 31 in R with no match in L then full outer join returns also this record with NULL values for R-columns.The left join would NOT return this record.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2009-07-16 : 10:16:34
|
| So, never use "Full outer join". Am right? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-16 : 10:21:32
|
I have seen a thread here in this forum where the full outer join was the solution.But I have never had a situation to use this in my world.Fred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-07-16 : 10:48:01
|
webby!! izit my thread? xD
Hope can help...but advise to wait pros with confirmation... |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-16 : 11:11:15
|
quote:
Originally posted by waterduck
webby!! izit my thread? xD
Hope can help...but advise to wait pros with confirmation...
I can't remember exactly the thread (I'm already a grandpa!) but I remind that there IS a thread because it was the first time I've seen that full outer join can be useful.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
Easier way to understand left and right join