...

Working with SQL Server was always a troublesome experience for me, especially when I had to pick up data from multiple tables. Working with multiple tables (Joins) was never my forte and used to give goose bumps whenever I work with those. I always used to feel like a tail-ender batsman, who is facing a fiery spell from a lead bowler of opposite team, until I saw a Facebook post where joins are explained with the help of pictures. That gave me a bit of confidence, so I thought of sharing the same with of you.

For explanation, I am assuming that circles represents 2 different tables, say TABLE1 and TABLE2, with COLUMN1 as the common field between 2 tables, and highlighted portion (in red) is the result of query.

Here are the tables, I am using for below examples:

TABLE1

EMPIDEMPNAMEDESIGNAIONSALARY
1001AAAMANAGER2,000,000
1002BBBTEAM LEAD1,500,000
1003CCCSR. DEVELOPER1,200,000
1004DDDDEVELOPER900,000
1005EEEDEVELOPER700,000

TABLE2

EMPIDEMPNAMEDESIGNAIONSALARY
1001AAAMANAGER2,000,000
1002BBBTEAM LEAD1,500,000
1005EEEDEVELOPER1,200,000
1007GGGDEVELOPER900,000
1008HHHDEVELOPER700,000

Inner Join

SELECT

*

FROM

TABLE1 TBL1

INNER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

 TABLE1TABLE2
EMPIDEMPNAMEDESIGNAIONSALARYEMPIDEMPNAMEDESIGNAIONSALARY
1001AAAMANAGER2,000,0001001AAAMANAGER2,000,000
1002BBBTEAM LEAD1,500,0001002BBBTEAM LEAD1,500,000
1005EEEDEVELOPER1,200,0001005EEEDEVELOPER1,200,000

Left Outer Join

SELECT

*

FROM

TABLE1 TBL1

LEFT OUTER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

TABLE1TABLE2
EMPIDEMPNAMEDESIGNAIONSALARYEMPIDEMPNAMEDESIGNAIONSALARY
1001AAAMANAGER2,000,0001001AAAMANAGER2,000,000
1002BBBTEAM LEAD1,500,0001002BBBTEAM LEAD1,500,000
1003CCCSR. DEVELOPER1,200,000
1004DDDDEVELOPER900,000
1005EEEDEVELOPER700,0001005EEEDEVELOPER1,200,000

RightOuter Join

SELECT

*

FROM

TABLE1 TBL1

RIGHT OUTER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

 TABLE1 TABLE2
EMPIDEMPNAMEDESIGNAIONSALARYEMPIDEMPNAMEDESIGNAIONSALARY
1001AAAMANAGER2,000,0001001AAAMANAGER2,000,000
1002BBBTEAM LEAD1,500,0001002BBBTEAM LEAD1,500,000
1005EEEDEVELOPER700,0001005EEEDEVELOPER1,200,000
1007GGGDEVELOPER900,000
1008HHHDEVELOPER700,000

FullOuter Join

SELECT

*

FROM

TABLE1 TBL1

FULL OUTER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

 TABLE1 TABLE2
EMPIDEMPNAMEDESIGNAIONSALARYEMPIDEMPNAMEDESIGNAIONSALARY
1001AAAMANAGER2,000,0001001AAAMANAGER2,000,000
1002BBBTEAM LEAD1,500,0001002BBBTEAM LEAD1,500,000
1003CCCSR. DEVELOPER1,200,000
1004DDDDEVELOPER900,000
1005EEEDEVELOPER700,0001005EEEDEVELOPER1,200,000
1007GGGDEVELOPER900,000
1008HHHDEVELOPER700,000

Left Outer Join where NULL

SELECT

*

FROM

TABLE1 TBL1

LEFT OUTER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

WHERE

TBL2.COLUMN1 IS NULL

 TABLE1 TABLE2
EMPIDEMPNAMEDESIGNAIONSALARYEMPIDEMPNAMEDESIGNAIONSALARY
1003CCCSR. DEVELOPER1,200,000
1004DDDDEVELOPER900,000

Right Outer Join where NULL

SELECT

*

FROM

TABLE1 TBL1

RIGHT OUTER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

WHERE

TBL1.COLUMN1 IS NULL

 TABLE1 TABLE2
EMPIDEMPNAMEDESIGNAIONSALARYEMPIDEMPNAMEDESIGNAIONSALARY
1007GGGDEVELOPER900,000
1008HHHDEVELOPER700,000

Full Outer Join where NULL

SELECT

*

FROM

TABLE1 TBL1

FULL OUTER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

WHERE

TBL1.COLUMN1 IS NULL

OR

TBL2.COLUMN1 IS NULL

 TABLE1 TABLE2
EMPIDEMPNAMEDESIGNAIONSALARYEMPIDEMPNAMEDESIGNAIONSALARY
1003CCCSR. DEVELOPER1,200,000
1004DDDDEVELOPER900,000
1007GGGDEVELOPER900,000
1008HHHDEVELOPER700,000

Now after this, I am confident enough, like any other top order batsman, who can play any bowler with ease.