lJOINS:
In the following tables the DepartmentID column of the Department table (which can be designated as Department.DepartmentID) is the primary key, while Employee.DepartmentID is a foreign key.
Employee Table
LastName
DepartmentID
Rafferty
31
Jones
33
Steinberg
33
Robinson
34
Smith
34
John
NULL
Department Table
DepartmentID
DepartmentName
31
Sales
33
Engineering
34
Clerical
35
Marketing
Note: The "Marketing" Department currently has no listed employees. Also, employee "John" has not been assigned to any Department yet.
How to create the tables
CREATE TABLE employee (
LastName varchar(25),
DepartmentID int
);
CREATE TABLE department (
DepartmentID int UNIQUE,
DepartmentName varchar(25)
);
ALTER TABLE employee
ADD CONSTRAINT fk_employee_dept
FOREIGN KEY (DepartmentID)
REFERENCES department(DepartmentID);
FOR INSERT INTO DATA
INSERT INTO department VALUES (31,'Sales');
INSERT INTO department VALUES (33,'Engineering');
INSERT INTO department VALUES (34,'Clerical');
INSERT INTO department VALUES (35,'Marketing');
INSERT INTO employee VALUES ('Rafferty',31);
INSERT INTO employee VALUES ('Jones',33);
INSERT INTO employee VALUES ('Steinberg',33);
INSERT INTO employee VALUES ('Robinson',34);
INSERT INTO employee VALUES ('Smith',34);
Inner join
SELECT *
FROM employee INNER JOIN department
ON employee.DepartmentID = department.DepartmentID;
The following example shows a query which is equivalent to the one from the previous example, but this time written using the implicit join notation:
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
Thus the result of the execution of either of the two queries above will be:
Employee.LastName
Employee.DepartmentID
Department.DepartmentName
Department.DepartmentID
Robinson
34
Clerical
34
Jones
33
Engineering
33
Smith
34
Clerical
34
Steinberg
33
Engineering
33
Rafferty
31
Sales
31
EQUI JOIN:
EXAMPLE:
SELECT *
FROM employee
EQUI JOIN department
ON employee.DepartmentID = department.DepartmentID;
DepartmentID
Employee.LastName
Department.DepartmentName
34
Smith
Clerical
33
Jones
Engineering
34
Robinson
Clerical
33
Steinberg
Engineering
31
Rafferty
Sales
Cross join
Example of an explicit cross join:
SELECT *
FROM employee CROSS JOIN department;
Example of an implicit cross join:
SELECT *
FROM employee, department;
Employee.LastName
Employee.DepartmentID
Department.DepartmentName
Department.DepartmentID
Rafferty
31
Sales
31
Jones
33
Sales
31
Steinberg
33
Sales
31
Smith
34
Sales
31
Robinson
34
Sales
31
John
NULL
Sales
31
Rafferty
31
Engineering
33
Jones
33
Engineering
33
Steinberg
33
Engineering
33
Smith
34
Engineering
33
Robinson
34
Engineering
33
John
NULL
Engineering
33
Rafferty
31
Clerical
34
Jones
33
Clerical
34
Steinberg
33
Clerical
34
Smith
34
Clerical
34
Robinson
34
Clerical
34
John
NULL
Clerical
34
Rafferty
31
Marketing
35
Jones
33
Marketing
35
Steinberg
33
Marketing
35
Smith
34
Marketing
35
Robinson
34
Marketing
35
John
NULL
Marketing
35
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 a WHERE clause.
Outer joins
Left outer join
Example of a left outer join
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
Steinberg
33
Engineering
33
Right outer joins
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
For example, this allows us to find each employee and his or her department, but still show departments that have no employees.
Example right outer join
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
Steinberg
33
Engineering
33
Rafferty
31
Sales
31
NULL
NULL
Marketing
35
Full outer join
For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn't have an employee.
Example full outer join:
SELECT *
FROM employee
FULL 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
John
NULL
NULL
NULL
Steinberg
33
Engineering
33
Rafferty
31
Sales
31
NULL
NULL
Marketing
35
Self-join
A self-join is joining a table to itself
Example
A query to find all pairings of two employees in the same country is desired. If you had two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, you could use a normal join operation to find the answer table. However, all the employee information is contained within a single large table.
Considering a modified Employee table such as the following:
Employee Table
EmployeeID
LastName
Country
DepartmentID
123
Rafferty
Australia
31
124
Jones
Australia
33
145
Steinberg
Australia
33
201
Robinson
United States
34
305
Smith
Germany
34
306
John
Germany
NULL
An example solution query could be as follows:
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
Which results in the following table being generated.
Employee Table after Self-join by Country
EmployeeID
LastName
EmployeeID
LastName
Country
123
Rafferty
124
Jones
Australia
123
Rafferty
145
Steinberg
Australia
124
Jones
145
Steinberg
Australia
305
Smith
306
John
Germany
For this example, note that:
F and S are aliases for the first and second copies of the employee table.
The condition F.Country = S.Country excludes pairings between employees in different countries. The example question only wanted pairs of employees in the same country.
The condition F.EmployeeID < S.EmployeeID excludes pairings where the EmployeeIDs are the same.
F.EmployeeID < S.EmployeeID also excludes duplicate pairings. Without it, the following less useful table would be generated (the table below displays only the "Germany" portion of the result):
EmployeeID
LastName
EmployeeID
LastName
Country
305
Smith
305
Smith
Germany
305
Smith
306
John
Germany
306
John
305
Smith
Germany
306
John
306
John
Germany
SELF JOIN:
You can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table. These joins allow you to retrieve related records from the same table. The most common case where you'd use a self-join is when you have a table that references itself, such as the employees table shown below:
id first_name last_name manager----------- --------------- --------------- -----------1 Pat Crystal NULL2 Dennis Miller 13 Jacob Smith 14 Allen Hunter 25 Mary Underwood 36 Joy Needham 3
In this table, the manager attribute simply references the employee ID of another employee in the same table. For example, Dennis Miller reports to Pat Crystal. Pat is apparently the president of this company, as she reports to no one. Suppose you're tasked with writing a SQL query to retrieve a list of employees and their managers. You can't write a basic SQL SELECT statement to retrieve this information, as you need to cross reference information contained in other records within the same table. Fortunately, you can use a self-join to solve this dilemma by joining the table to itself. Here's the SQL statement that will retrieve the desired results:
SELECT e.first_name AS 'Employee FN', e.last_name AS 'Employee LN', m.first_name AS 'Manager FN', m.last_name AS 'Manager LN'FROM employees AS e LEFT OUTER JOIN employees AS mON e.manager =m.id
And the corresponding output:
Employee FN Employee LN Manager FN Manager LN--------------- --------------- --------------- ---------------Pat Crystal NULL NULLDennis Miller Pat CrystalJacob Smith Pat CrystalAllen Hunter Dennis MillerMary Underwood Jacob SmithJoy Needham Jacob Smith(6 row(s) affected)
go through this it is very easy to learn
