i don't really understand the whole concept. however i just had a problem that wouldn't work (my query wouldn't return records where a field was NULL that it was doing a join on) but when i used a left outer join it now works correctly. so, i figured i better get a better grasp on these concepts.
could someone give me a quick explanation?Inner Join = Only joins records when both sides of the join are equal
Outer Join - Grabs all records from one side of the join no matter what values are there
Example:
Table1
Fields:ID,FName,LName
Records:
ID = 1;FName = Ryan;LName = Putman
ID = 2;FName = Bob;LName = Smith
Table2
Fields:ID,UserID,Address
Records:
ID = 1;UserID = 2;Address = 123 Test Dr.
ID = 2;UserID = 2;Address = 456 Nowhere Blvd.
If you run a query like this
Select Table1.FName,Table2.Address from Table1 inner join Table2 on Table1.ID = Table2.UserID
You will only get the records for Bob Smith becuase he is the only user with an address.
If you run a query like this
Select Table1.FName,Table2.Address from Table1 Left Outer join Table2 on Table1.ID = Table2.UserID
You will get the records for Bob and you'll also get the one for Ryan, but Ryan's will return NULL values for Table2.Address
If you wanted to run this last query and make sure you get all the records from Table2 you'd do a Left Outer Join - pretty simple to remember: Right - grabs all records from the table right of the word Join; Left - the opposite
could someone give me a quick explanation?Inner Join = Only joins records when both sides of the join are equal
Outer Join - Grabs all records from one side of the join no matter what values are there
Example:
Table1
Fields:ID,FName,LName
Records:
ID = 1;FName = Ryan;LName = Putman
ID = 2;FName = Bob;LName = Smith
Table2
Fields:ID,UserID,Address
Records:
ID = 1;UserID = 2;Address = 123 Test Dr.
ID = 2;UserID = 2;Address = 456 Nowhere Blvd.
If you run a query like this
Select Table1.FName,Table2.Address from Table1 inner join Table2 on Table1.ID = Table2.UserID
You will only get the records for Bob Smith becuase he is the only user with an address.
If you run a query like this
Select Table1.FName,Table2.Address from Table1 Left Outer join Table2 on Table1.ID = Table2.UserID
You will get the records for Bob and you'll also get the one for Ryan, but Ryan's will return NULL values for Table2.Address
If you wanted to run this last query and make sure you get all the records from Table2 you'd do a Left Outer Join - pretty simple to remember: Right - grabs all records from the table right of the word Join; Left - the opposite