{"id":348,"date":"2026-11-15T09:40:00","date_gmt":"2026-11-15T09:40:00","guid":{"rendered":"https:\/\/backendside.com\/blog\/?p=348"},"modified":"2026-11-21T12:15:12","modified_gmt":"2026-11-21T12:15:12","slug":"sql-joins-explained-inner-left-right","status":"publish","type":"post","link":"https:\/\/backendside.com\/blog\/2026\/11\/15\/sql-joins-explained-inner-left-right\/","title":{"rendered":"JOINs Demystified: INNER, LEFT, RIGHT and the Ones Nobody Uses"},"content":{"rendered":"<p class=\"lead\">Relational databases split data into separate tables on purpose &mdash; customers in one, orders in another &mdash; so nothing is stored twice. A <strong>JOIN<\/strong> is how you put them back together when you query. Most people learn <code>INNER JOIN<\/code>, pick up <code>LEFT JOIN<\/code> by trial and error, and treat the rest as trivia. That works until a report quietly drops rows or doubles a total. This guide uses one tiny dataset to show what every join type returns in MySQL and MariaDB, and the traps that catch experienced developers.<\/p>\n<h2>The sample data<\/h2>\n<p>Two tables. Notice the awkward cases on purpose: Chen has no orders, and order 104 points at customer 9, who does not exist (there is no foreign key to stop it).<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(50));\nCREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, total DECIMAL(8,2));\n\nINSERT INTO customers VALUES (1,'Ayesha'), (2,'Bilal'), (3,'Chen');\nINSERT INTO orders VALUES (101,1,50.00), (102,1,20.00), (103,2,75.00), (104,9,30.00);<\/code><\/pre>\n<h2>INNER JOIN: only the matches<\/h2>\n<p>An inner join returns rows where the join condition is true on <em>both<\/em> sides. Anything without a partner disappears.<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SELECT c.name, o.id AS order_id, o.total\nFROM customers c\nINNER JOIN orders o ON o.customer_id = c.id;\n\n-- name   | order_id | total\n-- Ayesha |      101 | 50.00\n-- Ayesha |      102 | 20.00\n-- Bilal  |      103 | 75.00<\/code><\/pre>\n<p>Chen is gone (no orders) and so is order 104 (no customer). In MySQL and MariaDB, plain <code>JOIN<\/code> means <code>INNER JOIN<\/code>.<\/p>\n<h2>LEFT JOIN: everything on the left, matches where they exist<\/h2>\n<p>A left join keeps every row from the left table. Where there is no match on the right, the right-hand columns come back as <code>NULL<\/code>.<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SELECT c.name, o.id AS order_id, o.total\nFROM customers c\nLEFT JOIN orders o ON o.customer_id = c.id;\n\n-- name   | order_id | total\n-- Ayesha |      101 | 50.00\n-- Ayesha |      102 | 20.00\n-- Bilal  |      103 | 75.00\n-- Chen   |     NULL |  NULL<\/code><\/pre>\n<p>This is the join for &ldquo;all customers, with their orders if any&rdquo; &mdash; the right choice for most reports where the main entity must not vanish.<\/p>\n<h2>RIGHT JOIN: the mirror image<\/h2>\n<p>A right join keeps every row from the <em>right<\/em> table instead:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SELECT c.name, o.id AS order_id, o.total\nFROM customers c\nRIGHT JOIN orders o ON o.customer_id = c.id;\n\n-- name   | order_id | total\n-- Ayesha |      101 | 50.00\n-- Ayesha |      102 | 20.00\n-- Bilal  |      103 | 75.00\n-- NULL   |      104 | 30.00<\/code><\/pre>\n<p>Any right join can be rewritten as a left join by swapping the table order, and most teams do exactly that so queries read consistently from the main table outward. That is why you rarely see <code>RIGHT JOIN<\/code> in the wild.<\/p>\n<h2>At a glance<\/h2>\n<table>\n<thead>\n<tr>\n<th>Join<\/th>\n<th>Keeps unmatched left rows?<\/th>\n<th>Keeps unmatched right rows?<\/th>\n<th>Rows from our data<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>INNER JOIN<\/code><\/td>\n<td>No<\/td>\n<td>No<\/td>\n<td>3<\/td>\n<\/tr>\n<tr>\n<td><code>LEFT JOIN<\/code><\/td>\n<td>Yes<\/td>\n<td>No<\/td>\n<td>4 (adds Chen)<\/td>\n<\/tr>\n<tr>\n<td><code>RIGHT JOIN<\/code><\/td>\n<td>No<\/td>\n<td>Yes<\/td>\n<td>4 (adds order 104)<\/td>\n<\/tr>\n<tr>\n<td><code>FULL OUTER JOIN<\/code><\/td>\n<td>Yes<\/td>\n<td>Yes<\/td>\n<td>5 &mdash; but not supported natively; emulate it<\/td>\n<\/tr>\n<tr>\n<td><code>CROSS JOIN<\/code><\/td>\n<td colspan=\"2\">Every combination, no condition<\/td>\n<td>12 (3 &times; 4)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">MyDBDesk &mdash; See the Join Paths Before You Write Them<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">The hardest part of a JOIN in an unfamiliar database is knowing which column links to which. <strong>MyDBDesk<\/strong>&rsquo;s <strong>Visual ER Designer<\/strong> shows your database as a draggable diagram &mdash; table boxes with their columns, and foreign keys drawn as lines &mdash; so the join paths are right in front of you. Pan and zoom the canvas, and the layout is saved per database for next time.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/mydbdesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore MyDBDesk &rarr;<\/a>\n<\/div>\n<h2>The ones nobody uses (and when you should)<\/h2>\n<h3>FULL OUTER JOIN &mdash; which MySQL does not have<\/h3>\n<p>Standard SQL&rsquo;s full outer join keeps unmatched rows from both sides. Neither MySQL nor MariaDB supports <code>FULL OUTER JOIN<\/code>, but you can emulate it with a <code>UNION<\/code> of a left and a right join:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SELECT c.name, o.id AS order_id, o.total\nFROM customers c LEFT JOIN orders o ON o.customer_id = c.id\nUNION\nSELECT c.name, o.id AS order_id, o.total\nFROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;<\/code><\/pre>\n<p><code>UNION<\/code> removes the duplicated matched rows. It is genuinely useful for reconciliation &mdash; comparing two lists and seeing what is missing from each side.<\/p>\n<h3>CROSS JOIN &mdash; every combination<\/h3>\n<p>A cross join pairs every row of one table with every row of another. Usually it is a bug (a join with a forgotten condition behaves the same way), but deliberately it is handy for generating grids &mdash; every product for every month in a sales report, for example, so months with zero sales still appear.<\/p>\n<h3>Self join &mdash; a table joined to itself<\/h3>\n<p>When rows refer to other rows in the same table, join the table to itself with two aliases:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- employees(id, name, manager_id)\nSELECT e.name AS employee, m.name AS manager\nFROM employees e\nLEFT JOIN employees m ON m.id = e.manager_id;<\/code><\/pre>\n<h3>Anti-join &mdash; rows with no match<\/h3>\n<p>&ldquo;Customers who have never ordered&rdquo; is one of the most common questions in business reporting. Two idiomatic ways to ask it:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- LEFT JOIN ... IS NULL\nSELECT c.name\nFROM customers c\nLEFT JOIN orders o ON o.customer_id = c.id\nWHERE o.id IS NULL;            -- returns Chen\n\n-- NOT EXISTS (often clearer, and handles NULLs safely)\nSELECT c.name\nFROM customers c\nWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);<\/code><\/pre>\n<p>Avoid <code>NOT IN (SELECT customer_id FROM orders)<\/code> for this: if the subquery returns even one <code>NULL<\/code>, <code>NOT IN<\/code> returns no rows at all.<\/p>\n<h2>Three traps that produce wrong answers<\/h2>\n<h3>1. Filtering a LEFT JOIN in WHERE<\/h3>\n<p>Say you want every customer and only their orders over 40:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- Wrong: Chen disappears, because o.total is NULL for him\nSELECT c.name, o.total\nFROM customers c\nLEFT JOIN orders o ON o.customer_id = c.id\nWHERE o.total &gt; 40;\n\n-- Right: put the condition on the joined table in ON\nSELECT c.name, o.total\nFROM customers c\nLEFT JOIN orders o ON o.customer_id = c.id AND o.total &gt; 40;<\/code><\/pre>\n<p>A <code>WHERE<\/code> condition on the right-hand table throws away the <code>NULL<\/code> rows, silently turning your left join into an inner join. Conditions that decide <em>which rows match<\/em> belong in <code>ON<\/code>; conditions that decide <em>which results you keep<\/em> belong in <code>WHERE<\/code>.<\/p>\n<h3>2. Fan-out doubles your totals<\/h3>\n<p>Join customers to orders <em>and<\/em> to payments, then <code>SUM(o.total)<\/code>, and every order is counted once per payment. Aggregate each child table in a subquery or CTE first, then join the summaries:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>WITH order_totals AS (\n  SELECT customer_id, SUM(total) AS spent\n  FROM orders GROUP BY customer_id\n)\nSELECT c.name, COALESCE(t.spent, 0) AS spent\nFROM customers c\nLEFT JOIN order_totals t ON t.customer_id = c.id;<\/code><\/pre>\n<h3>3. NULL never equals NULL<\/h3>\n<p>Join conditions use <code>=<\/code>, and <code>NULL = NULL<\/code> is not true. Rows with a <code>NULL<\/code> join key never match anything. If you genuinely need to match NULLs, MySQL and MariaDB offer the null-safe operator <code>&lt;=&gt;<\/code>.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">MyDBDesk &mdash; Experiment With Joins, Keep the Good Ones<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">Trying INNER against LEFT on the same data is the fastest way to learn the difference. In <strong>MyDBDesk<\/strong>&rsquo;s <strong>SQL console<\/strong>, select a few lines and run just the selection, and every statement is remembered in the history drop-down. <strong>Saved queries<\/strong> keep the joins you use often under a name. Stuck? Ask the private, on-device <strong>AI assistant<\/strong> in plain English with the current schema as context &mdash; any SQL it suggests is shown for you to review, with a Copy SQL button to take it to the editor yourself.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/mydbdesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore MyDBDesk &rarr;<\/a>\n<\/div>\n<h2>Making joins fast<\/h2>\n<ul>\n<li><strong>Index the join columns.<\/strong> Primary keys are indexed automatically; foreign-key columns such as <code>orders.customer_id<\/code> should be too. InnoDB creates an index when you declare a foreign key, but a column without a declared key has none unless you add it.<\/li>\n<li><strong>Match data types and collations<\/strong> on both sides. Joining an <code>INT<\/code> to a <code>VARCHAR<\/code>, or two text columns with different collations, can stop the index being used.<\/li>\n<li><strong>Select only the columns you need<\/strong>, especially with wide tables.<\/li>\n<li><strong>Check the plan with <code>EXPLAIN<\/code>.<\/strong> For each table in the join you want an indexed access type such as <code>eq_ref<\/code> or <code>ref<\/code>, not <code>ALL<\/code>.<\/li>\n<\/ul>\n<p>If you would rather have plain-English help reading a complex join, we looked at how an on-device assistant handles that in <a href=\"https:\/\/backendside.com\/blog\/2026\/07\/18\/ai-assistant-in-a-database-manager\/\">an AI assistant in a database manager<\/a>.<\/p>\n<h2>Key takeaways<\/h2>\n<ul>\n<li><strong><code>INNER JOIN<\/code><\/strong> keeps only matches; <strong><code>LEFT JOIN<\/code><\/strong> keeps every row of the main table, with <code>NULL<\/code>s where nothing matches.<\/li>\n<li><strong><code>RIGHT JOIN<\/code><\/strong> is a left join written backwards &mdash; most teams standardise on <code>LEFT<\/code>.<\/li>\n<li>MySQL and MariaDB have <strong>no <code>FULL OUTER JOIN<\/code><\/strong>; emulate it with a <code>UNION<\/code> of left and right joins.<\/li>\n<li>Find rows with no match using <strong><code>LEFT JOIN ... IS NULL<\/code> or <code>NOT EXISTS<\/code><\/strong>, not <code>NOT IN<\/code>.<\/li>\n<li>Put conditions on the joined table in <strong><code>ON<\/code>, not <code>WHERE<\/code><\/strong>, or your left join becomes an inner join.<\/li>\n<li>Pre-aggregate before joining several child tables to avoid <strong>fan-out<\/strong>, and index every join column.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>One small dataset, every kind of JOIN: INNER, LEFT, RIGHT, the FULL OUTER JOIN that MySQL does not have, CROSS and self joins, plus anti-joins and the ON-versus-WHERE trap that silently turns a LEFT JOIN into an INNER one.<\/p>\n","protected":false},"author":1,"featured_media":448,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,2],"tags":[24,25,23,30],"class_list":["post-348","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","category-windows","tag-mariadb","tag-mydbdesk","tag-mysql","tag-sql-joins"],"_links":{"self":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/348","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/comments?post=348"}],"version-history":[{"count":1,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/348\/revisions"}],"predecessor-version":[{"id":449,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/348\/revisions\/449"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media\/448"}],"wp:attachment":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media?parent=348"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/categories?post=348"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/tags?post=348"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}