Inner Join

Q. What is Inner Join? What are the different types of Inner Join?

Inner join is the default JOIN type. Sometimes it is called Simple Join.

For example -

An inner join will perform when we write like -

SELECT e.employee_id, e.last_name, d.department_name
FROM   employees e, departments d;

Look, here we even didn't write the "JOIN" word.

Here, the inner Join query compares each row of table employees with each row of table departments.

"JOIN" / "INNER JOIN" - is the same thing.

There are Two types Inner join -

•  Equijoins

•  Nonequijoins

EquiJoin

An equijoin is an inner join whose join condition contains an equality operator.

The following example is an equijoin because the join condition contains equality operator (=).

SELECT e.employee_id, e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id;

Here, the join condition is e.department_id=d.department_id. If a row in the employees table has a department ID that matches the value in a row in the departments table, then the database returns the joined result; otherwise, the database does not return a result.

Non-equiJoin

A nonequijoin is an inner join whose join condition contains an operator that is not an equality operator. The following query lists all employees whose hire date occurred when employee 176 (who is listed in job_history because he changed jobs in 2007) was working at the company:

SELECT e.employee_id, e.first_name, e.last_name, e.hire_date
FROM   employees e, job_history h
WHERE  h.employee_id = 176
AND    e.hire_date BETWEEN h.start_date AND h.end_date;

In the preceding example, the condition joining employees and job_history does not contain an equality operator, so it is a nonequijoin. Nonequijoins are relatively rare.

A CARTESIAN PRODUCT will result if there are no matching join columns specify, that mean, no Join Condition exists. In this scenario, a row will be returned for every combination of the two tables. If one table has 1000 rows and the second table has 3000 rows, the result set will return 3,000,000 (3 million) rows!

SELECT * FROM employees, departments;

 
Q: What is the difference between the "JOIN" and "INNER JOIN"?

No difference actually!

Suppose we have a table A and a table B. 
So. here a list of equivalent Join syntax:

A JOIN B           =   A INNER JOIN B       

A LEFT JOIN B    =   A LEFT OUTER JOIN B  
A RIGHT JOIN B  =   A RIGHT OUTER JOIN B
A FULL JOIN B    =   A FULL OUTER JOIN B