SQL Full Join

Last modified on: December 3, 2024

<div class="flex-shrink-0 flex flex-col relative items-end">
<div>
<div class="pt-0">
<div class="gizmo-bot-avatar flex h-8 w-8 items-center justify-center overflow-hidden rounded-full">&nbsp;</div>
</div>
</div>
</div>
<div class="group/conversation-turn relative flex w-full min-w-0 flex-col agent-turn">
<div class="flex-col gap-1 md:gap-3">
<div class="flex max-w-full flex-col flex-grow">
<div class="min-h-8 text-message flex w-full flex-col items-end gap-2 whitespace-normal break-words [.text-message+&amp;]:mt-5" dir="auto" data-message-author-role="assistant" data-message-id="7153caad-7bf2-457c-8a0c-3b701e576bc7" data-message-model-slug="gpt-4o">
<div class="flex w-full flex-col gap-1 empty:hidden first:pt-[3px]">
<div class="markdown prose w-full break-words dark:prose-invert light">
<h3><strong>SQL FULL JOIN (or FULL OUTER JOIN): A Complete Guide</strong></h3>
<h4><strong>What is a FULL JOIN?</strong></h4>
<p>A <strong>FULL JOIN</strong> (or <strong>FULL OUTER JOIN</strong>) combines the results of a <strong>LEFT JOIN</strong> and a <strong>RIGHT JOIN</strong>. It returns all rows from both tables, matching rows where possible. If there is no match, it fills the missing values with <strong>NULL</strong> for the respective columns.</p>
<p>In other words:</p>
<ul>
<li>Rows from the <strong>left table</strong> with no match in the <strong>right table</strong> will still appear in the result with <strong>NULL</strong> for the right table's columns.</li>
<li>Rows from the <strong>right table</strong> with no match in the <strong>left table</strong> will appear with <strong>NULL</strong> for the left table's columns.</li>
</ul>
<hr />
<h3><strong>Syntax of FULL JOIN</strong></h3>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT column_names
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;
</code></pre>
</div>
</div>
</div>
</div>
<ul>
<li><strong>table1</strong>: The first table (left table).</li>
<li><strong>table2</strong>: The second table (right table).</li>
<li><strong>column_name</strong>: The column used to match rows between the two tables.</li>
</ul>
<p>Some database systems (e.g., MySQL) do not support <strong>FULL JOIN</strong> directly but simulate it using a combination of <strong>LEFT JOIN</strong>, <strong>RIGHT JOIN</strong>, and <strong>UNION</strong>.</p>
<hr />
<h3><strong>Example of FULL JOIN</strong></h3>
<h4><strong>Scenario: Joining <code>employees</code> and <code>departments</code> tables</strong></h4>
<p>Let&rsquo;s say you have two tables: <code>employees</code> and <code>departments</code>. You want to retrieve all employees and all departments, including cases where there&rsquo;s no match between the two.</p>
<h4><strong>Table: employees</strong></h4>
<table>
<thead>
<tr>
<th>employee_id</th>
<th>employee_name</th>
<th>department_id</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>John Smith</td>
<td>1</td>
</tr>
<tr>
<td>2</td>
<td>Jane Doe</td>
<td>2</td>
</tr>
<tr>
<td>3</td>
<td>Mike Johnson</td>
<td>NULL</td>
</tr>
</tbody>
</table>
<h4><strong>Table: departments</strong></h4>
<table>
<thead>
<tr>
<th>department_id</th>
<th>department_name</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>IT</td>
</tr>
<tr>
<td>2</td>
<td>HR</td>
</tr>
<tr>
<td>3</td>
<td>Marketing</td>
</tr>
</tbody>
</table>
<h4><strong>FULL JOIN Query Example:</strong></h4>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT e.employee_name, e.department_id, d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id;
</code></pre>
</div>
</div>
</div>
</div>
<p><strong>Output Example:</strong></p>
<table>
<thead>
<tr>
<th>employee_name</th>
<th>department_id</th>
<th>department_name</th>
</tr>
</thead>
<tbody>
<tr>
<td>John Smith</td>
<td>1</td>
<td>IT</td>
</tr>
<tr>
<td>Jane Doe</td>
<td>2</td>
<td>HR</td>
</tr>
<tr>
<td>Mike Johnson</td>
<td>NULL</td>
<td>NULL</td>
</tr>
<tr>
<td>NULL</td>
<td>3</td>
<td>Marketing</td>
</tr>
</tbody>
</table>
<p>In this query:</p>
<ul>
<li><strong><code>e</code></strong> is the alias for the <strong><code>employees</code></strong> table.</li>
<li><strong><code>d</code></strong> is the alias for the <strong><code>departments</code></strong> table.</li>
<li>The <strong><code>FULL JOIN</code></strong> ensures that all rows from both tables are included in the result, even if there is no match.</li>
</ul>
<hr />
<h3><strong>Explanation of FULL JOIN</strong></h3>
<ul>
<li>The row <strong>Mike Johnson</strong> from the <code>employees</code> table is included even though there is no matching department.</li>
<li>The row <strong>Marketing</strong> from the <code>departments</code> table is included even though there is no matching employee.</li>
<li>Where matches exist (e.g., IT and HR), corresponding data is combined into one row.</li>
</ul>
<hr />
<h3><strong>Simulating FULL JOIN in MySQL</strong></h3>
<p>MySQL does not support <strong>FULL JOIN</strong> directly. To achieve the same result, you can use a combination of <strong>LEFT JOIN</strong>, <strong>RIGHT JOIN</strong>, and <strong>UNION</strong>:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT e.employee_name, e.department_id, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id

UNION

SELECT e.employee_name, e.department_id, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;
</code></pre>
</div>
</div>
</div>
</div>
<p>This query combines the results of a <strong>LEFT JOIN</strong> and a <strong>RIGHT JOIN</strong>, ensuring that all unmatched rows from both tables are included.</p>
<hr />
<h3><strong>FULL JOIN with Multiple Tables</strong></h3>
<p>You can perform a <strong>FULL JOIN</strong> with multiple tables, but this requires chaining multiple joins. For example:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>SELECT e.employee_name, d.department_name, l.location
FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id
FULL JOIN locations l ON d.location_id = l.location_id;
</code></pre>
</div>
</div>
</div>
</div>
<p>This query will include all rows from the <code>employees</code>, <code>departments</code>, and <code>locations</code> tables, even if there&rsquo;s no match in any of the tables.</p>
<hr />
<h3><strong>Key Points to Remember</strong></h3>
<ul>
<li><strong>FULL JOIN</strong> includes all rows from both tables.</li>
<li>Rows with no match in one table are filled with <strong>NULL</strong> for the corresponding columns.</li>
<li>Some databases (e.g., MySQL) do not support <strong>FULL JOIN</strong> directly, so you may need to simulate it using <strong>LEFT JOIN</strong>, <strong>RIGHT JOIN</strong>, and <strong>UNION</strong>.</li>
</ul>
<hr />
<h3>&nbsp;</h3>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More