JOINS IN SQL SERVER WITH EXAMPLE

DEFINATION:

JOINS ACTUALY PERFORMS ACTUALY TWO ORE MORE TABLES COMBINED INTO A SINGLE RESULT SET.TO COMBINE DIFFERENT TABLES,TWO OR MORE DATABASE ,DIFFERENT SERVERS.

THERE ARE FOUR TYPES OF JOINS AVAILABLE IN SQL SERVER

1) INNER JOIN
2) OUTER JOIN(LEFT OUTER JOIN,RIGHT OUTER JOIN)
3) FULL JOIN.
4) CROSS JOIN
5) SELF JOIN

INNER JOIN:

TWO TABLE HAVING COMMON COLUMN VALUES GO WITH INNER JOIN

SELECT * FROM <TABLE1> INNER JOIN <TABLE2> ON TABLE1.COLUMNNAME=TABLE2.COLUMNNAME

OUTER JOIN:

OUTER JOIN HAS TWO SUB TYPES LEFT OUTER JOIN AND RIGHT OUTER JOIN

LEFT OUTER JOIN:

SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMNNAME=TABLE2.COLUMNNAME WHERE COLUMNNAME='XYX'

RIGHT OUTER JOIN:

SELECT * FROM TABLE1 RIGHT OUTER JOIN TABLE2 ON TABLE1.COLUMNNAME=TABLE2.COLUMNNAME

FULL JOIN:

COMBINE WITH LEFT OUTER JOIN AND RIGHT OUTER JOIN

SELECT * FROM TABLE1 FULL JOIN TABLE2 ON TABLE1.COLUMNNAME=TABLE2.COLUMNNAME

CROSS JOIN:

CROSS JOIN IS AN CARTESIAN PRODUCT CROSS JOIN CANNOT USE WHERE CONDITION

SELECT * FROM TABLE1 CROSS JOIN TABLE2

REGARDS





sriram