SQL: Join

A join is a means for combining fields from two tables by Using values common to each. ANSI standard SQL specifies four types of join: inner, outer, left and right. as a special case, a table can join to itself in a self-join.

The following is the SQL to create two tables:

CREATE TABLE department( DepartmentID INT, DepartmentName VARCHAR(20)); CREATE TABLE employee( LastName VARCHAR(20), DepartmentID INT); INSERT INTO department(DepartmentID, DepartmentName) VALUES(31, 'Sales');INSERT INTO department(DepartmentID, DepartmentName) VALUES(33, 'Engineering');INSERT INTO department(DepartmentID, DepartmentName) VALUES(34, 'Clerical');INSERT INTO department(DepartmentID, DepartmentName) VALUES(35, 'Marketing'); INSERT INTO employee(LastName, DepartmentID) VALUES('Rafferty', 31);INSERT INTO employee(LastName, DepartmentID) VALUES('Jones', 33);INSERT INTO employee(LastName, DepartmentID) VALUES('Steinberg', 33);INSERT INTO employee(LastName, DepartmentID) VALUES('Robinson', 34);INSERT INTO employee(LastName, DepartmentID) VALUES('Smith', 34);INSERT INTO employee(LastName, DepartmentID) VALUES('John', NULL);

 

Employee table

Lastname	Departmentid
Rafferty	31
Jones	33
Steberger	33
Robinson	34
Smith	34
John	Null

Department table

Departmentid	Departmentname
31	Sales
33	Engineering
34	Clerical
35	Marketing

 

 

 

 

 

 

 

• Cross join

Cross join returns the Cartesian (flute product) 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 roow from the second table.

The cross join does not apply any predicate to filter records from the joined table. programmers can further filter the results of a cross join by using where caluse.

SELECT *FROM employee CROSS JOIN department;

Employee. lastname	Employee. Dimension mentid	Department. departmentname	Department. initialize mentid
Rafferty	31	Sales	31
Jones	33	Sales	31
Steberger	33	Sales	31
Smith	34	Sales	31
Robinson	34	Sales	31
John	Null	Sales	31
Rafferty	31	Engineering	33
Jones	33	Engineering	33
Steberger	33	Engineering	33
Smith	34	Engineering	33
Robinson	34	Engineering	33
John	Null	Engineering	33
Rafferty	31	Clerical	34
Jones	33	Clerical	34
Steberger	33	Clerical	34
Smith	34	Clerical	34
Robinson	34	Clerical	34
John	Null	Clerical	34
Rafferty	31	Marketing	35
Jones	33	Marketing	35
Steberger	33	Marketing	35
Smith	34	Marketing	35
Robinson	34	Marketing	35
John	Null	Marketing	35

 

• Inner join

An inner join is most common Join Operation used in applications and can be regared ad the default join-type.

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 all pairs of rows which satisfy the join-predicate.

1. Explicit join Notation:

SELECT *FROM employee INNER JOIN departmentON employee.DepartmentID = department.DepartmentID;

2. Implicit join Notation:

SELECT *FROM employee, departmentWHERE employee.DepartmentID=department.DepartmentID;

Employee. lastname	Employee. Dimension mentid	Department. departmentname	Department. initialize mentid
Robinson	34	Clerical	34
Jones	33	Engineering	33
Smith	34	Clerical	34
Steberger	33	Engineering	33
Rafferty	31	Sales	31

Note: programmers shocould take special care when joining tables on columns that 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:

SELECT *FROM employee JOIN department  ON employee.DepartmentID = department.DepartmentID;

SELECT *FROM employee, departmentWHERE employee.DepartmentID = department.DepartmentID;

SELECT *FROM employee INNER JOIN department USING (DepartmentID);

 

Natural join:

SELECT *FROM employee NATURAL JOIN department;

As with the explicitUSINGClause, only one hour mentid column occurs in the joined table, with no qualifier:

Departmentid	Employee. lastname	Department. departmentname
34	Smith	Clerical
33	Jones	Engineering
34	Robinson	Clerical
33	Steberger	Engineering
31	Rafferty	Sales

 

• Outer Join:

AnOuter JoinDoes 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 ).

1. Left outer joins:

The result ofLeft Outer Join(Or simplyLeft join) For table A and B always contains all records of the "Left" table (), even if the join-condition does not find any matching record in the "right" table (B ).

SELECT *FROM employee LEFT OUTER JOIN department  ON employee.DepartmentID = department.DepartmentID;

Employee. lastname	Employee. Dimension mentid	Department. departmentname	Department. initialize mentid
Jones	33	Engineering	33
Rafferty	31	Sales	31
Robinson	34	Clerical	34
Smith	34	Clerical	34
John	Null	Null	Null
Steberger	33	Engineering	33

2. Right outer join:

Every row from the "right" table (B) will appear in the joined table at least once. if no matching row from the "Left" table (a) exists, null will appear in columns from a for those records that have no match in B.

SELECT *FROM employee RIGHT OUTER JOIN department  ON employee.DepartmentID = department.DepartmentID;

Employee. lastname	Employee. Dimension mentid	Department. departmentname	Department. initialize mentid
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Steberger	33	Engineering	33
Rafferty	31	Sales	31
Null	Null	Marketing	35

3. Full outer join:

Conceptually,Full outer joinCombines 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 ).

SELECT *FROM employee FULL OUTER JOIN department  ON employee.DepartmentID = department.DepartmentID;

 

Employee. lastname	Employee. Dimension mentid	Department. departmentname	Department. initialize mentid
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
John	Null	Null	Null
Steberger	33	Engineering	33
Rafferty	31	Sales	31
Null	Null	Marketing	35