Difference between Equi-Join and Inner-Join in SQL
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;
Inner Join, Natural Joins and Equi Join
Inner join of A and B combines columns of a row from A and a row from B based on a join predicate. For example, a "sempai" join: SELECT ... FROM people A INNER JOIN people B ON A.age > B.age
will pair each person with each person that is their junior; the juniormost people will not be selected from A, and seniormost people will not be selected from B, because there are no matching rows.
Equi join is a particular join where the join relation is equality. A "sempai" join from the last paragraph is not an equi join; but "same age" join would be. Though typically it would be used for foreign relationships (equi joins on primary keys), such as SELECT ... FROM person A INNER JOIN bicycle B ON A.bicycle_id = B.id
. (Pay no attention to the fact that this is not a proper model, people sometimes have multiple bicycles... a bit of a silly example, I'm sure I could have found a better one.)
A natural join is a special kind of equi join that assumes equality of all shared columns (without explicitly stating the predicate). So for example SELECT ... FROM people A INNER JOIN bicycles B ON A.bicycle_id = B.bicycle_id
is equivalent to SELECT ... FROM people A NATURAL JOIN bicycles B
, assuming bicycle_id
is the only column present in both tables. Most people I know will not use this, because of several reasons - it is a more common practice to have the primary key not repeat the table name, i.e. bicycles.id
than bicycles.bicycles_id
; it is possible the foreign key does not reflect the table name (e.g. person.overseer_id
rather than person.person_id
, for obvious reasons), and (forgotten my me but thankfully remembered by Sudipta Mondal) there might be unrelated columns that are named the same but make zero sense to join on, like creation_time
. For these reasons, I have never used NATURAL JOIN
in my life.
Equi/natural joins do not necessarily have to be inner.
what is inner equijoin?
From Join (SQL)
An equi-join, also known as an
equijoin, is a specific type of
comparator-based join, or theta join,
that uses only equality comparisons in
the join-predicate. Using other
comparison operators (such as <)
disqualifies a join as an equi-join.
These are joins where only equality operators are used.
Eg.
SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID
Is it better to do an equi join in the from clause or where clause
It's a style matter. Generally, you'd want to put the conditions that define the "shape" of the result set in the FROM clause (i.e. those that control which rows from each table should join together to produce a result), whereas those conditions which filter the result set should be in the WHERE clause. For INNER JOINs, the effects are equal, but once OUTER JOINs (LEFT, RIGHT) are involved, it feels a lot clearer.
In your first example, I'm left asking "what has this got to do with Table B?" when I encounter this odd condition in the JOIN. Whereas in the second, I can skip over the FROM clause (and all JOINs) if I'm not interested, and just see the conditions which determine whether rows are going to be returned in the WHERE clause.
SQL JOIN and different types of JOINs
What is SQL JOIN
?
SQL JOIN
is a method to retrieve data from two or more database tables.
What are the different SQL JOIN
s ?
There are a total of five JOIN
s. They are :
1. JOIN or INNER JOIN
2. OUTER JOIN
2.1 LEFT OUTER JOIN or LEFT JOIN
2.2 RIGHT OUTER JOIN or RIGHT JOIN
2.3 FULL OUTER JOIN or FULL JOIN
3. NATURAL JOIN
4. CROSS JOIN
5. SELF JOIN
1. JOIN or INNER JOIN :
In this kind of a JOIN
, we get all records that match the condition in both tables, and records in both tables that do not match are not reported.
In other words, INNER JOIN
is based on the single fact that: ONLY the matching entries in BOTH the tables SHOULD be listed.
Note that a JOIN
without any other JOIN
keywords (like INNER
, OUTER
, LEFT
, etc) is an INNER JOIN
. In other words, JOIN
is
a Syntactic sugar for INNER JOIN
(see: Difference between JOIN and INNER JOIN).
2. OUTER JOIN :
OUTER JOIN
retrieves
Either,
the matched rows from one table and all rows in the other table
Or,
all rows in all tables (it doesn't matter whether or not there is a match).
There are three kinds of Outer Join :
2.1 LEFT OUTER JOIN or LEFT JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the
right table. If there are no columns matching in the right table, it returns NULL
values.
2.2 RIGHT OUTER JOIN or RIGHT JOIN
This JOIN
returns all the rows from the right table in conjunction with the matching rows from the
left table. If there are no columns matching in the left table, it returns NULL
values.
2.3 FULL OUTER JOIN or FULL JOIN
This JOIN
combines LEFT OUTER JOIN
and RIGHT OUTER JOIN
. It returns rows from either table when the conditions are met and returns NULL
value when there is no match.
In other words, OUTER JOIN
is based on the fact that: ONLY the matching entries in ONE OF the tables (RIGHT or LEFT) or BOTH of the tables(FULL) SHOULD be listed.
Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.
3. NATURAL JOIN :
It is based on the two conditions :
- the
JOIN
is made on all the columns with the same name for equality. - Removes duplicate columns from the result.
This seems to be more of theoretical in nature and as a result (probably) most DBMS
don't even bother supporting this.
4. CROSS JOIN :
It is the Cartesian product of the two tables involved. The result of a CROSS JOIN
will not make sense
in most of the situations. Moreover, we won't need this at all (or needs the least, to be precise).
5. SELF JOIN :
It is not a different form of JOIN
, rather it is a JOIN
(INNER
, OUTER
, etc) of a table to itself.
JOINs based on Operators
Depending on the operator used for a JOIN
clause, there can be two types of JOIN
s. They are
- Equi JOIN
- Theta JOIN
1. Equi JOIN :
For whatever JOIN
type (INNER
, OUTER
, etc), if we use ONLY the equality operator (=), then we say that
the JOIN
is an EQUI JOIN
.
2. Theta JOIN :
This is same as EQUI JOIN
but it allows all other operators like >, <, >= etc.
Many consider both
EQUI JOIN
and ThetaJOIN
similar toINNER
,OUTER
etcJOIN
s. But I strongly believe that its a mistake and makes the
ideas vague. BecauseINNER JOIN
,OUTER JOIN
etc are all connected with
the tables and their data whereasEQUI JOIN
andTHETA JOIN
are only
connected with the operators we use in the former.Again, there are many who consider
NATURAL JOIN
as some sort of
"peculiar"EQUI JOIN
. In fact, it is true, because of the first
condition I mentioned forNATURAL JOIN
. However, we don't have to
restrict that simply toNATURAL JOIN
s alone.INNER JOIN
s,OUTER JOIN
s
etc could be anEQUI JOIN
too.
Related Topics
How to Create a Table Alias in MySQL
Safest Way to Get Last Record Id from a Table
SQL Query with Distinct and Sum
Adding 'Go' Statements to Entity Framework Migrations
Linq to SQL: How to Stop the Auto Generated Object Name from Being Renamed
Select Closest Numerical Value with MySQL Query
Role of Selectivity in Index Scan/Seek
Google Big Query SQL - Get Most Recent Column Value
Update Multiple Columns in a Trigger Function in Plpgsql
Get All Dates in Date Range in SQL Server
Do Conditional Insert with SQL
Does SQLite Support Any Kind of If(Condition) Statement in a Select
No Unique or Exclusion Constraint Matching the on Conflict
SQL - How to Get Only the Numbers After the Decimal