Advance Database Part 1 - Joins Revisited.

There is a lot more to SQL & Oracle Queries¹ than just Select From Where clause(s) which we generally use to write most queries. In this series of post I would be revisiting some of the advance SQL & Database concepts.

Joins - join's are pretty simple & relatively well know concept. Its something most of us use day in and day out. However there different type of joins with stubble difference among them - This post should serve as a quick view of the different types of joins we could encounter.

Employee Table

LastName	DepartmentID
Rafferty	31
Jones	33
Steinberg	33
John	NULL

Department Table

DepartmentID	DepartmentName
31	Sales
33	Engineering
34	Clerical
35	Marketing

1. Join or Inner Join - The usual joins which we deal with everyday are Inner Joins or simply called Joins. There are three kinds of inner joins

• Equi-join - This are the most common type of inner join used. Two tables are combined based on an equality condition, usually a foreign key in the first table is equated with a primary key in the second table.
  
  Usual way to write the query,
  

  SELECT *
  FROM   employee, department 
  WHERE  employee.DepartmentID = department.DepartmentID;
  

  
  SQL Syntax
  

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

  
  Alternate SQL Syntax
  

  SELECT *
  FROM   employee JOIN department 
         USING (DepartmentID);
  

  
  Are all the 3 syntax equivalent? No really, there are difference in the Column that show up² in the end result. However all three produce the same rows.
  
  Results
  

  Employee-Department Table

  LastName	Employee.DepartmentID	Department .DepartmentID	DepartmentName
  Rafferty	31	31	Sales
  Jones	33	33	Engineering
  Steinberg	33	33	Engineering

  
  
  1. The employee John who has not been allocated a department is not included in the result set.
  2. The departments Clerical and Marketing which have no employees are not included in the result set.
  
  
  Hot Tip
  Equi-Joins require a matching row to be present on both sides of the table for the join to happen.
  
  

• Natural Join - A join on the two tables automatically using  the column names that match between the two tables. This isn't usually used, nor are they recommended. The danger comes from inadvertently adding a new column with a name that matches the other table. This means that any existing natural join will start comparing rows with different criteria than before.
  
  SQL Syntax
  

  SELECT *
  FROM   employee NATURAL JOIN department; 

  
  Results
  Same as the Equi-Join since in this example both the table have only 1 common column name - DepartmentID. The result set would contain only one DepartmentId column without any prefix table name.

---

2. Outer Join - Unlike an inner join, outer join does not require each row in the table to match with an row in the other table.
   
   Hot Tip
   Outer joins are classified depending on the table who's rows are included in the result even when there are no matching rows in the other table.

• Left Outer Join - In a left outer join - all the rows in the left table would always be present in the output even if the matching row is not present in the right hand table. When a matching right hand row is not present, the row in the left hand table is represented with all null values for the right hand table.
  
  SQL Syntax
  

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

  
  Result
  

  Employee-Department Table

  LastName	Employee.DepartmentID	DepartmentName	Department.DepartmentID
  Rafferty	31	Sales	31
  Jones	33	Engineering	33
  Steinberg	33	Engineering	33
  John	NULL	NULL	NULL

  
  
• Right Outer Join - A right outer join is similar to a left outer join except that all the rows in the right table would always be present in the output. For most practical cases the right outer join isn't used and instead replaced by an equivalent Left Outer Join by interchanging the left and right table.
   
• Full Outer Join - In a full outer join all rows from both the tables are present in the result-set. Row which do not have a matching condition are represented once with null values filled in for the other table.
  
  SQL Syntax
  

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

  
  Result

  Employee-Department Table

  LastName	Employee.DepartmentID	DepartmentName	Department.DepartmentID
  Rafferty	31	Sales	31
  Jones	33	Engineering	33
  Steinberg	33	Engineering	33
  John	NULL	NULL	NULL
  NULL	NULL	Clerical	34
  NULL	NULL	Marketing	35

---

3. Cross Joins - Returns the Cartesian product of rows from tables involved in the join. Rarely useful.
   
   Usual way of writing the query,

   SELECT *
   FROM   employee, department;

   
   SQL Syntax
   

   SELECT *
   FROM   employee 
   CROSS JOIN department
   

   
   Results
   This would result in 16 rows - each row in the left hand table would be joined with each single row in the right hand table,
   

   Employee Table

   LastName	Employee.DepartmentID	Department .DepartmentID	DepartmentName
   Rafferty	31	31	Sales
   Rafferty	31	33	Engineering
   Rafferty	31	34	Clerical
   Rafferty	31	35	Marketing
   ...
   John	NULL	31	Sales
   John	NULL	33	Engineering
   John	NULL	34	Clerical
   John	NULL	35	Marketing

---

4. Self Joins - When both tables involved in the join is the same table - the join is said to be a self join. A self join may either be a inner join, outer join or a Cartesian product.

Foot Notes

1. Oracle Queries = That's the database I use day in and day out. Oracle is what I'm interested in, thought most of this is pure SQL. 
2. Reference - The post is based heavily on the wikipedia article on SQL Joins.