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

Inner Join

 

SELECT

*

FROM

TABLE1 TBL1

INNER JOIN

TABLE2 TBL2

ON

                TBL1.COLUMN1 = TBL2.COLUMN1

 

 

TABLE1

TABLE2

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

 

Left Outer Join

Left Outer Join

 

SELECT

*

FROM

TABLE1 TBL1

LEFT 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

 

Right Outer Join

Right Outer 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

 

 

Full Outer Join

 

Full Outer 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

 

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

 

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

 

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.

Vineet S
Vineet S
Technical Consultant