Difference between Equi-Join and Inner-Join in SQL

[sachinbhatt]

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'm looking for a list of employees with the lowest salaries in each department. I can accomplish it in two ways: an Equi-Join or an Inner-Join. I've been informed they can both be utilized to get the desired outcome. I used the following queries:

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 command returns the appropriate table, with the columns 'First Name,' 'Dept Name,' and 'Min Salary' having all essential data.

The Inner-Join statement, however, does not execute because the First Name column must be included in the aggregate function or GROUP BY clause. This perplexes me since I have no idea how to solve it. After reading this article, I gained some knowledge but did not fully get it. How can I make the Inner-Join query produce the same results as the Equi-Join query?

[slangley]

Hi @sachinbhatt.

Here's a page on inner joins that may be helpful.  

Regards.

--Sharon

[dgg · Accepted solution]

There are a couple of issues with the second query:

1. Your subquery selects fields that are not included in the group by aggregate (as you already observed).
2. You're joining b to alt on Dept_ID without selecting Dept_ID in that subquery.

If you're trying to get the lowest paid employee with department, see if this works:

select
	d.DEPT_NAME,
	e.FIRST_NAME,
	e.SIR_NAME,
	e.SALARY
from
	EMPLOYEES e
inner join
	DEPARTMENTS d
on
	e.DEPT_ID = d.DEPT_ID
group by
	e.DEPT_ID
having
	e.SALARY = min(e.SALARY);

You didn't state what database you're using but I've done mockup using MySQL on SQL Fiddle - see here:

http://www.sqlfiddle.com/#!9/7a745c1/10

Hope that helps,

dgg