{"id":22382,"date":"2018-08-09T05:00:54","date_gmt":"2018-08-09T05:00:54","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=22382"},"modified":"2021-03-10T21:11:00","modified_gmt":"2021-03-10T15:41:00","slug":"sql-join","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-join\/","title":{"rendered":"SQL Join – Inner, Left, Right & Full Joins"},"content":{"rendered":"

We have large units of data stored in our database, consisting of thousands of tables. When we need to look into a specific set of data satisfying various conditions, we face problems while combining a large amount of data.<\/p>\n

The data is spread into multiple tables, and thus to extract the data from multiple tables we use various joins to convert the tables into one table.<\/p>\n

What are Joins in SQL and Use of SQL Joins?<\/h3>\n

SQL join statements help us to combine the rows and columns and parts of various tables and view them as one. By the use of joins, we can easily combine and present data into a single table.<\/p>\n

This makes it easy for us to perform queries and transactions on the data needed by us. We perform joins based on one or more common fields in the two or more tables.<\/p>\n

Let us try to understand by using the example of a school student\u2019s data.<\/p>\n

We have a table storing the data of the student with all the personal details including the parent\u2019s details and health details of the student. On the other side, we have a table that stores the data on the performance of the student.<\/p>\n

Now suppose we want to run a survey on our school students which requires the data of the parents and the performance of the student over time. To do any sort of analysis, we require data to be present in the same table.<\/p>\n

To obtain this we use joins on the tables. By using join we can combine data that satisfies various conditions and logics we specify and require.<\/p>\n

When we use joins, they are done on multiple parameters and conditions passed. Here we will discover more about the types and uses of the available joins in SQL.<\/p>\n

Demo Database<\/h3>\n

Let us view our tables, i.e. DataFlair_emp1 and DataFlair_emp2.
\nQuery:\u00a0<\/strong><\/p>\n

SELECT * FROM DataFlair_emp1 ;\r\n<\/pre>\n
\"view<\/a><\/p>\n
Query:\u00a0<\/strong><\/p>\n
SELECT * FROM DataFlair_emp2 ;\r\n<\/pre>\n
\"View<\/a><\/p>\n
Types of SQL Joins<\/h3>\n
We have four types of joins in SQL, and they are as follows:<\/p>\n\n\n\n\n\n\n\n
Sr.No<\/b><\/td>\nJOIN<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
1<\/span><\/td>\nInner Join<\/span><\/td>\nFetches value common in all the tables.<\/span><\/td>\n<\/tr>\n
2<\/span><\/td>\nLeft (Outer) Join<\/span><\/td>\nFetches all values from the left table and matching records from the right table.<\/span><\/td>\n<\/tr>\n
3<\/span><\/td>\nRight (Outer) Join<\/span><\/td>\nFetches all values from the right table and matching records from the left table.<\/span><\/td>\n<\/tr>\n
4<\/span><\/td>\nFull (Outer) Join<\/span><\/td>\nFetches value when there is a match in the right or left table.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Below are the diagrammatic representations of all the SQL Joins:<\/p>\n

\n\"SQL<\/a><\/p>\n
\"SQL<\/a><\/p>\n
\"SQL<\/a><\/p>\n
\"SQL<\/a><\/p>\n
SQL Inner Join Syntax<\/h4>\n
SELECT * \r\nFROM tableName1\r\nINNER JOIN tableName2\r\nON tableName1.matchingColumn = tableName2.matchingColumn ;\r\n<\/pre>\n
Example 1: [Inner Join] Let us view all the data in both of our tables using the inner join.<\/strong>
\nQuery:<\/strong><\/p>\n
SELECT *\r\nFROM DataFlair_emp1\r\nINNER JOIN DataFlair_emp2\r\nON dataflair_emp1.emp_id = dataflair_emp2.emp_id ;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
SQL Left (Outer) Join Syntax<\/h4>\n
SELECT * \r\nFROM tableName1\r\nLEFT JOIN tableName2\r\nON tableName1.matchingColumn = tableName2.matchingColumn ;\r\n<\/pre>\n
Example 2: [Left (Outer) Join] Let us use the left join on both of our tables.<\/strong>
\nQuery:<\/strong><\/p>\n
SELECT *\r\nFROM DataFlair_emp1\r\nLEFT JOIN DataFlair_emp2\r\nON dataflair_emp1.emp_id = dataflair_emp2.emp_id ;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
SQL Right (Outer) Join Syntax<\/h4>\n
SELECT * \r\nFROM tableName1\r\nRIGHT JOIN tableName2\r\nON tableName1.matchingColumn = tableName2.matchingColumn ;\r\n<\/pre>\n
Example 3: [Right (Outer) Join] Let us use the right join on both of our tables.<\/strong>
\nQuery:<\/strong><\/p>\n
SELECT *\r\nFROM DataFlair_emp1\r\nRIGHT JOIN DataFlair_emp2\r\nON dataflair_emp1.emp_id = dataflair_emp2.emp_id ;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
SQL Full (Outer) Join Syntax<\/h4>\n
SELECT * \r\nFROM tableName1\r\nRIGHT JOIN tableName2\r\nON tableName1.matchingColumn = tableName2.matchingColumn ;\r\n<\/pre>\n
Example 4: [Full (Outer) Join] Let us use the full join on both of our tables.<\/strong>
\nQuery:<\/strong><\/p>\n
SELECT *\r\nFROM DataFlair_emp1\r\nFULL JOIN DataFlair_emp2\r\nON dataflair_emp1.emp_id = dataflair_emp2.emp_id ;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Summary<\/h3>\n
When we have to deal with large volumes of data, we need to do a lot of modifications and tweaks in our data to extract some meaningful knowledge.<\/p>\n
With the help of joins, we are able to easily combine the data and draw useful insights out of it. We have discussed four significant types of joins, i.e. Full outer join, Left outer join, Right outer join, and inner join.<\/p>\n","protected":false},"excerpt":{"rendered":"
We have large units of data stored in our database, consisting of thousands of tables. When we need to look into a specific set of data satisfying various conditions, we face problems while combining...<\/p>\n","protected":false},"author":6,"featured_media":86843,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[7807,9345,12715,13461,13481,13490,13492,13493,13494,13498,13508],"class_list":["post-22382","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-join-query-in-sql","tag-outer-join-in-sql","tag-self-join-in-sql","tag-sql-full-join","tag-sql-inner-join","tag-sql-join-example","tag-sql-joins-3-tables","tag-sql-left-join","tag-sql-left-outer-join","tag-sql-multiple-joins","tag-sql-outer-join"],"yoast_head":"\nSQL Join - Inner, Left, Right & Full Joins - DataFlair<\/title>\n<meta name=\"description\" content=\"Learn what is SQL Join with types, syntax and examples - SQL Inner join, SQL full join, SQL Left Join, outer join in SQL, SQL Left outer join,\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sql-join\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Join - Inner, Left, Right & Full Joins - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Learn what is SQL Join with types, syntax and examples - SQL Inner join, SQL full join, SQL Left Join, outer join in SQL, SQL Left outer join,\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-join\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-09T05:00:54+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-10T15:41:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Joins.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Join - Inner, Left, Right & Full Joins - DataFlair","description":"Learn what is SQL Join with types, syntax and examples - SQL Inner join, SQL full join, SQL Left Join, outer join in SQL, SQL Left outer join,","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sql-join\/","og_locale":"en_US","og_type":"article","og_title":"SQL Join - Inner, Left, Right & Full Joins - DataFlair","og_description":"Learn what is SQL Join with types, syntax and examples - SQL Inner join, SQL full join, SQL Left Join, outer join in SQL, SQL Left outer join,","og_url":"https:\/\/data-flair.training\/blogs\/sql-join\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-08-09T05:00:54+00:00","article_modified_time":"2021-03-10T15:41:00+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Joins.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-join\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-join\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"SQL Join – Inner, Left, Right & Full Joins","datePublished":"2018-08-09T05:00:54+00:00","dateModified":"2021-03-10T15:41:00+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-join\/"},"wordCount":575,"commentCount":3,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-join\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Joins.jpg","keywords":["join query in SQL","outer join in SQL","Self join in SQL","SQL Full join","SQL inner join","SQL join example","SQL joins 3 tables","SQL Left join","SQL Left outer join","SQL Multiple joins","SQL Outer join"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-join\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-join\/","url":"https:\/\/data-flair.training\/blogs\/sql-join\/","name":"SQL Join - Inner, Left, Right & Full Joins - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-join\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-join\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Joins.jpg","datePublished":"2018-08-09T05:00:54+00:00","dateModified":"2021-03-10T15:41:00+00:00","description":"Learn what is SQL Join with types, syntax and examples - SQL Inner join, SQL full join, SQL Left Join, outer join in SQL, SQL Left outer join,","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-join\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-join\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-join\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Joins.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Joins.jpg","width":1200,"height":628,"caption":"SQL Joins"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-join\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Searching for Groups of Scala Regex","item":"https:\/\/data-flair.training\/blogs\/tag\/searching-for-groups-of-scala-regex\/"},{"@type":"ListItem","position":3,"name":"SQL Join – Inner, Left, Right & Full Joins"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/22382","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=22382"}],"version-history":[{"count":7,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/22382\/revisions"}],"predecessor-version":[{"id":86856,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/22382\/revisions\/86856"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/86843"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=22382"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=22382"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=22382"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}