Skip to main content
S
sachinbhatt8-Gravel
8-Gravel
December 20, 2022
Solved

Difference between Equi-Join and Inner-Join in SQL

  • December 20, 2022
  • 2 replies
  • 5393 views

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?

Best answer by dgg

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

 

2 replies

S
slangleyCommunity Manager
Community Manager
December 21, 2022

Hi @sachinbhatt.

 

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

 

Regards.

 

--Sharon

    D
    dgg1-VisitorAnswer
    1-Visitor
    December 27, 2022

    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

     

      Terms & ConditionsCookie settingsAccessibility statement