SQL Joins

SQL Joins  Microsoft SQL Server 2008 Management and Administration

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements

1) SQL Inner Join  PostgreSQL: A Better Way to Learn PostgreSQL (Prima Development)


In SQL Inner Join  the query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate

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

2. SQL Equi-join     Build Your Own Database Driven Website Using PHP and MySQL
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate
SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

SQL provides an optional shorthand notation for expressing equi-joins, by way of the USING construct
 SELECT *
FROM employee
INNER JOIN department
USING (DepartmentID


3) SQL Natural join   Beginning Database Design: From Novice to Professional
 A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name 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

4) SQL Cross join      Learn SQL

A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement. In other words, a cross join combines every row in B with every row in A. The number of rows in the result set will be the number of rows in A times the number of rows in B.

SELECT *
FROM employee CROSS JOIN department


5) SQL Left Outer Join     Sams Teach Yourself SQL in 24 Hours (4th Edition)

The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), 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

6) SQL Right Outer Join  Learning MySQL

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. 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

7. SQL Full outer join    SQL: A Beginner's Guide

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

SELECT *FROM employee

FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

8. Self-join          MySQL: Your visual blueprint for creating open source databases

A self-join is joining a table to itself

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;