What is a Join in SQL? What are different types of Joins in SQL? Outer Join, Inner Join, Equi Join, Natural Join, Cross Join, Full Outer Join

Join - A join in SQL is a clause that allows merging of records from one or more than one tables in a database. The records from the tables are fetched based on some values that are common to each. See code example below:

Say we have 2 tables, T_EMPLOYEES and T_SALARY

Select * from T_EMPLOYEES JOIN T_SALARY
ON
T_EMPLOYEES.EMP_ID = T_SALARY.EMP_ID;

Consider the following 2 tables:

Inner Join - in this type of join, every record in the tables being joined have a matching record. The condition based on which the records are matched is called the join predicate.

Implicit Vs. Explicit Inner Joins - When the clause 'Inner Join' is used, it is said to be an explicit join.
See code example below:

SELECT * FROM t_shooters
INNER JOIN t_guntypes
ON t_shooters.gun_type = t_guntypes.gun_type;

Above is an explicit inner join. The same can be achieved without using the 'inner join' clause like this:

SELECT * FROM t_shooter, t_guntype
WHERE t_shooters.gun_type = t_guntypes.gun_type;

Above is an implicit inner join.

Equi Join - This is a type of Inner Join. (It is also called a Theta Join). - It is a join where the equality '=' operator is used. In case an operator like '<' or '>' or any other operator is used, it is not an Equi Join.

An Equi Join may be used by using equality operator or the Using clause. See code example below:

SELECT T_SHOOTERS.NAME, GUN_TYPE, T_GUNTYPES.DESCRIPTION
FROM T_SHOOTERS INNER JOIN T_GUNTYPES
USING(GUN_TYPE);

Natural Join - It is a type of Inner Join. It is a join where in the join predicate is based on all the column names that are common to both the tables being joined. See code example below:

SELECT * From T_SHOOTERS NATURAL JOIN T_GUNTYPES;

Results will be as below:

Cross Join - Also called Cartesian Join. It is the result of joining each row of a table with each row of the other table.

Outer Join - In an Outer join, each record of a table does not really need to match with a record in the corresponding table. Outer joins maybe Left Outer Joins or Right Outher Joins. Outer Joins are always explicit.

Left Outer Join - This join contains all records from the left table, and matching records in the other table. However, if there are no matching records in the other table, it will still return a result, where in the records of the other table will be NULL.

Right Outer Join - This join fetches all records from the right table and only matching records from the left table. By saying left table, it means the table who's name is to the left of the Join Clause.

See code example below:

SELECT * FROM t_shooters LEFT OUTER JOIN t_guntypes
ON t_shooters.gun_type = t_guntypes.gun_type;

Full Outer Join - A full outer join merges the result fetched from Left and Right Outer joins. See code example below:

SELECT * FROM t_shooters
FULL OUTER JOIN t_guntypes
ON t_shooters.gun_type = t_guntypes.gun_type;