INNER JOIN (EQUI-JOIN) Predicate

1. INNER JOIN (EQUI-JOIN) Predicate source tables SELECT * FROM employee, department WHERE employee.DepartmentID = department.DepartmentID is equivalent to SELECT * FROM employee INNERJOIN department ON employee.DepartmentID = department.DepartmentID or the same using another syntactic sugar SELECT Employee.lastName, DepartmentID, Department.DepartmentName FROM Employee INNERJOIN Department USING(DepartmentID); the result of employee department taken fromhttp://en.wikipedia.org/wiki/Join_(SQL)

2. NATURAL JOIN Predicate source tables SELECT * FROM employee NATURAL JOIN department the result taken fromhttp://en.wikipedia.org/wiki/Join_(SQL)

3. CROSS JOIN (CARTESIAN PRODUCT) Predicate source tables SELECT * FROM employee CROSS JOIN department is the same as implicit expression SELECT * FROM employee, department; the result of employee department … … … … taken fromhttp://en.wikipedia.org/wiki/Join_(SQL)

4. LEFT OUTER JOIN Predicate source tables SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID the result taken fromhttp://en.wikipedia.org/wiki/Join_(SQL)

5. RIGHT OUTER JOIN Predicate source tables SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID the result taken fromhttp://en.wikipedia.org/wiki/Join_(SQL)

6. FULL OUTER JOIN Predicate source tables SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID the result taken fromhttp://en.wikipedia.org/wiki/Join_(SQL)

7. THETA-JOIN (Θ-JOIN) Predicate Θ  {< , > , = ,  ,  ,  } an example …. There is possible to define yet more abstract joins such as SEMI-JOIN, ANTI-JOIN etc. taken fromhttp://en.wikipedia.org/wiki/Join_(relational_algebra)#Joins_and_join-like_operators

8. RDM example – Doctor’s Office conceptual model of the problem possible relational implementation

9. Query example What are surnames and addresses of doctors having diagnosis alergy?