Database - JOINS

SQL joins are used to query data from two or more tables,

based on a relationship between certain columns in these tables.

TYPES OF JOINS

   1.Inner join

   2.Outer join

       * Left outer join

       * Right outer join

       * Full outer join

   3.Cross join

   4.Self join

INNER JOIN

   Return rows when there is at least one match in both tables.

 SYNTAX:-

         SELECT column_name(s)

         FROM table_name1

         INNER JOIN table_name2

         ON table_name1.column_name=table_name2.column_name

LEFT OUTER JOIN

 Return all rows from the left table, even if there are

 no matches in the right table.

    The LEFT JOIN keyword returns all rows from the left table

(table_name1), even if there are no matches in the right table (table_name2).

RIGHT OUTER JOIN

The RIGHT JOIN keyword returns all the rows from the right table (table_name2),

even if there are no matches in the left table (table_name1).

FULL OUTER JOIN

The FULL JOIN keyword return rows when there is a match in one of the tables.

SYNTAX:-

SELECT column_name(s)

FROM table_name1

FULL JOIN table_name2

ON table_name1.column_name=table_name2.column_name

CROSS JOIN

    A cross join that does not have a WHERE clause produces the Cartesian product of the

 tables involved in the join. The size of a Cartesian product result set is the number

 of rows in the first table multiplied by the number of rows in the second table.

(In case if we use where clause,it will be converted as inner join)

SELF JOIN

Self Join in SQL Server 2000/2005 helps in retrieving the records

 having some relation or similarity with other records in the same

 database table. A common example of employees table can do more clearly

 about the self join in sql. Self join in sql means joining the single

 table to itself. It creates the partial view of the single table

 and retrieves the related records. You can use aliases for the same

 table to set a self join between the single table and retrieve the

 records satisfying the condition in where clause.