SQL Server Joins - Mystery Resolved

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 FB 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.EMPID = TBL2.EMPID

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 Join

SELECT

*

FROM

TABLE1 TBL1

LEFT JOIN

TABLE2 TBL2

ON

                TBL1.EMPID = TBL2.EMPID

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

-------------------------------------------------------------------------------------------------

Right Join

SELECT

*

FROM

TABLE1 TBL1

RIGHT JOIN

TABLE2 TBL2

ON

                TBL1.EMPID = TBL2.EMPID

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

-------------------------------------------------------------------------------------------------

Full Outer Join

SELECT

*

FROM

TABLE1 TBL1

FULL OUTER JOIN

TABLE2 TBL2

ON

                TBL1.EMPID = TBL2.EMPID

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.EMPID = TBL2.EMPID

WHERE

                TBL2.EMPID 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.EMPID = TBL2.EMPID

WHERE

                TBL1.EMPID 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.EMPID = TBL2.EMPID

WHERE

                TBL1.EMPID IS NULL

OR

                TBL2.EMPID 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 J.

Same article is also published in my company's official site. Please click here to see the article over there.