SQL Joins – Mystery Resolved
Vineet S
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.
