Joins

Join is a query in which data is retrieved for two or more table. A join matches the data from two or more tables, based on the values of one or more columns in table.

Need for joins

In a database where the tables are normalized, one table may not give you all information about a particular entity. So for comprehensive data analysis, you must assemble data from several tables.

Different types of joins

·         Inner join

·         Outer join

·         Natural join

Inner join

Inner join returns the matching rows form the tables that are being joined.

Consider the following relations

1.      Employee(emp_name, city)
2.      Employee_salary(emp_name, department, salary)

Example 1:

SQL> select employee.emp_name,employee_salary.salary from employee   inner join

           employee_salary

           on

           employee.emp_name=employee_salary.emp_name;

Shows the following results

Example 2

SQL> select * from employee   inner join   employee_salary

           on

           employee.emp_name=employee_salary.emp_name;

Shows the following results

Outer join

The outer join can be nay one of the following:

·         Left outer

·         Right outer

·         Full outer

Left outer join

The left outer join returns matching rows from the table being joined, and also matching rows from the left table in the result and places null values in the attributes that comes from the right table.

Example 1:

select employee.emp_name,salary from employee  left outer join employee_salary

on

employee.emp_name=employee_salary.emp_name;

Shows the following result

Right outer join

The right outer join operation returns matching rows from the tables being joined and also non-matching rows from the right table in the result and places null values in the attributes that comes from the left table.

Example 1:

SQL> select employee.emp_name,salary from employee right outer join

           employee_salary

            on

            employee.emp_name=employee_salary.emp_name;

Shows following result