{"id":12688,"date":"2014-01-12T06:51:00","date_gmt":"2014-01-12T06:51:00","guid":{"rendered":"https:\/\/viewmyprojects.com\/winwirewp\/?p=12688"},"modified":"2025-01-28T11:39:36","modified_gmt":"2025-01-28T11:39:36","slug":"sql-joins-mystery-resolved","status":"publish","type":"post","link":"https:\/\/viewmyprojects.com\/winwirewp\/blog\/sql-joins-mystery-resolved\/","title":{"rendered":"SQL Joins \u2013 Mystery Resolved"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Here are the tables, I am using for below examples:<\/p>\n\n\n\n

TABLE1<\/strong><\/p>\n\n\n\n

EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td><\/tr>
1001<\/td>AAA<\/td>MANAGER<\/td>2,000,000<\/td><\/tr>
1002<\/td>BBB<\/td>TEAM LEAD<\/td>1,500,000<\/td><\/tr>
1003<\/td>CCC<\/td>SR. DEVELOPER<\/td>1,200,000<\/td><\/tr>
1004<\/td>DDD<\/td>DEVELOPER<\/td>900,000<\/td><\/tr>
1005<\/td>EEE<\/td>DEVELOPER<\/td>700,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

TABLE2<\/strong><\/p>\n\n\n\n

EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td><\/tr>
1001<\/td>AAA<\/td>MANAGER<\/td>2,000,000<\/td><\/tr>
1002<\/td>BBB<\/td>TEAM LEAD<\/td>1,500,000<\/td><\/tr>
1005<\/td>EEE<\/td>DEVELOPER<\/td>1,200,000<\/td><\/tr>
1007<\/td>GGG<\/td>DEVELOPER<\/td>900,000<\/td><\/tr>
1008<\/td>HHH<\/td>DEVELOPER<\/td>700,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Inner Join<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

SELECT<\/strong><\/p>\n\n\n\n

*<\/strong><\/p>\n\n\n\n

FROM<\/strong><\/p>\n\n\n\n

TABLE1 TBL1<\/strong><\/p>\n\n\n\n

INNER JOIN<\/strong><\/p>\n\n\n\n

TABLE2 TBL2<\/strong><\/p>\n\n\n\n

ON<\/strong><\/p>\n\n\n\n

TBL1.COLUMN1 = TBL2.COLUMN1<\/strong><\/p>\n\n\n\n

 TABLE1<\/strong><\/td>TABLE2<\/strong><\/td><\/tr>
EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td>EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td><\/tr>
1001<\/td>AAA<\/td>MANAGER<\/td>2,000,000<\/td>1001<\/td>AAA<\/td>MANAGER<\/td>2,000,000<\/td><\/tr>
1002<\/td>BBB<\/td>TEAM LEAD<\/td>1,500,000<\/td>1002<\/td>BBB<\/td>TEAM LEAD<\/td>1,500,000<\/td><\/tr>
1005<\/td>EEE<\/td>DEVELOPER<\/td>1,200,000<\/td>1005<\/td>EEE<\/td>DEVELOPER<\/td>1,200,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Left Outer Join<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

SELECT<\/strong><\/p>\n\n\n\n

*<\/strong><\/p>\n\n\n\n

FROM<\/strong><\/p>\n\n\n\n

TABLE1 TBL1<\/strong><\/p>\n\n\n\n

LEFT OUTER JOIN<\/strong><\/p>\n\n\n\n

TABLE2 TBL2<\/strong><\/p>\n\n\n\n

ON<\/strong><\/p>\n\n\n\n

TBL1.COLUMN1 = TBL2.COLUMN1<\/strong><\/p>\n\n\n\n

TABLE1<\/strong><\/td>TABLE2<\/strong><\/td><\/tr>
EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td>EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td><\/tr>
1001<\/td>AAA<\/td>MANAGER<\/td>2,000,000<\/td>1001<\/td>AAA<\/td>MANAGER<\/td>2,000,000<\/td><\/tr>
1002<\/td>BBB<\/td>TEAM LEAD<\/td>1,500,000<\/td>1002<\/td>BBB<\/td>TEAM LEAD<\/td>1,500,000<\/td><\/tr>
1003<\/td>CCC<\/td>SR. DEVELOPER<\/td>1,200,000<\/td><\/td><\/td><\/td><\/td><\/tr>
1004<\/td>DDD<\/td>DEVELOPER<\/td>900,000<\/td><\/td><\/td><\/td><\/td><\/tr>
1005<\/td>EEE<\/td>DEVELOPER<\/td>700,000<\/td>1005<\/td>EEE<\/td>DEVELOPER<\/td>1,200,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

RightOuter Join<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

SELECT<\/strong><\/p>\n\n\n\n

*<\/strong><\/p>\n\n\n\n

FROM<\/strong><\/p>\n\n\n\n

TABLE1 TBL1<\/strong><\/p>\n\n\n\n

RIGHT OUTER JOIN<\/strong><\/p>\n\n\n\n

TABLE2 TBL2<\/strong><\/p>\n\n\n\n

ON<\/strong><\/p>\n\n\n\n

TBL1.COLUMN1 = TBL2.COLUMN1<\/strong><\/p>\n\n\n\n

 TABLE1<\/strong><\/td> TABLE2<\/strong><\/td><\/tr>
EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td>EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td><\/tr>
1001<\/td>AAA<\/td>MANAGER<\/td>2,000,000<\/td>1001<\/td>AAA<\/td>MANAGER<\/td>2,000,000<\/td><\/tr>
1002<\/td>BBB<\/td>TEAM LEAD<\/td>1,500,000<\/td>1002<\/td>BBB<\/td>TEAM LEAD<\/td>1,500,000<\/td><\/tr>
1005<\/td>EEE<\/td>DEVELOPER<\/td>700,000<\/td>1005<\/td>EEE<\/td>DEVELOPER<\/td>1,200,000<\/td><\/tr>
<\/td><\/td><\/td><\/td>1007<\/td>GGG<\/td>DEVELOPER<\/td>900,000<\/td><\/tr>
<\/td><\/td><\/td><\/td>1008<\/td>HHH<\/td>DEVELOPER<\/td>700,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

FullOuter Join<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

SELECT<\/strong><\/p>\n\n\n\n

*<\/strong><\/p>\n\n\n\n

FROM<\/strong><\/p>\n\n\n\n

TABLE1 TBL1<\/strong><\/p>\n\n\n\n

FULL OUTER JOIN<\/strong><\/p>\n\n\n\n

TABLE2 TBL2<\/strong><\/p>\n\n\n\n

ON<\/strong><\/p>\n\n\n\n

TBL1.COLUMN1 = TBL2.COLUMN1<\/strong><\/p>\n\n\n\n

 TABLE1<\/strong><\/td> TABLE2<\/strong><\/td><\/tr>
EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td>EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td><\/tr>
1001<\/td>AAA<\/td>MANAGER<\/td>2,000,000<\/td>1001<\/td>AAA<\/td>MANAGER<\/td>2,000,000<\/td><\/tr>
1002<\/td>BBB<\/td>TEAM LEAD<\/td>1,500,000<\/td>1002<\/td>BBB<\/td>TEAM LEAD<\/td>1,500,000<\/td><\/tr>
1003<\/td>CCC<\/td>SR. DEVELOPER<\/td>1,200,000<\/td><\/td><\/td><\/td><\/td><\/tr>
1004<\/td>DDD<\/td>DEVELOPER<\/td>900,000<\/td><\/td><\/td><\/td><\/td><\/tr>
1005<\/td>EEE<\/td>DEVELOPER<\/td>700,000<\/td>1005<\/td>EEE<\/td>DEVELOPER<\/td>1,200,000<\/td><\/tr>
<\/td><\/td><\/td><\/td>1007<\/td>GGG<\/td>DEVELOPER<\/td>900,000<\/td><\/tr>
<\/td><\/td><\/td><\/td>1008<\/td>HHH<\/td>DEVELOPER<\/td>700,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Left Outer Join where NULL<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

SELECT<\/strong><\/p>\n\n\n\n

*<\/strong><\/p>\n\n\n\n

FROM<\/strong><\/p>\n\n\n\n

TABLE1 TBL1<\/strong><\/p>\n\n\n\n

LEFT OUTER JOIN<\/strong><\/p>\n\n\n\n

TABLE2 TBL2<\/strong><\/p>\n\n\n\n

ON<\/strong><\/p>\n\n\n\n

TBL1.COLUMN1 = TBL2.COLUMN1<\/strong><\/p>\n\n\n\n

WHERE<\/strong><\/p>\n\n\n\n

TBL2.COLUMN1 IS NULL<\/strong><\/p>\n\n\n\n

 TABLE1<\/strong><\/td> TABLE2<\/strong><\/td><\/tr>
EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td>EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td><\/tr>
1003<\/td>CCC<\/td>SR. DEVELOPER<\/td>1,200,000<\/td><\/td><\/td><\/td><\/td><\/tr>
1004<\/td>DDD<\/td>DEVELOPER<\/td>900,000<\/td><\/td><\/td><\/td><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Right Outer Join where NULL<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

SELECT<\/strong><\/p>\n\n\n\n

*<\/strong><\/p>\n\n\n\n

FROM<\/strong><\/p>\n\n\n\n

TABLE1 TBL1<\/strong><\/p>\n\n\n\n

RIGHT OUTER JOIN<\/strong><\/p>\n\n\n\n

TABLE2 TBL2<\/strong><\/p>\n\n\n\n

ON<\/strong><\/p>\n\n\n\n

TBL1.COLUMN1 = TBL2.COLUMN1<\/strong><\/p>\n\n\n\n

WHERE<\/strong><\/p>\n\n\n\n

TBL1.COLUMN1 IS NULL<\/strong><\/p>\n\n\n\n

 TABLE1<\/strong><\/td> TABLE2<\/strong><\/td><\/tr>
EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td>EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td><\/tr>
<\/td><\/td><\/td><\/td>1007<\/td>GGG<\/td>DEVELOPER<\/td>900,000<\/td><\/tr>
<\/td><\/td><\/td><\/td>1008<\/td>HHH<\/td>DEVELOPER<\/td>700,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Full Outer Join where NULL<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

SELECT<\/strong><\/p>\n\n\n\n

*<\/strong><\/p>\n\n\n\n

FROM<\/strong><\/p>\n\n\n\n

TABLE1 TBL1<\/strong><\/p>\n\n\n\n

FULL OUTER JOIN<\/strong><\/p>\n\n\n\n

TABLE2 TBL2<\/strong><\/p>\n\n\n\n

ON<\/strong><\/p>\n\n\n\n

TBL1.COLUMN1 = TBL2.COLUMN1<\/strong><\/p>\n\n\n\n

WHERE<\/strong><\/p>\n\n\n\n

TBL1.COLUMN1 IS NULL<\/strong><\/p>\n\n\n\n

OR<\/strong><\/p>\n\n\n\n

TBL2.COLUMN1 IS NULL<\/strong><\/p>\n\n\n\n

 TABLE1<\/strong><\/td> TABLE2<\/strong><\/td><\/tr>
EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td>EMPID<\/strong><\/td>EMPNAME<\/strong><\/td>DESIGNAION<\/strong><\/td>SALARY<\/strong><\/td><\/tr>
1003<\/td>CCC<\/td>SR. DEVELOPER<\/td>1,200,000<\/td><\/td><\/td><\/td><\/td><\/tr>
1004<\/td>DDD<\/td>DEVELOPER<\/td>900,000<\/td><\/td><\/td><\/td><\/td><\/tr>
<\/td><\/td><\/td><\/td>1007<\/td>GGG<\/td>DEVELOPER<\/td>900,000<\/td><\/tr>
<\/td><\/td><\/td><\/td>1008<\/td>HHH<\/td>DEVELOPER<\/td>700,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Now after this, I am confident enough, like any other top order batsman, who can play any bowler with ease.<\/p>\n","protected":false},"excerpt":{"rendered":"

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… Continue reading SQL Joins \u2013 Mystery Resolved<\/span><\/a><\/p>\n","protected":false},"author":109,"featured_media":16739,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-12688","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized","entry"],"acf":[],"featured_image_src":"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/SQL-Joins-\u2013-Mystery-Resolved-graphic.webp","author_info":{"display_name":"Vineet S","author_link":"https:\/\/viewmyprojects.com\/winwirewp\/author\/vineet-s\/"},"views":3676,"uagb_featured_image_src":{"full":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/SQL-Joins-\u2013-Mystery-Resolved-graphic.webp",800,440,false],"thumbnail":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/SQL-Joins-\u2013-Mystery-Resolved-graphic-150x150.webp",150,150,true],"medium":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/SQL-Joins-\u2013-Mystery-Resolved-graphic-300x165.webp",300,165,true],"medium_large":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/SQL-Joins-\u2013-Mystery-Resolved-graphic-768x422.webp",750,412,true],"large":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/SQL-Joins-\u2013-Mystery-Resolved-graphic.webp",750,413,false],"1536x1536":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/SQL-Joins-\u2013-Mystery-Resolved-graphic.webp",800,440,false],"2048x2048":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/SQL-Joins-\u2013-Mystery-Resolved-graphic.webp",800,440,false],"post-thumbnail":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/SQL-Joins-\u2013-Mystery-Resolved-graphic.webp",800,440,false]},"uagb_author_info":{"display_name":"Vineet S","author_link":"https:\/\/viewmyprojects.com\/winwirewp\/author\/vineet-s\/"},"uagb_comment_info":0,"uagb_excerpt":"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……","_links":{"self":[{"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/posts\/12688","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/users\/109"}],"replies":[{"embeddable":true,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/comments?post=12688"}],"version-history":[{"count":3,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/posts\/12688\/revisions"}],"predecessor-version":[{"id":22563,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/posts\/12688\/revisions\/22563"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/media\/16739"}],"wp:attachment":[{"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/media?parent=12688"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/categories?post=12688"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/tags?post=12688"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}