Different types of JOINS in SQL and MYSQL

A SQL JOIN clause is used to retrieve data from two or more tables in database.

A JOIN works on one, two or more tables if they have at least one common field and have a relationship between them.

1. INNER JOIN (JOIN)
2. OUTER JOIN
   1. LEFT OUTER JOIN or LEFT JOIN
   2. RIGHT OUTER JOIN or RIGHT JOIN
   3. FULL OUTER JOIN or FULL JOIN
3. CROSS JOIN
4. SELF JOIN
5. NATURAL JOIN

In this kind of a JOIN, we get all records that match the condition in both the tables

In other words - Inner join: Select records that have matching values in both tables.

Remarks:

The Inner Join refers to the intersection of two (or more) tables.

Join and Inner Join are functionally equivalent, but INNER JOIN can be a bit clear to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.

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 :
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.

In others words - LEFT JOIN: Select records from the first (left-most) table with matching records of right table.

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.

In others words - RIGHT JOIN: Select records from the second (right-most) table with matching records of left table.

This join combines left join and right join. It returns rows from either table when the conditions are met and returns NULL value when there is no match.

In other words- FULL (OUTER) JOIN: Selects all records that match either left or right table records.

It is the Cartesian product of the two tables involved.

Its result is the number of records in the first table multiplied by the number of records in the second table, showing the columns altogether.

SELECT * FROM department, employee

or

SELECT * FROM department CROSS JOIN employee;

A self join is simply when you join a table with itself.

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.

1. Equi JOIN
2. Theta JOIN | NonEqui Join

As the name itself indicates, it combines common records from two tables based on equality condition.

It is reverse of Equi-join where joining condition is uses other than equal operator(=) e.g, !=, <=, >=, >, < or BETWEEN etc