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
Employee table
| LastName | DepartmentID |
|---|---|
| Rafferty | 31 |
| Jones | 33 |
| Heisenberg | 33 |
| Robinson | 34 |
| Smith | 34 |
| John |
NULL
|
| DepartmentID | DepartmentName |
|---|---|
| 31 | Sales |
| 33 | Engineering |
| 34 | Clerical |
| 35 | Marketing |
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.[1]
Example
of an explicit cross join:
SELECT *
FROM employee
CROSS JOIN department;
Example
of an implicit cross join:
SELECT *
FROM employee,
department;
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.
FROM employee INNER JOIN department
ON employee.DepartmentID = department.DepartmentID;
The following
example is equivalent to the previous one, but this time using
implicit join notation:
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.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 employee
JOIN department
ON
employee.DepartmentID = department.DepartmentID;
We
can write equi-join as below,
SELECT *
FROM employee,
department
WHERE
employee.DepartmentID = department.DepartmentID;
SELECT *
FROM employee
INNER JOIN department 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 employee NATURAL JOIN department;
| DepartmentID | Employee.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 employee
LEFT OUTER JOIN department
ON
employee.DepartmentID = department.DepartmentID;
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.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 employee
RIGHT OUTER JOIN department
ON
employee.DepartmentID = department.DepartmentID;
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.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).
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.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 |
No comments:
Post a Comment