Thursday, March 17, 2011

Examples of all kinds of JOINs in SQL

There is a description in wikipedia, if you have problems with understanding the results.


CREATE DATABASE test1;
USE test1;

CREATE TABLE `employee` (
`LastName` varchar(25),
`DepartmentID` int(4),
UNIQUE KEY `LastName` (`LastName`)
);

CREATE TABLE `department` (
`DepartmentID` int(4),
`DepartmentName` varchar(25),
UNIQUE KEY `DepartmentID` (`DepartmentID`)
);


INSERT INTO employee(LastName, DepartmentID) VALUES
("Rafferty", 31),
("Jones", 33),
("Steinberg", 33),
("Robinson", 34),
("Smith", 34),
("John", NULL);


INSERT INTO department( DepartmentID, DepartmentName ) VALUES
(31, "Sales"),
(33, "Engineering"),
(34, "Clerical"),
(35, "Marketing")
;


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


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


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

SELECT *
FROM employee NATURAL JOIN department;

SELECT *
FROM employee CROSS JOIN department;

SELECT *
FROM employee, department;

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


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

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

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

No comments:

Post a Comment