Joining Multiple Tables in SQL: Best Practices and Techniques

SQL JOINS :

•  Sometimes we have to combine the data from so many tables.
•  A join combines the data spread across tables.
•  A join is performed by the 'where' clause which combines the specified rows of tables.
•  A common column provides the join condition.

Types of joins :

 There are three types of joins. They are

  1. Simple join or inner join

  2. Self join

  3. Outer Join

1. Inner join

•   The most important and frequently used of the joins is the inner join.
•   They are also referred to as EQUIJOIN.
•   Inner join retrieve only the 'MATCHED RECORDS'.

Example :

  select e.employee_id,e.first_name,e.department_name,d.department_id

  from employees e, departments d

  where e.department_id = d.department_id;

  

 Non-equijoin

•   A join that is based on relational operators other than = is called a non-Equi join.

Example :

  select a.item_id,item_name,stock,quantity

  from item_master a,sales_details b

  where(a.item_id=b.item_id)

  and (a.stock<b.quantity);

  This statement lists all items for which the stock in hand is less than the quantity ordered.

2. Outer Join

•    The outer join extends the result of a simple join.
•    An outer join returns all the rows from one table that do not match any row from the other table.
•    The symbol + is used to represent the outer join.

 Left Outer Join

•    All the records from the left side table and only the matched rows join from the right side table.

 Example :

   select e.employee_id,e.first_name,e.department_name,d.department_id

   from employees e, departments d

   where e.department_id = d.department_id(+);

 Right Outer Join

•    All the records are from the right side table and only the matched rows left side table.

 Example :

   select e.employee_id,e.first_name,e.department_name,d.department_id

   from employees e, departments d

   where e.department_id(+) = d.department_id;

3. Self Join 

•   Self-join is a regular join, but the table is joined with itself.