JOINS IN SQL SERVER  

I have two tables First A1 and Second A2

1). Structure for FULL OUTER JOIN – When you want to display all data from two table into one table then you should use this JOIN.

   Structure (Syntax) - SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
   Example - SELECT * FROM A1 FULL OUTER JOIN A2 ON A1.ID = A2.REF_ID;

2). INNER JOIN – If you want to print common record from both tables then use it.

   Syntax - SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
    Example - SELECT * FROM A1 INNER JOIN A2 ON A1.ID = A2.REF_ID;

3). LEFT OUTER JOIN - The LEFT JOIN keyword returns all records from the left table (A1), and the matched records from the right table (A2). The result is NULL from the right side if there is no match. 

             Syntax - SELECT column name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

             Example - SELECT * FROM A1 LEFT JOIN A2 ON A1.ID = A2.REF_ID;

4). RIGHT OUTER JOIN - The RIGHT JOIN keyword returns all records from the right table (A2) and the matched records from the left table (A1). The result is NULL from the left side when there is no match.  

        Syntax - SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
         Example - SELECT * FROM A1 RIGHT JOIN A2 ON A1.ID = A2.REF_ID;                                                                

5). SELF JOIN - A SELF-JOIN is a regular join, but the table is joined with itself.

Syntax – SELECT * FROM table A1, table A2 WHERE condition;
  Example - SELECT * FROM A1, A2 WHERE A1.ID = A2.REF_ID;

6). CROSS JOIN - In the SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian product.

Syntax - SELECT * FROM table1 CROSS JOIN table2;

  Example - SELECT * FROM A1 CROSS JOIN A2;