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

EMPID	EMPNAME	DESIGNAION	SALARY
1001	AAA	MANAGER	2,000,000
1002	BBB	TEAM LEAD	1,500,000
1003	CCC	SR. DEVELOPER	1,200,000
1004	DDD	DEVELOPER	900,000
1005	EEE	DEVELOPER	700,000

TABLE2

EMPID	EMPNAME	DESIGNAION	SALARY
1001	AAA	MANAGER	2,000,000
1002	BBB	TEAM LEAD	1,500,000
1005	EEE	DEVELOPER	1,200,000
1007	GGG	DEVELOPER	900,000
1008	HHH	DEVELOPER	700,000

Inner Join

SELECT

*

FROM

TABLE1 TBL1

INNER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

TABLE1	TABLE2
EMPID	EMPNAME	DESIGNAION	SALARY	EMPID	EMPNAME	DESIGNAION	SALARY
1001	AAA	MANAGER	2,000,000	1001	AAA	MANAGER	2,000,000
1002	BBB	TEAM LEAD	1,500,000	1002	BBB	TEAM LEAD	1,500,000
1005	EEE	DEVELOPER	1,200,000	1005	EEE	DEVELOPER	1,200,000

Left Outer Join

SELECT

*

FROM

TABLE1 TBL1

LEFT OUTER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

TABLE1	TABLE2
EMPID	EMPNAME	DESIGNAION	SALARY	EMPID	EMPNAME	DESIGNAION	SALARY
1001	AAA	MANAGER	2,000,000	1001	AAA	MANAGER	2,000,000
1002	BBB	TEAM LEAD	1,500,000	1002	BBB	TEAM LEAD	1,500,000
1003	CCC	SR. DEVELOPER	1,200,000
1004	DDD	DEVELOPER	900,000
1005	EEE	DEVELOPER	700,000	1005	EEE	DEVELOPER	1,200,000

RightOuter Join

SELECT

*

FROM

TABLE1 TBL1

RIGHT OUTER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

TABLE1	TABLE2
EMPID	EMPNAME	DESIGNAION	SALARY	EMPID	EMPNAME	DESIGNAION	SALARY
1001	AAA	MANAGER	2,000,000	1001	AAA	MANAGER	2,000,000
1002	BBB	TEAM LEAD	1,500,000	1002	BBB	TEAM LEAD	1,500,000
1005	EEE	DEVELOPER	700,000	1005	EEE	DEVELOPER	1,200,000
				1007	GGG	DEVELOPER	900,000
				1008	HHH	DEVELOPER	700,000

FullOuter Join

SELECT

*

FROM

TABLE1 TBL1

FULL OUTER JOIN

TABLE2 TBL2

ON

TBL1.COLUMN1 = TBL2.COLUMN1

TABLE1	TABLE2
EMPID	EMPNAME	DESIGNAION	SALARY	EMPID	EMPNAME	DESIGNAION	SALARY
1001	AAA	MANAGER	2,000,000	1001	AAA	MANAGER	2,000,000
1002	BBB	TEAM LEAD	1,500,000	1002	BBB	TEAM LEAD	1,500,000
1003	CCC	SR. DEVELOPER	1,200,000
1004	DDD	DEVELOPER	900,000
1005	EEE	DEVELOPER	700,000	1005	EEE	DEVELOPER	1,200,000
				1007	GGG	DEVELOPER	900,000
				1008	HHH	DEVELOPER	700,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
EMPID	EMPNAME	DESIGNAION	SALARY	EMPID	EMPNAME	DESIGNAION	SALARY
1003	CCC	SR. DEVELOPER	1,200,000
1004	DDD	DEVELOPER	900,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
EMPID	EMPNAME	DESIGNAION	SALARY	EMPID	EMPNAME	DESIGNAION	SALARY
				1007	GGG	DEVELOPER	900,000
				1008	HHH	DEVELOPER	700,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
EMPID	EMPNAME	DESIGNAION	SALARY	EMPID	EMPNAME	DESIGNAION	SALARY
1003	CCC	SR. DEVELOPER	1,200,000
1004	DDD	DEVELOPER	900,000
				1007	GGG	DEVELOPER	900,000
				1008	HHH	DEVELOPER	700,000

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