Damian Phillips
asked on
Difference between an Inner-Join and an Equi-Join in SQL Server 2008
favorite
I have 2 tables called 'table123' and 'table246'.
'table123' columns: 'ID', 'Dept_ID', 'First_Name', 'Surname', 'Salary', 'Address'.
'table246' columns: 'Dept_ID', 'Dept_Name'.
I want to find the list of employees with the lowest salary per department. Two of the ways I can do it is an Equi-Join or an Inner-Join. I've been told they can both be used to provide the desired result.
The queries I used:
Equi-Join:
Inner Join:
The Equi-Join statement gives me the desired table, containing the columns 'First_Name', 'Dept_Name' & 'Min_Salary', with all relevant data.
However, the Inner-Join statement doesn't run because the First_Name column needs to be included in the aggregate function or GROUP BY clause. This really confuses me, as I don't know how to go about fixing it. How can I adjust the Inner-Join query, so as to give the same result as the Equi-Join query?
I have 2 tables called 'table123' and 'table246'.
'table123' columns: 'ID', 'Dept_ID', 'First_Name', 'Surname', 'Salary', 'Address'.
'table246' columns: 'Dept_ID', 'Dept_Name'.
I want to find the list of employees with the lowest salary per department. Two of the ways I can do it is an Equi-Join or an Inner-Join. I've been told they can both be used to provide the desired result.
The queries I used:
Equi-Join:
SELECT First_Name, b.Dept_Name, alt.Min_Salary AS Min_Salary
FROM table123 a, table246 b,
(SELECT Dept_ID, MIN(Salary)Min_Salary
FROM table123
GROUP BY Dept_ID)alt
WHERE a.Dept_ID = b.Dept_ID
AND a.salary = alt.Min_Salary
AND a.Dept_ID = alt.Dept_ID;
Inner Join:
SELECT MIN(Salary)Min_Salary, Dept_Name
FROM table123 a, table246 b
INNER JOIN (SELECT First_Name, MIN(Salary)
FROM table123
GROUP BY Dept_ID)alt
ON b.Dept_ID = alt.Dept_ID;
The Equi-Join statement gives me the desired table, containing the columns 'First_Name', 'Dept_Name' & 'Min_Salary', with all relevant data.
However, the Inner-Join statement doesn't run because the First_Name column needs to be included in the aggregate function or GROUP BY clause. This really confuses me, as I don't know how to go about fixing it. How can I adjust the Inner-Join query, so as to give the same result as the Equi-Join query?
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
by the way, if you just wanted a department name and and minimum salary with department, then:
select
d.Dept_Name
, min(Salary) AS min_salary
from table246 AS d
inner join table123 AS e on d.Dept_ID = e.Dept_ID
group by
d.Dept_Name
If you are after the person with the minimum salary in each department then I recommend Scott's query to you
select
d.Dept_Name
, min(Salary) AS min_salary
from table246 AS d
inner join table123 AS e on d.Dept_ID = e.Dept_ID
group by
d.Dept_Name
If you are after the person with the minimum salary in each department then I recommend Scott's query to you
select a.first_name, b.min_salary, c.dept_name
from table123 a
inner join
(select dept_id, min(salary) min_salary
from table123
group by dept_id) b
on a.dept_id = b.dept_id
and a.salary = b.min_salary
inner join
select dept_id, dept_name
from table 246 c
on a.dept_id = c.dept_id
from table123 a
inner join
(select dept_id, min(salary) min_salary
from table123
group by dept_id) b
on a.dept_id = b.dept_id
and a.salary = b.min_salary
inner join
select dept_id, dept_name
from table 246 c
on a.dept_id = c.dept_id
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
My main problem is that I don't understand how Equi-Joins work and I just need help understanding it, so I can make one from scratch and get the desired result.