What is Join and its type?

Join:-
          A join is used to view or fetch information from multiple tables.

Types of Join

  1. Cross Join
  2. Natural Join
  3. Equi Join or Simple Join or Inner Join
  4. Self Join
  5. Full Outer Join
 
 
Cross Join :-
            When we do not use and where condition between tables that called cross join. It is also called Cartesian Products.
            Suppose first table have 10 records and second table have 5 records then output is 50 records.
Example:-
           SELECT e.employee_id,e.first_name,d.department_id,d.department_name
           FROM employees e, departments d;
           
           
Natural Join :-
                The Natural join clause is based on all columns in the two tables that have same name and same data type and return only matched data from two
                tables.
Example:-
        SELECT employee_id, last_name,department_name,location_id
        FROM employees NATURAL JOIN departments;
       
       
Inner Join :-
             This type of join involves primary key and foreign key complements.

Example:-
        SELECT e.employee_id, d.department_id
        FROM employees e.departments d
        WHERE e.department_id = d.department_id;
       

Self Join :-   
            When table join to itself called SELF Join.
           
Example:-
         SELECT e.last_name employee_name, m.last_name manager_name
         FROM employees e, employees m
         WHERE e.manager_id = m.employee_id;
       
       
Outer Join:-
            Sometimes we need to find match and unmatched record both from the tables so we use OUTER join.
Type of Outer Join:-
    a) Left Outer Join
    b) Right Outer Join
    c) Full Outer Join
   
    a) Left Outer Join:-
                        Left Outer Join means we fetch all the record (matched and unmatched) from the left tables in a query.
        Example:-
                SELECT e.employee_id.e.last_name,d.department_name
                FROM employees e LEFT OUTER JOIN departments d
                ON(e.department_id = d.department_id);
   
    b) Right Outer Join:-
                        Right Outer Join means we fetch all the record (matched and unmatched) from the right tables in a query.
        Example:-
                SELECT e.employee_id.e.last_name,d.department_name
                FROM employees e RIGHT OUTER JOIN departments d
                ON(e.department_id = d.department_id);   
           
    c) Full Outer Join:-
                        Full Outer Join means we fetch all the record (matched and unmatched) from the both tables in a query.
        Example:-
                SELECT e.employee_id.e.last_name,d.department_name
                FROM employees e FULL OUTER JOIN departments d
                ON(e.department_id = d.department_id);        

   
USING Clause:-
           
Example:-
        SELECT e.employee_id, d.department_id
        FROM employees e.departments d
        USING (department_id);
       
       
ON Clause:-
            Use ON clause to specify a join condition. This let you specify join conditions separate from any search condition on WHERE clause

Example:-
        SELECT e.employee_id,e.last_name d.department_id, d.department_name
        FROM employees e.departments d
        ON (e.department_id = d.department_id);