Advanced Join Techniques in SQL: Examples and Best Practices

Here we will learn about:

• Retrieve only the non matching rows from the left table
• Retrieve only the non matching rows from the right table
• Retrieve only the non matching rows from both the left and right table

How to retrieve only the non matching rows from the left table. The output should be as shown below:

Code:

<span class="pln">
SELECT       </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Gender</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Salary</span><span class="pun">,</span><span class="pln"> </span><span class="typ">DepartmentName</span><span class="pln">
FROM         tblEmployee E
LEFT JOIN    tblDepartment D
ON           E</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> D</span><span class="pun">.</span><span class="typ">Id</span><span class="pln">
WHERE        D</span><span class="pun">.</span><span class="typ">Id</span><span class="pln"> IS NULL
</span>

How to retrieve only the non matching rows from the right table

Code:

<span class="pln">
SELECT         </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Gender</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Salary</span><span class="pun">,</span><span class="pln"> </span><span class="typ">DepartmentName</span><span class="pln">
FROM           tblEmployee E
RIGHT JOIN     tblDepartment D
ON             E</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> D</span><span class="pun">.</span><span class="typ">Id</span><span class="pln">
WHERE          E</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> IS NULL
</span>

How to retrieve only the non matching rows from both the left and right table. Matching rows should be eliminated.

Code:

<span class="pln">
SELECT         </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Gender</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Salary</span><span class="pun">,</span><span class="pln"> </span><span class="typ">DepartmentName</span><span class="pln">
FROM           tblEmployee E
FULL JOIN      tblDepartment D
ON             E</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> D</span><span class="pun">.</span><span class="typ">Id</span><span class="pln">
WHERE          E</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> IS NULL
OR             D</span><span class="pun">.</span><span class="typ">Id</span><span class="pln"> IS NULL
</span>

Code: SQL Script to create tblEmployee and tblDepartment tables

<span class="pln">
  
</span><span class="typ">Create</span><span class="pln"> table tblDepartment
</span><span class="pun">(</span><span class="pln">
     ID </span><span class="kwd">int</span><span class="pln"> primary key</span><span class="pun">,</span><span class="pln">
     </span><span class="typ">DepartmentName</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
     </span><span class="typ">Location</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
     </span><span class="typ">DepartmentHead</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Go</span><span class="pln">

</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblDepartment values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'IT'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'London'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rick'</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblDepartment values </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Payroll'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Delhi'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Ron'</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblDepartment values </span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'HR'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'New York'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Christie'</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblDepartment 
values </span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Other Department'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Sydney'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Cindrella'</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Go</span><span class="pln">

</span><span class="typ">Create</span><span class="pln"> table tblEmployee
</span><span class="pun">(</span><span class="pln">
     ID </span><span class="kwd">int</span><span class="pln"> primary key</span><span class="pun">,</span><span class="pln">
     </span><span class="typ">Name</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
     </span><span class="typ">Gender</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
     </span><span class="typ">Salary</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
     </span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> foreign key references tblDepartment</span><span class="pun">(</span><span class="typ">Id</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Go</span><span class="pln">

</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Azam'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Roma'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Female'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Inza'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3500</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Jaman'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4500</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">5</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Samser'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2800</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">6</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Kamran'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">7000</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Sara'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Female'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4800</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">8</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Valarie'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Female'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">5500</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">9</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rumman'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">6500</span><span class="pun">,</span><span class="pln"> NULL</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblEmployee values </span><span class="pun">(</span><span class="lit">10</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Russell'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Male'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">8800</span><span class="pun">,</span><span class="pln"> NULL</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Go</span><span class="pln">
</span>