Home > Software > Testing > Manual-Testing
Interview Questions   Tutorials   Discussions   Programs   Videos   

Manual-Testing - What are the different types of joins?




276
views
asked marvit October 12, 2014 12:19 AM  

What are the different types of joins?


           

1 Answers



 
answered By cipher   0  

1.Inner Join-Inner join returns only those records/rows that match/exists in both the tables. Syntax for Inner Join is as

Select * from table1 as t1 inner join table2 as t2on t1.IDcol=t2.IDcol

2.Outer Join-We have three types of Outer Join.

2.1 Left Outer Join- Left outer join returns all records/rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values. Syntax for Left outer Join is as :

Select * from table1 as t1 left outer join table2 as t2 on t1.IDcol=t2.IDcol

2.2 Right Outer Join-Right outer join returns all records/rows from right table and from left table returns only matched records. If there are no columns matching in the left table, it returns NULL values. Syntax for right outer Join is as

Select * from table1 as t1 right outer join table2 as t2 on t1.IDcol=t2.IDcol

2.3 Full Outer Join-Full outer join combines left outer join and right outer join. This join returns all records/rows from both the tables.If there are no columns matching in the both tables, it returns NULL values. Syntax for full outer Join is as :

Select * from table1 as t1 full outer join table2 as t2 on t1.IDcol=t2.IDcol

  1. Cross Join-Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table. Syntax for right outer Join is as :

Select * from table1 cross join table2

4.Self Join-Self join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically we have only three types of joins : Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to itself. Hence Self join is not a type of Sql join.

flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert