how to work with Multiple tables in SQL for tester– Understanding joins

We have gained knowledge on basic queries in SQL working with a single table in previous articles. Let us extend our knowledge working with multiple tables in SQL. Below are the tables, we will use in our examples.

This post will mainly discuss on concept of joins in SQL.In the next post, we will go further and discuss on nested queries, union, and other useful concepts on SQL.

Problem 1: From the above tables, provide Org_Name for each of the users in User Table?

Answer: First of all, we need to identify which information we need to extract from above table. We need Org_Name from Organisation table and Username from User Table.

Second Point is to identify the common attribute/column in two tables based on which we will create the relation between two tables. As in above tables, Org_Id is the foreign Key in table User which is mapped with Id in the Organisation table and is used to create a relation between the two tables.

We can use joins to get data the two tables. JOIN is used to combine rows from two or more tables, based on a common field between them. In this problem, we will use join or inner join or a equi – join to extract the required information

•  INNER JOIN: Returns all rows when there is at least one match in BOTH tables. An Inner Join is same as Join or equi-join.

•  Since we have make a match between Organisation and user table on Org_Id, we will create inner join or equi-join or simply join as shown in example below

• Syntax - Join : Select * from Organisation a join User b on a.Id = b.Org_Id

• Syntax - InnerJoin : Select * from Organisation a innerjoin User b on a.Id = b.Org_Id

• Syntax – Equi-Join : Select * from Organisation a,User b where a.Id = b.Org_Id

Join and Inner Join are same thing and fetches the same results as Equi-Join between two tables.

Other than Inner Join or equi-Join, Other examples of joins are following:

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

• Syntax- Left Join : Select * from Organisation a LEFT join User b on a.Id = b.Org_Id

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

• Syntax- Right Join : Select * from Organisation a Right join User b on a.Id = b.Org_Id

• FULL JOIN: Return combined result of both LEFT and RIGHT joins.

• Syntax- Full Join : Select * from Organisation a full outer join User b on a.Id = b.Org_Id

In above problem, we will use an inner join and can use any syntax from Join, inner-join or equi-join.Since we require Org_Name and User_Name in Output, Instead of fetching all information using Select *, we will extract Org_Name from Organisation table and similarly User_Name from User table. Also we can use alias to giving meaningful name to column. The syntax will be :

Select a.Org_Name as OrganisationName ,b.User_Name as User from Organisation a join User b on a.Id = b.Org_Id

Problem 2: In the above tables, provide Org_Name, Salary and Designation for each of the user in User Table?

Answer: In this example we need to create multiple join between tables. The query will be as follows. Try to understand the query with the concept explained in previous table.

Select us.User_Name, org.Org_Name, us.Salary, des.staff_role from User us join Organisation org on us.Org_Id =org.Id join User_Designation usd on us.Id = usd.User_Id join Designation des on usd.Designation_Id = des.Id