SQL Joins

gistfile1.rb

#INNER JOIN
#An 'inner join' is the most common join operation used in applications 
#and can be regarded as 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 find all pairs of rows which satisfy the join-predicate. 
SELECT * FROM employee 
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID;

#LEFT JOIN
#An outer join does 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).
#(In this case left and right refer to the two sides of the JOIN keyword.)
SELECT *
FROM employee 
LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;

#RIGHT JOIN
#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.
#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.
SELECT *
FROM employee 
RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;