-->

Joins in Database

Posted by Admin on
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.LastNameEmp.DepartmentIdDepart.DepartmentNameDepart.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





Please Comment if you like the above post or if you find anything wrong .

No comments:

Post a Comment