Joins in SQL Server.

                                                                   JOINS

Types of Joins 

• Inner join 
• Outer join (right , left and full outer)
• Cross join
• Self Join             

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables
• SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
• CARTESIAN JOIN: Returns the Cartesian product of the sets of records from the two or more joined tables.

 Joins with Examples :

create table tableA ( 
    a int 
   ,b int 
);

create table tableB ( 
   a int 
  ,b int 
);



insert into tableA (a,b) values (1,2); 
insert into tableB (a,b) values (1,2); 


insert into tableA (a,b) values (11,12); 
insert into tableB (a,b) values (21,22); 

Queries on joins:

Inner Join: select * from tableA a inner join tableB b on   a.a=b.a

Result:

Left outer join:select * from tableA a left join tableB b on a.a=b.a
Result:

EXAMPLE2:   select * from tableA a left join tableB b on a.a=b.a where b.a is null

 Right outer Join:select * from tableA a right join tableB b on a.a=b.a
Result:

EXAMPLE2:select * from tableA a right join tableB b on a.a=b.a where a.a is null

Full outer join:select * from tableA a full outer join tableB b on a.a=b.a
Result:

Cross Join:select * from tableA a cross join tableB b
Result: 
                                         

Self Join: select a.a,a.b,b.a,b.b from tableA a inner join tableA b on a.a=b.a

Result: