{"id":17130,"date":"2024-05-23T09:37:41","date_gmt":"2024-05-23T16:37:41","guid":{"rendered":"https:\/\/www.pingcap.com\/?post_type=article&#038;p=17130"},"modified":"2024-06-17T00:14:16","modified_gmt":"2024-06-17T07:14:16","slug":"sql-joins-101-essential-concepts-explained","status":"publish","type":"article","link":"https:\/\/www.pingcap.com\/ko\/article\/sql-joins-101-essential-concepts-explained\/","title":{"rendered":"Master SQL Joins Like a Pro: The Essential Guide"},"content":{"rendered":"<p>Understanding SQL Joins is fundamental for anyone interacting with relational databases. Whether you are a database administrator, a software developer, or simply curious about database operations, mastering SQL JOIN commands will dramatically improve your ability to manipulate and understand your data efficiently. This article will walk you through the major types of JOINs you need to know, using both theory and practical examples, particularly focusing on how they are implemented in <a href=\"\/ko\/tidb\/\">\ud2f0DB<\/a>, a distributed SQL database designed to support critical business applications.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Understanding_SQL_JOIN\"><\/span><strong>Understanding SQL JOIN<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>A SQL JOIN clause is used to combine rows from two or more tables, based on a related column between them. It enables databases to provide a more comprehensive dataset that couldn&#8217;t otherwise be derived from querying single tables. This operation is pivotal for relational database systems, where normalization is a practice, and data is stored across multiple tables.<\/p>\n\n\n\n<p>SQL JOIN plays a critical role in database management and application development by allowing for the retrieval of information dispersed across several tables. Without JOINs, maintaining data integrity and constructing meaningful queries from a normalized database would be cumbersome, if not impossible. They bring the relational in &#8220;relational databases,&#8221; enabling complex transactions and sophisticated data analysis.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Types_of_SQL_JOIN\"><\/span><strong>Types of SQL JOIN<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>There are several types of SQL JOINs that you can use depending on your specific needs. Let&#8217;s take a closer look at the most common join types.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>INNER JOIN<\/strong><\/h3>\n\n\n\n<p>An INNER JOIN produces a result set that includes only the rows that match the join condition from both participating tables. If there is no matching rows, the rows are not returned. This is the default behavior for JOIN, so INNER JOIN is the same as JOIN.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"584\" height=\"347\" src=\"https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join.png\" alt=\"SQL JOIN-INNER JOIN\" class=\"wp-image-17133\" srcset=\"https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join.png 584w, https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join-300x178.png 300w\" sizes=\"auto, (max-width: 584px) 100vw, 584px\" \/><\/figure>\n\n\n\n<p><strong>Example of INNER JOIN<\/strong><\/p>\n\n\n\n<p>Considering the following example, using TiDB:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Assuming two tables, 'products' and 'orders'\nSELECT products.name, orders.quantity\nFROM products\nINNER JOIN orders ON products.id = orders.product_id\nWHERE orders.status = 'delivered';<\/code><\/pre>\n\n\n\n<p>This query fetches product names and order quantities for all delivered orders, exemplifying INNER JOIN by linking orders to their respective products.<\/p>\n\n\n\n<p><em><strong><strong>Note:<\/strong><\/strong> If you do a join without an ON clause, you will do what is sometimes called a CROSS JOIN, but not the INNER JOIN. A CROSS JOIN combines each row of the first table with every row of the second table, often resulting in a large Cartesian product. This difference is crucial for understanding the potential data volume and processing implications of each join type.<\/em><\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong><em><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\">Want to try the SQL JOINs on a powerful MySQL-compatible database?<\/mark><\/em><\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/tidbcloud.com\/free-trial\/\" class=\"button\" target=\"_blank\" data-gtag=\"event:go_to_cloud_signup,product_type:serverless,button_name:Sign Up for Free,position:article_middle_cta\" rel=\"noopener\">Sign Up for TiDB Serverless Now<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>LEFT JOIN<\/strong><\/h3>\n\n\n\n<p>A LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table, and the matched records from the right table. The result is NULL on the right side if there is no match.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"426\" height=\"305\" src=\"https:\/\/static.pingcap.com\/files\/2024\/05\/23092316\/left-outer-join.png\" alt=\"SQL JOIN-LEFT OUTER JOIN\" class=\"wp-image-17135\" srcset=\"https:\/\/static.pingcap.com\/files\/2024\/05\/23092316\/left-outer-join.png 426w, https:\/\/static.pingcap.com\/files\/2024\/05\/23092316\/left-outer-join-300x215.png 300w\" sizes=\"auto, (max-width: 426px) 100vw, 426px\" \/><\/figure>\n\n\n\n<p><strong>Example of LEFT JOIN<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- With 'employees' as the left table and 'departments' as the right table\nSELECT employees.name, departments.name\nFROM employees\nLEFT JOIN departments ON employees.department_id = departments.id;<\/code><\/pre>\n\n\n\n<p>This query will list all employees, including those without a department, showcasing the utility of LEFT JOIN in comprehensive data retrieval across related tables.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>RIGHT JOIN<\/strong><\/h3>\n\n\n\n<p>A RIGHT JOIN (or RIGHT OUTER JOIN) operates like a LEFT JOIN, but returns all records from the right table, and the matched records from the left table.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"426\" height=\"305\" src=\"https:\/\/static.pingcap.com\/files\/2024\/05\/23092336\/right-outer-join.png\" alt=\"SQL JOIN-RIGHT OUTER JOIN\" class=\"wp-image-17136\" srcset=\"https:\/\/static.pingcap.com\/files\/2024\/05\/23092336\/right-outer-join.png 426w, https:\/\/static.pingcap.com\/files\/2024\/05\/23092336\/right-outer-join-300x215.png 300w\" sizes=\"auto, (max-width: 426px) 100vw, 426px\" \/><\/figure>\n\n\n\n<p><strong>Example of RIGHT JOIN<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Assuming 'students' on the right, and 'courses' on the left\nSELECT courses.title, students.name\nFROM courses\nRIGHT JOIN students ON courses.id = students.course_id;<\/code><\/pre>\n\n\n\n<p>This query ensures all students are listed, illustrating RIGHT JOIN&#8217;s role in scenarios where completeness from one side is essential.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>FULL JOIN<\/strong><\/h3>\n\n\n\n<p>FULL JOIN combines LEFT JOIN and RIGHT JOIN, returning rows when there is a match in one of the tables. It provides a full outer join of the two tables, leaving no record behind.<\/p>\n\n\n\n<p><strong>Example of FULL JOIN<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Joining 'authors' and 'publications'\nSELECT authors.name, publications.title\nFROM authors\nFULL JOIN publications ON authors.id = publications.author_id;<\/code><\/pre>\n\n\n\n<p>This query presents a complete bridging of authors to their publications, demonstrating FULL JOIN&#8217;s capability to merge disjoined datasets into a coherent whole.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Summary\"><\/span><strong>Summary<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>SQL JOINs are indispensable tools in the database management toolkit. They empower developers and database administrators to weave together data stored across the schema into a tapestry of meaningful information. Among SQL databases, TiDB provides an interesting case study due to its distributed nature, enhancing the traditional capabilities of SQL JOIN with its horizontal scalability and engineering for critical business applications. By mastering JOIN operations, you can harness the full power of relational databases, turning discrete tables of data into insightful, actionable information.<\/p>\n\n\n\n<p>Through these examples, we&#8217;ve barely scratched the surface of what&#8217;s possible with SQL JOINs. To learn even more about joins, you can check out our <a href=\"https:\/\/www.pingcap.com\/ko\/education\/course-learn-sql-with-tidb\/\">Course: Learn SQL with TiDB<\/a> as well as our docs on <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/dev-guide-join-tables\">Multi-table Join Queries<\/a>. <\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong><em><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\">Try SQL JOINs on TiDB Serverless today and experience seamless data integration!<\/mark><\/em><\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/tidbcloud.com\/free-trial\/\" class=\"button\" target=\"_blank\" data-gtag=\"event:go_to_cloud_signup,product_type:serverless,button_name:Sign Up for Free,position:article_bottom_cta\" rel=\"noopener\">Sign Up for Free<\/a><\/p>","protected":false},"excerpt":{"rendered":"<p>Understanding SQL Joins is fundamental for anyone interacting with relational databases. Whether you are a database administrator, a software developer, or simply curious about database operations, mastering SQL JOIN commands will dramatically improve your ability to manipulate and understand your data efficiently. This article will walk you through the major types of JOINs you need [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":0,"template":"","class_list":["post-17130","article","type-article","status-publish","hentry"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Master SQL Joins Like a Pro: The Essential Guide | TiDB<\/title>\n<meta name=\"description\" content=\"The guide explains join types with clear examples, empowering you to combine tables and extract valuable insights from databases.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pingcap.com\/ko\/article\/sql-joins-101-essential-concepts-explained\/\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Master SQL Joins Like a Pro: The Essential Guide | TiDB\" \/>\n<meta property=\"og:description\" content=\"The guide explains join types with clear examples, empowering you to combine tables and extract valuable insights from databases.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pingcap.com\/ko\/article\/sql-joins-101-essential-concepts-explained\/\" \/>\n<meta property=\"og:site_name\" content=\"TiDB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/facebook.com\/pingcap2015\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-17T07:14:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/\",\"url\":\"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/\",\"name\":\"Master SQL Joins Like a Pro: The Essential Guide | TiDB\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join.png\",\"datePublished\":\"2024-05-23T16:37:41+00:00\",\"dateModified\":\"2024-06-17T07:14:16+00:00\",\"description\":\"The guide explains join types with clear examples, empowering you to combine tables and extract valuable insights from databases.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/#primaryimage\",\"url\":\"https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.pingcap.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Articles\",\"item\":\"https:\/\/www.pingcap.com\/article\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Master SQL Joins Like a Pro: The Essential Guide\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.pingcap.com\/#website\",\"url\":\"https:\/\/www.pingcap.com\/\",\"name\":\"TiDB\",\"description\":\"TiDB | SQL at Scale\",\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.pingcap.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ko-KR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.pingcap.com\/#organization\",\"name\":\"PingCAP\",\"url\":\"https:\/\/www.pingcap.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"width\":811,\"height\":232,\"caption\":\"PingCAP\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/facebook.com\/pingcap2015\",\"https:\/\/x.com\/PingCAP\",\"https:\/\/linkedin.com\/company\/pingcap\",\"https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Master SQL Joins Like a Pro: The Essential Guide | TiDB","description":"The guide explains join types with clear examples, empowering you to combine tables and extract valuable insights from databases.","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:\/\/www.pingcap.com\/ko\/article\/sql-joins-101-essential-concepts-explained\/","og_locale":"ko_KR","og_type":"article","og_title":"Master SQL Joins Like a Pro: The Essential Guide | TiDB","og_description":"The guide explains join types with clear examples, empowering you to combine tables and extract valuable insights from databases.","og_url":"https:\/\/www.pingcap.com\/ko\/article\/sql-joins-101-essential-concepts-explained\/","og_site_name":"TiDB","article_publisher":"https:\/\/facebook.com\/pingcap2015","article_modified_time":"2024-06-17T07:14:16+00:00","og_image":[{"url":"https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_site":"@PingCAP","twitter_misc":{"Est. reading time":"4\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/","url":"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/","name":"Master SQL Joins Like a Pro: The Essential Guide | TiDB","isPartOf":{"@id":"https:\/\/www.pingcap.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/#primaryimage"},"image":{"@id":"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/#primaryimage"},"thumbnailUrl":"https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join.png","datePublished":"2024-05-23T16:37:41+00:00","dateModified":"2024-06-17T07:14:16+00:00","description":"The guide explains join types with clear examples, empowering you to combine tables and extract valuable insights from databases.","breadcrumb":{"@id":"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/"]}]},{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/#primaryimage","url":"https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2024\/05\/23092234\/inner-join.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.pingcap.com\/article\/sql-joins-101-essential-concepts-explained\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pingcap.com\/"},{"@type":"ListItem","position":2,"name":"Articles","item":"https:\/\/www.pingcap.com\/article\/"},{"@type":"ListItem","position":3,"name":"Master SQL Joins Like a Pro: The Essential Guide"}]},{"@type":"WebSite","@id":"https:\/\/www.pingcap.com\/#website","url":"https:\/\/www.pingcap.com\/","name":"\ud2f0DB","description":"TiDB | SQL at Scale","publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pingcap.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ko-KR"},{"@type":"Organization","@id":"https:\/\/www.pingcap.com\/#organization","name":"PingCAP","url":"https:\/\/www.pingcap.com\/","logo":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/","url":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","width":811,"height":232,"caption":"PingCAP"},"image":{"@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/facebook.com\/pingcap2015","https:\/\/x.com\/PingCAP","https:\/\/linkedin.com\/company\/pingcap","https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA"]}]}},"card_markup":"        <a class=\"card-article\" href=\"https:\/\/www.pingcap.com\/ko\/article\/sql-joins-101-essential-concepts-explained\/\">            <h3>Master SQL Joins Like a Pro: The Essential Guide<\/h3>            <p>Understanding SQL Joins is fundamental for anyone interacting with relational databases. Whether you are a database administrator, a software developer, or simply curious about database operations, mastering SQL JOIN commands will dramatically improve your ability to manipulate and understand your data efficiently. This article will walk you through the major types of JOINs you need [&hellip;]<\/p>        <\/a>","_links":{"self":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article\/17130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article"}],"about":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/types\/article"}],"author":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/users\/8"}],"wp:attachment":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media?parent=17130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}