CODEWITHSUNDEEP
sql-server-2008
CrashBandicoot
CrashBandicoot

Reputation: 399

Difference between Equi-Join and Inner-Join in SQL

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?

Upvotes: 3

Views: 1428

Answers (1)

MayowaO
MayowaO

Reputation: 370

Try this:

SELECT a.First_Name, b.Dept_Name, alt.Min_Salary AS Min_Salary
FROM table123 a
INNER JOIN table246 b
    ON a.Dept_ID = b.Dept_ID
INNER JOIN (
            SELECT Dept_ID, MIN(Salary) Min_Salary
            FROM table123
            GROUP BY Dept_ID
           ) alt
    ON b.Dept_ID = alt.Dept_ID
WHERE a.Salary = alt.Min_Salary;

Upvotes: 3

Related Questions