cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X

Difference between Equi-Join and Inner-Join in SQL

sachinbhatt
7-Bedrock

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'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?

1 ACCEPTED SOLUTION

Accepted Solutions
dgg
11-Garnet
11-Garnet
(To:sachinbhatt)

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

 

View solution in original post

2 REPLIES 2
slangley
23-Emerald II
(To:sachinbhatt)

Hi @sachinbhatt.

 

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

 

Regards.

 

--Sharon

dgg
11-Garnet
11-Garnet
(To:sachinbhatt)

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

 

Top Tags