JOINS IN SQL SERVER

JOINS:

              Some times data retrieve from multiple table using joins.

              Sql joins used to combines rows from two or more tables based on a common field.          

             

             The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

  INNER JOIN:

An Inner Join will take two tables and join them together based on the values in common columns (linking field) from each table.

Syntax:

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

    E.g:       SELECT D.empname, D.empsalary, E.parentid

             FROM empdet D

             INNER JOIN employee E

             ON D.empid=E.id

 ORDER BY D.empname

OUTER JOINS:

                       Outer joins can be a left, a right, or full outer join.

LEFT OUTER JOIN:

                                   

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

       syntex:

    

  SELECT column_name(s)FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

 SELECT D.empname, D.empsalary, E.parentid

            FROM empdet D

            LEFT OUTER JOIN employee E

            ON D.empid=E.id

            ORDER BY D.empname

 RIGHT OUTER JOIN:

This join returns all the rows from the right table in conjunction with the matching rows from the left   table. If there are no columns matching in the left table, it returns NULL values.

Syntax:

           

                 SELECT column_name(s)FROM table_name1 RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

      E.g:     

      SELECT D.empname, D.empsalary,E.parentid FROM empdet D 

RIGHT OUTER JOIN employee E

ON D.empid=E.id

ORDER BY D.empname

FULL OUTER JOIN:

This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.

Syntax:   

                              SELECT column_name(s)FROM table_name1 FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

     E.g:            SELECT D.empname, D.empsalary, E.parentid

FROM empdet D

FULL OUTER JOIN employee E

ON D.empid=E.id

ORDER BY D.empname

CROSS JOIN:

This join is a Cartesian join that does not necessitate any condition to join. The result-set contains records that are multiplication of record number from both the tables.

     E.g:           

            SELECT A.accountid, accountname, aliasname, baseamount

            FROM accountmaster A CROSS JOIN accountalias S

           

UNION AND UNION ALL:

The difference between Union and Union all is that Union all allows the duplicate rows and Union not allows Duplicate values (or) rows.

      E.g:            --UNION:

SELECT empname FROM empdet UNION SELECT first_name FROM employee1

--UNION ALL:

SELECT empname FROM empdet

UNION ALL

SELECT name FROM employee