{"id":169649,"date":"2026-01-28T03:50:37","date_gmt":"2026-01-28T02:50:37","guid":{"rendered":"https:\/\/liora.io\/en\/?p=169649"},"modified":"2026-08-08T12:31:58","modified_gmt":"2026-08-08T11:31:58","slug":"sql-joins-everything-you-need-to-know-about","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/sql-joins-everything-you-need-to-know-about","title":{"rendered":"SQL joins: everything you need to know about table associations"},"content":{"rendered":"\n<p><strong>If you&#8217;re a regular reader of our blog, you&#8217;ve already heard of SQL or &#8220;Structured Query Language&#8221;: a programming language for managing databases, and you know all about the importance of SQL joins. <\/strong><\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"what-is-sql-joins\">What is SQL Joins ?<\/h2>\n\n\n<p>If you missed the article on the\u00a0<a href=\"https:\/\/liora.io\/en\/sql-learn-all-about-the-programming-language-for-databases\" rel=\"noopener\" target=\"_blank\">basics of SQL language<\/a>. We&#8217;ll give it to you again here.<\/p>\n\n\n<p>At first glance, SQL joins may seem complicated or difficult to apply. Liora has put together a quick summary of the main SQL joins, with a simple example to make your life easier! But first of all, what is a join?\u00a0\u00a0<\/p>\n\n\n<p>Joins are used to retrieve data from a database where tables have relationships with each other. They enable you to exploit the tables in a database, link them and obtain efficient results.<\/p>\n\n\n<p>There are several types, and we&#8217;re going to look at the main ones.\u00a0<\/p>\n\n\n<p>\u00a0Throughout this article, we&#8217;ll be relying on two tables:\u00a0<\/p>\n\n\n<ul class=\"wp-block-list\"><li>the city table shows the cities where the customer corresponding to the customer_id lives,\u00a0<\/li><li>the email table gives customer email addresses.<\/li><\/ul>\n\n\n<p><strong>City<\/strong><\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:138px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Data1\" decoding=\"async\" height=\"111\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/Data1.png\" style=\"width:138px;max-width:100%;height:auto\" width=\"138\"\/><\/figure>\n\n\n<p><strong>Email<\/strong><\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:402px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Data2\" decoding=\"async\" height=\"109\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/Data2.png\" style=\"width:402px;max-width:100%;height:auto\" width=\"402\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"1-inner-join\">1. INNER JOIN<\/h2>\n\n\n<p>The internal join or INNER JOIN returns data when the condition is true in both tables.\u00a0\u00a0<\/p>\n\n\n<p>As shown in the diagram, this type of join will concatenate the tuples of 2 tables two by two if a condition is met. This condition can be of any type, as long as it returns <a href=\"https:\/\/en.wikipedia.org\/wiki\/Boolean_data_type\">a Boolean<\/a>. Typically, this condition will be the equality of a common attribute. It is necessary to specify the attributes to be used to perform the join. The ON keyword is used, followed by the desired equality after the tables have been specified in the INNER JOIN.\u00a0<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:300px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Inner join\" decoding=\"async\" height=\"180\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/Inner-join.png\" style=\"width:300px;max-width:100%;height:auto\" width=\"300\"\/><\/figure>\n\n\n<p>This is the query that wastes the most information since it only selects rows in both tables where there is information in both tables.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:414px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Inner join1\" decoding=\"async\" height=\"97\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/Inner-join1.png\" style=\"width:414px;max-width:100%;height:auto\" width=\"414\"\/><\/figure>\n\n\n<p>Here, by performing an INNER JOIN between the two tables, we obtain only the customer IDs present in the email table and in the city table. Customer IDs 4 and 5, which are present in the email table but not in the city table, are not taken into account. The same applies to customer IDs 7 and 8, which are in the city table but not in the email table.<\/p>\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center wp-container-core-buttons-is-layout-5ee10de4\" style=\"margin-top:32px;margin-bottom:32px\"><div class=\"wp-block-button\"><a class=\"wp-block-button__link wp-element-button\" href=\"\/en\/courses\/data-ai\/data-analyst\">Learn to use SQL<\/a><\/div><\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"2-left-join\">2. LEFT JOIN\n<\/h2>\n\n\n<p>The LEFT JOIN is a join between 2 tables that returns all records from the left-hand table, even if there is no match with the right-hand table. If there is no match, missing values are set to NULL. As with the INNER JOIN, you need to specify the attributes to be used to perform the join: the ON keyword is used, followed by the desired equality after the tables in the LEFT JOIN have been filled in.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:301px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"left join sql\" decoding=\"async\" height=\"180\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/left-join-sql.png\" style=\"width:301px;max-width:100%;height:auto\" width=\"301\"\/><\/figure>\n\n\n<p>Here we&#8217;re doing a LEFT JOIN query against the email table, so we&#8217;re displaying all rows when the customer_id is present in the left-hand table, i.e. in the email table.\u00a0\u00a0<\/p>\n\n\n<p>\u00a0Thus the lines corresponding to customer_id 7 and 8 are not present in the query output.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:409px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"left join2\" decoding=\"async\" height=\"119\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/left-join2.png\" style=\"width:409px;max-width:100%;height:auto\" width=\"409\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"3-right-join\">3. RIGHT JOIN\n<\/h2>\n\n\n<p>The RIGHT JOIN is a join between 2 tables that returns all records from the right-hand table, even if there is no match with the left-hand table. If there is no match, missing values are set to NULL.\u00a0<\/p>\n\n\n<p>As with INNER JOIN and LEFT JOIN, you need to specify the attributes to be used to perform the join: use the ON keyword followed by the desired equality after entering the tables in the RIGHT JOIN.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:300px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"right join\" decoding=\"async\" height=\"180\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/right-join.png\" style=\"width:300px;max-width:100%;height:auto\" width=\"300\"\/><\/figure>\n\n\n<p>Here we&#8217;re doing a LEFT JOIN query against the email table, so we&#8217;re displaying all rows when the customer_id is present in the left-hand table, i.e. in the email table.\u00a0\u00a0<\/p>\n\n\n<p>\u00a0Thus the lines corresponding to customer_id 7 and 8 are not present in the query output.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:418px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"right join1\" decoding=\"async\" height=\"108\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/right-join1.png\" style=\"width:418px;max-width:100%;height:auto\" width=\"418\"\/><\/figure>\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center wp-container-core-buttons-is-layout-5ee10de4\" style=\"margin-top:32px;margin-bottom:32px\"><div class=\"wp-block-button\"><a class=\"wp-block-button__link wp-element-button\" href=\"\/en\/courses\/data-ai\/data-analyst\">Become an expert on SQL with Liora<\/a><\/div><\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"4-full-join\">4. FULL JOIN<\/h2>\n\n\n<p>The FULL JOIN is a join between 2 tables that returns all records from both tables, even if there is no match with the other. If there is no match, missing values are set to NULL. This join also requires the ON attribute.\u00a0<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:300px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Full join\" decoding=\"async\" height=\"180\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/Full-join.png\" style=\"width:300px;max-width:100%;height:auto\" width=\"300\"\/><\/figure>\n\n\n<p>Here, it doesn&#8217;t matter if there are no corresponding rows in one of the two tables, all the information from both tables is present. This is the join that keeps as much information as possible.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:464px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"FUll join1\" decoding=\"async\" height=\"136\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/FUll-join1.png\" style=\"width:464px;max-width:100%;height:auto\" width=\"464\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"5-natural-join\">5. NATURAL JOIN<\/h2>\n\n\n<p>The NATURAL JOIN is a join between 2 tables that returns records from both tables in a &#8220;natural&#8221; way.\u00a0 There must be at least one column with the same name in both tables. It performs a natural join by returning rows with pairs that match both tables.\u00a0\u00a0<\/p>\n\n\n<p>With NATURAL JOIN, the ON is not specified.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:414px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Natural join\" decoding=\"async\" height=\"97\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/08\/Natural-join.png\" style=\"width:414px;max-width:100%;height:auto\" width=\"414\"\/><\/figure>\n\n\n<p>The output shows that a join has been made between the two tables with &#8220;id_client&#8221;, as this is the only variable present in both tables.\u00a0<\/p>\n\n\n<p>But what&#8217;s the difference between NATURAL JOIN and INNER JOIN?\u00a0\u00a0<\/p>\n\n\n<p>The INNER JOIN avoids the repetition of equal rows, which is not possible with the NATURAL JOIN. The INNER JOIN returns a table based on the data specified in the ON, whereas the NATURAL JOIN returns a table based on a column with the same name and type in both tables.\u00a0\u00a0<\/p>\n\n\n<p>\u00a0The four types of SQL joins to remember are :\u00a0<\/p>\n\n\n<ul class=\"wp-block-list\"><li>INNER JOIN to retrieve information between two tables when the ON condition is met on both tables,\u00a0<\/li><li>LEFT \/ RIGHT JOIN to retrieve information between two tables, keeping all information from one of the two tables,\u00a0<\/li><li>FULL JOIN retrieves all information from both tables without loss of information (but may display many NULLs),\u00a0<\/li><li>NATURAL JOIN performs a natural join between the two tables, so there&#8217;s no need to specify the ON.\u00a0\u00a0<\/li><\/ul>\n\n\n<p>Joins are an efficient way of associating several tables. There are several types, and we&#8217;ve already seen the main ones.\u00a0<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"<p>If you&#8217;re a regular reader of our blog, you&#8217;ve already heard of SQL or &#8220;Structured Query Language&#8221;: a programming language for managing databases, and you know all about the importance of SQL joins. What is SQL Joins ? If you missed the article on the\u00a0basics of SQL language. We&#8217;ll give it to you again here. [&hellip;]<\/p>\n","protected":false},"author":85,"featured_media":169651,"comment_status":"open","ping_status":"open","sticky":false,"template":"elementor_theme","format":"standard","meta":{"_acf_changed":false,"editor_notices":[],"footnotes":""},"categories":[2433],"class_list":["post-169649","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-ai"],"acf":[],"_links":{"self":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/169649","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/users\/85"}],"replies":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/comments?post=169649"}],"version-history":[{"count":5,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/169649\/revisions"}],"predecessor-version":[{"id":209856,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/169649\/revisions\/209856"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media\/169651"}],"wp:attachment":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media?parent=169649"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/categories?post=169649"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}