SQL – Joins

Spread the love

Joins – this  functionality is very useful and important, it maps the records to combine rows between mapped tables, basis on related column between them.

SQL 4 type of joins – inner join , left outer join , right outer join and and full outer join
Alternate names of joins and can be specified within a query

SQL- Join Alternate Name
Inner join Join
Left outer join Left join
Right outer join Right join
Full outer join Full join

Technics and defined joins for programming– (it can be asked by an interviewer)

Note: These technics are built to resolve the complexity of query writing.

Natural Join
Cross Join
Self Join
Equi Join
Non-Equi-Join

 

 

EXAMPLE

 

ITEM_TYPE TABLE

S_NOPRODUCTTYPE
1NIKESHOES
2REEBOKTSHIRT
3FILASHOES
4PUMARUNNING SHOES
5ADIDASSHOES

BRAND TABLE

S_NOPRODUCTMONTHYEARQTYSALES
1NIKEDEC2016107599
2REEBOKJAN2014181200
3FILAFEB201786000
4PUMAJAN2015165000
5NULLAPR201252500

 

 

INNER JOIN

SELECT A.PRODUCT,         A.TYPE ,          B.QTY,       B.SALES
FROM    ITEM_TYPES    AS    A
INNER JOIN
BRAND  B
ON A.PRODUCT  =    B.PRODUCT

RESULT

PRODUCTTYPEQTYSALES;;
NIKESHOES107599
REEBOKTSHIRT181200
FILASHOES86000
PUMARUNNING SHOES165000

 

 

LEFT OUTER JOIN

SELECT A.PRODUCT,        A.TYPE ,      B.QTY,        B.SALES
FROM    ITEM_TYPES   AS  A
LEFT OUTER     JOIN
BRAND   B
ON A.PRODUCT   =    B.PRODUCT

RESULT

PRODUCTTYPEQTYSALES
NIKESHOES107599
REEBOKTSHIRT181200
FILASHOES86000
PUMARUNNING SHOES165000
ADIDASSHOESNULLNULL

 

RIGHT OUTER JOIN

SELECT A.PRODUCT,       A.TYPE ,     B.QTY,        B.SALES
FROM   ITEM_TYPES  AS  A
RIGHT OUTER  JOIN
BRAND  B
ON A.PRODUCT   =   B.PRODUCT

RESULT

PRODUCTTYPEQTYSALES
NIKESHOES107599
REEBOKTSHIRT181200
FILASHOES86000
PUMARUNNING SHOES165000
NULLNULL52500

 

FULL OUTER JOIN

SELECT A.PRODUCT, A.[TYPE] ,B.QTY,B.SALES
FROM ITEM_TYPES AS A
FULL JOIN
BRAND B
ON A.PRODUCT = B.PRODUCT

RESULT

PRODUCTTYPEQTYSALES
NIKESHOES107599
REEBOKTSHIRT181200
FILASHOES86000
PUMARUNNING SHOES165000
ADIDASSHOESNULLNULL

 

Uses of other joins

SELF JOIN , CROSS JOIN

A team member has reporting manager are mapped, also a reporting manager will be reporting manager for himself , below is the table where “Reporting Manager” is null , which should have a reporting manager. for accomplish the following condition needs to use SELF JOIN.

 

Emp Table

EMP_IDNAMEREPORTING_MANAGER
1RAJIVM.N JAIN
2SURESHM.N JAIN
3VIRENM.N JAIN
4NAIRM.N JAIN
5KUNALM.N JAIN
6M.N JAINNULL

 

Example 1 

SELECT A.EMP_ID , A.NAME , B.NAME AS REPORTING_MANAGER
FROM EMP A , EMP B           
ORDER BY B.NAME

Note –  It is a  cross join also, emp table * emp table records will be merged ,  a different table can be merged for other results

RESULT

EMP_IDNAMEREPORTING_MANAGER
1RAJIVKUNAL
2SURESHKUNAL
3VIRENKUNAL
4NAIRKUNAL
5KUNALKUNAL
6M.N JAINKUNAL
1RAJIVM.N JAIN
2SURESHM.N JAIN
3VIRENM.N JAIN
4NAIRM.N JAIN
5KUNALM.N JAIN
6M.N JAINM.N JAIN
1RAJIVNAIR
2SURESHNAIR
3VIRENNAIR
4NAIRNAIR
5KUNALNAIR
6M.N JAINNAIR
1RAJIVRAJIV
2SURESHRAJIV
3VIRENRAJIV
4NAIRRAJIV
5KUNALRAJIV
6M.N JAINRAJIV
1RAJIVSURESH
2SURESHSURESH
3VIRENSURESH
4NAIRSURESH
5KUNALSURESH
6M.N JAINSURESH
1RAJIVVIREN
2SURESHVIREN
3VIRENVIREN
4NAIRVIREN
5KUNALVIREN
6M.N JAINVIREN

 

EQUI JOIN / NON EQUI JOIN

Example 2 – if only single group is in the table then a filter can be used

SELECT A.EMP_ID , A.NAME , B.NAME AS REPORTING_MANAGER
FROM EMP A , EMP B
WHERE B.NAME = ‘M.N JAIN’
ORDER BY 1

USE OPERATOR for NON EQUI JOIN –  > , <= , >= , <>

RESULT

EMP_IDNAMEREPORTING_MANAGER
1RAJIVM.N JAIN
2SURESHM.N JAIN
3VIRENM.N JAIN
4NAIRM.N JAIN
5KUNALM.N JAIN
6M.N JAINM.N JAIN