Join commands in SQL [duplicate]

Navigation
1

I have some doubts about the commands inner join , left join , right join and full join , I do not know if it's just the join that makes the joins between the tables, but together it comes with these commands , and I am not able to understand the purpose of them, could anyone explain to me how they work and when to use them?

    
asked by anonymous 03.12.2015 / 12:56

2 answers

1

For example, consider the scheme below:

CROSSJOIN

Whenwewanttojointwoormoretablesbycrossing.Thatis,foreachrowoftheFUNCTIONALtablewewantalltheCHARGESorviceversa.

INNERJOIN

Whenwewanttojointwoormoretablesbycoincidence.ForeachrowoftheFUNCINARYtablewewantthecorrespondingPOSITIONthatinternally(INNER),intheirattributevalues,match.InthecaseofFUNIONARYandCHARGEthematchinginternalattributesarecodeIngintheTABLEtableandcodeIngintheFUNCTIONALtable.Tocombinethetwotables,itwillbenecessarytoconnectthetwotablesbytheirmatchinginternalattributes(INNER).

LEFTOUTERJOIN

LookingattheFUNCTIONALtable,supposethattheemployeeTadeuhasnopositionassociatedwithit.Ifwewishtolistalltheemployeeswiththeirrespectivepositions,includingtheemployeeswithoutpositions,wecoulduseallthepoweroftheINNERJOINjunctionbyaddingOUTER(OUTS/OUTS)EmployeeswhoarenotpartofINNERJOIN,justtothosewithoutpositionslikeTadeu.WecanachievethisbyjoiningFUNCIONARIO/CARGOthroughthedeclarationOUTERLEFTJOINCARGO,whichpromotestheinternaljoining(INNER)ofallemployeestopositionsandlistsother(OUTSOURDED/OUTER)nonassociated.

Animportantobservationisthattheorderoftheconnection(ON)makesnodifference,ie:"ON (F.codCargo = C.codCargo)" is exactly equal to "ON (C.codCargo = F.codCargo)"

RIGHTOUTERJOIN

LookingattheCARGOtable,supposethemanagerposition,withcodeC3,isnotreferenced/associatedby/toanyemployeeintheOFFICIALtable.IfwewishtolistallCHARGESandtheirrespectiveOFFICERS,includingCHARGESwithoutEMPLOYEES,wecouldusetheRIGTHOUTERJOINjoin.

OUTERFULLJOIN

Herewecombinethepowerofinternaljoins(INNER),thelistingofallothernon-associatedlines,bothontherightside(RIGHT)ofthejunctionandontheLEFTside.

@ reference

    
03.12.2015 / 13:19
1

Dener, all join the tables with some differences:

When you use INNER JOIN with two tables for example,

INNER join creates an intersection set as in math, as in the image below, INNER JOIN brings all the related jobs to ids 4 and 5 of the job table that contains the user table and ignores the others, as in set A and B. 

SELECT * FROM usuario A INNER JOIN cargo B on B.id = A.id_cargo

NowLEFTJOINcreatesarelationshipsetbetweenthetwotables,butthefollowingdifference:

SELECT*FROMusuarioALEFTJOINcargoBA.id_cargo=B.id

Intheabovequery,thetablespecifiedintheleftuserwillhavepreference,thenitwillresultinuserswhohaveajobanduserswhodonothaveajob,andRIGHTJOINdoestheopposite,fulJOINjoinsthetables.

    
03.12.2015 / 13:23
Getting xlsx file with HTML and PHP How to add groups of inputs according to the number inserted in a text box without refresh