|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Left outer join or Inner joinsThere two tables (TB1 and TB2) .These two tables are related to each other
using two Keys (Key1,Key2). I'd like to see how many records of TB1 have their counterparts in TB2. For scenarios like the above I mostly use left outer joins ,but I've noticed that inner join in more common (aparently!!Unless I'm mistaken). Can somebody help to clear this out for me? Thanks Well, that really depends on the scenario you have.
When you use Inner Join you get data which are in both TB1 and TB2 related to the Keys (usually a PK and FK, mother-child relationship). Now, in case that you have data from TB1 (let's assume this is the one with the PK) and there's no correspondent data in TB2, this data (TB1) are excluded using Inner Join becuase they don't have child records on TB2. So, If you use Left Outer Join you assure to get all the records from TB1 and the records from TB2 plus the records that are on TB1 and not necessarily have records related on TB2, which all the fields are set to Null values. Same thing with Right Join, but the other way around. Show quote "J-T" <J-T@nospam.com> escribió en el mensaje news:%23Sy7jx0gFHA.2444@tk2msftngp13.phx.gbl... > There two tables (TB1 and TB2) .These two tables are related to each other > using two Keys (Key1,Key2). I'd like to see how many records of TB1 have > their counterparts in TB2. > > For scenarios like the above I mostly use left outer joins ,but I've > noticed that inner join in more common (aparently!!Unless I'm mistaken). > > Can somebody help to clear this out for me? > > Thanks > Beautiful description.
> to the Keys (usually a PK and FK, mother-child relationship). I always use this term (mother-child relationship) ,but look at what CELKO thinks about terminology:-): CELKO Wrote: >>No, your problem is that you have no idea what an RDBMS is supposed to He proabably needs to tell this to those guys who write whitepapers for >>be and still think that the user interface is where data modeling >>begins. You even still use 1970's terms like "child" instead of >>"referencing table". ADO.NET too as I've seen a lot of people using this term instead of "refeerecning table" and even his God (Dr.Codd) :-) Thanks for the help , Show quote "Nery Gonzalez" <nery_gonza***@hotmail.com> wrote in message news:Ouya120gFHA.4000@TK2MSFTNGP12.phx.gbl... > Well, that really depends on the scenario you have. > > When you use Inner Join you get data which are in both TB1 and TB2 related > to the Keys (usually a PK and FK, mother-child relationship). Now, in case > that you have data from TB1 (let's assume this is the one with the PK) and > there's no correspondent data in TB2, this data (TB1) are excluded using > Inner Join becuase they don't have child records on TB2. > > So, If you use Left Outer Join you assure to get all the records from TB1 > and the records from TB2 plus the records that are on TB1 and not > necessarily have records related on TB2, which all the fields are set to > Null values. > > Same thing with Right Join, but the other way around. > > > > > > "J-T" <J-T@nospam.com> escribió en el mensaje > news:%23Sy7jx0gFHA.2444@tk2msftngp13.phx.gbl... >> There two tables (TB1 and TB2) .These two tables are related to each >> other using two Keys (Key1,Key2). I'd like to see how many records of TB1 >> have their counterparts in TB2. >> >> For scenarios like the above I mostly use left outer joins ,but I've >> noticed that inner join in more common (aparently!!Unless I'm mistaken). >> >> Can somebody help to clear this out for me? >> >> Thanks >> > > > He proabably needs to tell this to those guys who write whitepapers for Yes, however don't generalize. Microsoft also perpetuates the use of the> ADO.NET too terms "field" and "record" throughout their documentation, terms which have no place in an RDBMS (though we're not all as anal about it as Celko :-)). They also produced Access, which is a whole different abomination. So, no, there is no such unilateral equation as "(Microsoft said/did/likes it) == (it is good)"... Sure, anytime....!!!
don't even bother paying attention... Sometimes (always) the usage of a word gives the meaning of it!!! Show quote "J-T" <J-T@nospam.com> escribió en el mensaje news:OlTZL90gFHA.272@TK2MSFTNGP15.phx.gbl... > Beautiful description. > >> to the Keys (usually a PK and FK, mother-child relationship). > I always use this term (mother-child relationship) ,but look at what CELKO > thinks about terminology:-): > > CELKO Wrote: >>>No, your problem is that you have no idea what an RDBMS is supposed to >>>be and still think that the user interface is where data modeling >>>begins. You even still use 1970's terms like "child" instead of >>>"referencing table". > > He proabably needs to tell this to those guys who write whitepapers for > ADO.NET too as I've seen a lot of people using this term instead of > "refeerecning table" and even his God (Dr.Codd) :-) > > Thanks for the help , > > > > > > "Nery Gonzalez" <nery_gonza***@hotmail.com> wrote in message > news:Ouya120gFHA.4000@TK2MSFTNGP12.phx.gbl... >> Well, that really depends on the scenario you have. >> >> When you use Inner Join you get data which are in both TB1 and TB2 >> related to the Keys (usually a PK and FK, mother-child relationship). >> Now, in case that you have data from TB1 (let's assume this is the one >> with the PK) and there's no correspondent data in TB2, this data (TB1) >> are excluded using Inner Join becuase they don't have child records on >> TB2. >> >> So, If you use Left Outer Join you assure to get all the records from TB1 >> and the records from TB2 plus the records that are on TB1 and not >> necessarily have records related on TB2, which all the fields are set to >> Null values. >> >> Same thing with Right Join, but the other way around. >> >> >> >> >> >> "J-T" <J-T@nospam.com> escribió en el mensaje >> news:%23Sy7jx0gFHA.2444@tk2msftngp13.phx.gbl... >>> There two tables (TB1 and TB2) .These two tables are related to each >>> other using two Keys (Key1,Key2). I'd like to see how many records of >>> TB1 have their counterparts in TB2. >>> >>> For scenarios like the above I mostly use left outer joins ,but I've >>> noticed that inner join in more common (aparently!!Unless I'm mistaken). >>> >>> Can somebody help to clear this out for me? >>> >>> Thanks >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics