Murtaza Badshah Murtaza Badshah - 4 months ago 13
SQL Question

Joining two tables in SQL Server when one table has huge data and other has few data

If I want to join two tables (not inner join), Left table has huge data (millions of record), and right table has few records. What should I prefer (Left or Right Outer join) and why.

Answer

Well first of all That join is independent of the size of the tables

Well I think this depends what data you want either from Left table or from right table let us assume you have two tables Employees which has millions of records let us put this in right and Department which has 10 record put it in left Now each employee has one department.

Employee

  • EmpID
  • DepartmentId

    Department

  • DepartmentId

  • Department Name

Now Suppose You want to know which employee belongs to which Department Use This Query.

Select e.empId,d.DepartmentName
from employee e
join department d
on e.departmentid=d.departmentid

Now Suppose You want to know which employee has now assigned any department use the below query

 Select e.empId,d.DepartmentName
from employee e
left join department d
on e.departmentid=d.departmentid
where d.departmentid is null

Now Suppose You want to know how many employees which depratment use the below query

Select d.[Department Name],COUNT(e.empID) from Employee e
left join Department d
on e.DepartmentId=d.DepartmentId
group by d.[Department Name]

For more information about joins please use the below image Sql Joins

Comments