SQL Day 13: Advance Joins

LO1: Retrieve only non-matching rows from the left table
LO2: Retrieve only non-matching rows from the right table
LO3: Retrieve only non-matching rows from both the right and the left table

Prerequisite: Joins

Retrieve only non-matching rows from the left table

In the previous tutorial, we have seen that how to retrieve the matching rows + non-matching from left the table using Left Outer Join.



Now here we are finding only the non-matching rows from the left table as you can see in below figure.



So to achieve the above result, we can use the IS NULL with where clause.

Let’s see how we can use the IS NULL to filter out the rows using where clause in Left Outer Join.
In table tblemployee we have the record that has DepartmentID is NULL as you can see in the below figure.



When we apply left outer join on tblemployee and tbldepartment table then we get the matching rows from the left table as well as non-matching rows from the left table.

select         Name, Email, Salary, DepartmentName
from  tblEmployee e
LEFT Join tblDepartment d
ON          e.DepartmentID=d.ID



In above figure, employee Tim has departmentid NULL

So what our intention is to get only the non-matching rows from the left table i.e. to get only the Tim record, to do that we need to add the where clause with IS NULL

select         Name, Email, Salary, DepartmentName
from  tblEmployee e
LEFT Join tblDepartment d
ON          e.DepartmentID=d.ID
Where  e.DepartmentID IS NULL

In this way, we can find only the non-matching rows from the left table.

Retrieve only non-matching rows from the right table

Here we are finding only the non-matching rows from the right table as you can see in below figure.


The query will be the same as left outer join but this time we need to use the right join instead of left outer join.

select         Name, Email, Salary, DepartmentName
from  tblEmployee e
Right Join tblDepartment d
ON          e.DepartmentID=d.ID
Where  e.DepartmentID IS NULL

This will get the non-matching rows from the right table.


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

As we see in the previous session that Full outer join will retrieve non-matching rows from both the table + matching rows from the both the table.

Now we have to only retrieve the non-matching rows both the tables as below figure.


To retrieve only the non-matching rows we need run the below query.

select         Name, Email, Salary, DepartmentName
from  tblEmployee e
FULL Join tblDepartment d
ON          e.DepartmentID=d.ID
Where  e.DepartmentID IS NULL
OR          d.id IS NULL



So these are the ways to find out the only the non-matching rows from the tables.
That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading