Welcome to Shivshanker's Blog

SQL JOINS

• SQL JOINS
  A join combines records from two tables in a relational database and results in a new (temporary) table, also called joined table. In the Structured Query Language (SQL), there are two types of joins: inner and outer.
  As a special case, a table (base table, view, or joined table) can be joined with itself again. This is called self-join.
  Mathematically, join is relation composition, the fundamental operation in relational algebra, and generalizing function composition.
  We will use these two tables to show the join examples
  Table: Department
  Table: Employee
  INNER JOIN
  An inner join essentially finds the intersection between the two tables. This is the most common type of join used, and is considered the default join type. Inner joins return all rows from multiple tables where the join condition is met.
  The join example below takes all the records from table A (in this case, employee) and finds the matching record(s) in table B (department) based on the join predicate. If no match is found, the record from A is not included in the joined table. If multiple results are found in B that match the predicate (condition given), then one row will be returned for each (the values from A will be repeated).SELECT *
  FROM Employee INNER JOIN Department
  ON Employee.DepartmentID = Department.DepartmentID
  Result:
  Notice that employee Jasper and department Marketing do not appear. Neither of these records have accompanying rows in their associative tables, and are thus omitted from the inner join result.
  Example of an implicit inner join:
  SELECT *
  FROM Employee, Department
  WHERE Employee.DepartmentID = Department.DepartmentID
  CROSS JOIN
  While not used very commonly, a cross join is the foundation upon which inner joins are built. A cross join returns the cartesian product of the sets of rows from the joined tables. Thus, it is an inner join where the join condition always evaluates to True.
  If A and B are two sets then cross join = A X B.
  The SQL code for a cross join lists the tables to be joined (FROM), but does not include any filtering join predicate (WHERE).
  Example of an explicit cross join:
  SELECT *
  FROM Employee CROSS JOIN department
  Example of an implicit corss join:
  SELECT *
  FROM employee, department
  Resule:
  As you can see the cross join does not apply any predicate when matching records for the joined table. These joins are almost never used, except to generate all possible combinations of records from tables that do not share a common element. Still, the results of a cross joins can be further filtered.
  OUTER JOINS
  Outer joins are subdivided further into left outer joins, right outer joins, and full outer joins.
  LEFT OUTER JOIN
  A left outer join is very different from an inner join. Instead of limiting results to those in both tables, it limits results to those in the "left" table (A). This means that if the ON clause matches 0 records in B, a row in the result will still be returned but with NULL in each column from B.
  This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).
  A left outer join returns all the values from left table + matched values from right table (or NULL in case of no matching value).
  For example, this allows us to find the employee's departments, but still show the employee even when their department is NULL or does not exist (contrary to the inner join example above, where employees in non-existent departments were ignored).
  Example of a left outer join:
  SELECT *
  FROM Employee LEFT OUTER JOIN Department
  ON Employee.DepartmentID = Department.DepartmentID
  Result:
  Notice that for Jasper DepartmentName and DepartmentId are NULL.
  RIGHT OUTER JOIN
  A right outer join is much like a left outer join, except that the tables are reversed. Every record from the right side, B, will be returned, and NULL will be returned for columns from A for those rows that have no matching record in A.
  A right outer join returns all the values from right table + matched values from left table (or NULL in case of no matching value).
  
  Example right outer join:
  SELECT *
  FROM Employee RIGHT OUTER JOIN Department
  ON Employee.DepartmentID = Department.DepartmentID
  Result:
  Notice that LastName and DepartmentID are NULL for Marketing Department
  FULL OUTER JOIN
  A full outer join combines the results of both left and right outer joins. These joins will show records from both tables, and fill in NULLs for missing matches on either side.
  Some database systems do not support this functionality, but it can be emulated through the use of left and right outer joins and unions (see below).
  Example full outer join:
  SELECT *
  FROM Employee FULL OUTER JOIN Department
  ON Employee.DepartmentID = Department.DepartmentID
  Result:
  
  Notice the NULL Values for Jasper LastName and Marketing DepartmentName.