Selenium WebDriver Java Framework Course Limited Time Offer for $20

Selenium WebDriver Java Framework Course Limited Time Offer for $20

 

Print

Using Joins in SQL Statement

When doing database testing, we may need to join tables to retrieve information from multiple tables, views or query result sets. For joining tables, we can use JOIN in SQL statement.

 

Inner Joins

Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table.

For example, here is an inner join retrieving the BusinessEntityID, FirstName and LastName from the Person table and retrieve corresponding person's phone number from the PersonPhone table.

Use AdventureWorks2008
Go

SELECT top 10 
p.BusinessEntityID,
p.FirstName,
p.LastName,
ph.PhoneNumber
from Person.Person p
inner join 
Person.PersonPhone ph
on p.BusinessEntityID=ph.BusinessEntityID

Outer Joins:

Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.

Left Joins

All rows are retrieved from the left table referenced with a left outer join.

Use AdventureWorks2008
Go 
--find products that does not have any transaction history
select p.ProductID,p.Name,t.TransactionID,t.Quantity
from Production.Product p left join Production.TransactionHistory t
on p.ProductID=t.ProductID
where t.Quantity is null

--if we make right join, the above query does not return any records because in the transaction table, all records are included
select p.ProductID,p.Name,t.TransactionID,t.Quantity
from Production.Product p right join Production.TransactionHistory t
on p.ProductID=t.ProductID
where t.Quantity is null

Right Joins

All rows from the right table referenced in a right outer join.

Use AdventureWorks2008
Go 
-- using Right Join, find products that have quantity of sale equals to 1
select p.ProductID,p.Name,t.TransactionID,t.Quantity
from Production.Product p right join Production.TransactionHistory t
on p.ProductID=t.ProductID
where t.Quantity=1 

All rows from both tables are returned in a full outer join.

 

Use AdventureWorks2008
Go
create table #a(TestCaseID int Primary Key,TestCaseName varchar(50))
create table #b (TestCaseID int,TestModule varchar(50))
insert #a(TestCaseID,TestCaseName) values(1,'Login Test')
insert #a(TestCaseID,TestCaseName) values(2,'Change User Password')
insert #a(TestCaseID,TestCaseName) values(3,'Change User Preferences')
insert #a(TestCaseID,TestCaseName) values(4,'Change User Address')

insert #b(TestCaseID,TestModule) values(1,'Login Module')
insert #b(TestCaseID,TestModule) values(2,'User Settings Module')
insert #b(TestCaseID,TestModule) values(3,'User Settings Module')
insert #b(TestCaseID,TestModule) values(5,'Use Profile Module')
--table a full outer join table b will include all records from table a and b
select a.TestCaseID as TestCaseID_A, TestCaseName,b.TestCaseID as TestCaseID_B, TestModule
 from #a a full outer join #b b
on a.TestCaseID=b.TestCaseID

Query Result