Joins in DBMS

Explain all the Joins ?

An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each table.

Suppose we have two table in our database as follows:

LastName	DepartmentId
Raffery	31
Jones	33
Heisenberg	33
Robinson	34
Smith	34
John	NULL

                   (Emp)

DepartmentId	DepartmentName
31	Sales
33	Engineering
34	Clerical
35	Marketing

                   (Depart )

Cross Join

CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table

Example of an explicit cross join:

SELECT *

FROM Emp CROSS JOIN Depart;

Example of an implicit cross join:

SELECT *

FROM Emp, Depart;

Inner Join

Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

SELECT *

FROM Emp INNER JOIN Depart

ON Emp.DepartmentID = Depart.DepartmentID;

The following example is equivalent to the previous one, but this time using implicit join notation:

SELECT *

FROM Emp, Depart

WHERE Emp.DepartmentID = Depart.DepartmentID;

Emp.LastName	Emp.DepartmentId	Depart.DepartmentName	Depart.DepartmentId
Robinson	34	Clerical	34
Jones	33	Engineering	33
Smith	34	Clerical	34
Heisenberg	33	Engineering	33
Rafferty	31	Sales	31

Note: Programmers should take special care when joining tables on columns that can contain NULL values, since NULL will never match any other value (not even NULL itself), unless the join condition explicitly uses the IS NULL or IS NOT NULL predicates.

Equi-join
An equi-join is a specific type of comparator-based join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join. The query shown above has already provided an example of an equi-join:

SELECT *
FROM Emp JOIN Depart
ON Emp.DepartmentID = Depart.DepartmentID;

We can write equi-join as below,

SELECT *
FROM Emp, Depart
WHERE Emp.DepartmentID = Depart.DepartmentID;

SELECT * FROM Emp INNER JOIN Depart USING (DepartmentID);

Natural join
A natural join is a type of equi-join where the join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally named columns

SELECT *
FROM Emp NATURAL JOIN Depart;

DepartmentId	Emp.LastName	Department.DepartmentName
34	Smith	Clerical
33	Jones	Engineering
34	Robinson	Clerical
33	Heisenberg	Engineering
31	Rafferty	Sales

Outer join
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained (left, right, or both).

No implicit join-notation for outer joins exists in standard SQL.

Left outer join
A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table.

SELECT *
FROM Emp LEFT OUTER JOIN Department
ON Emp.DepartmentID = Depart.DepartmentID;

Emp.LastName	Emp.DepartmentId	Depart.DepartmentName	Depart.DepartmentId
Jones	33	Engineering	33
Rafferty	31	Sales	31
Robinson	34	Clerical	34
Smith	34	Clerical	34
John	NULL	NULL	NULL
Heisenberg	33	Engineering	33

Right outer join

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed.A right outer join returns all the values from the right table and matched values from the left table (NULL in the case of no matching join predicate).

SELECT *

FROM Emp RIGHT OUTER JOIN Depart

ON Emp.DepartmentID = Depart.DepartmentID;

Emp.LastName	Emp.DepartmentId	Depart.DepartmentName	Depart.DepartmentId
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Heisenberg	33	Engineering	33
Rafferty	31	Sales	31
NULL	NULL	Marketing	35

Full outer join
Conceptually, a full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).

Emp.LastName	Emp.DepartmentId	Depart.DepartmentName	Depart.DepartmentId
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
John	NULL	NULL	NULL
Heisenberg	33	Engineering	33
Rafferty	31	Sales	31
NULL	NULL	Marketing	35

This covers the concept of joins in DBMS , please comment and share below if you have any question or you want to add anything.