Subquery vs inner join

1. Use joins when we need to get data from both the tables in SELECT statement.

2. Use sub query when we need to get data from only one table and other table is used only to check existence. 

Inner join: By using inner join we can select fields of both the tables. For example:

 

SELECT

E.ntEmpID,

E.vcName,

E.vcName,

ED.moSalary,

ED.dtDOJ

FROM tblEmp E INNER JOIN tblEmpDetail ED

ON E.ntEmpID = ED.ntEmpID

 

Sub query: we can get the data from only table. For example:

 

SELECT

E.ntEmpID,

E.vcName,

E.vcName

FROM tblEmp E

WHERE ntEmpID IN (SELECT ntEmpID

FROM tblEmpDetail)

 

Here we cannot include fields of table tblEmpDetail in select list. So if we need to get the data from fields of both tables join is only solution. It is not possible by subquery.

 

When we need to get the data from only one table by checking the existence in other table then it is possible by both join as well as subquery. In this case which one will perform better inner join or subquery?

 

Here we have written a same query in four different ways:

 

–Using inner join

SELECT E.*

FROM tblEmp E INNER JOIN tblEmpDetail ED

ON E.ntEmpID = ED.ntEmpID

 

–Using IN clause

SELECT *

FROM tblEmp

WHERE ntEmpID IN (SELECT ntEmpID

FROM tblEmpDetail

 

–Using EXISTS clause

SELECT *

FROM tblEmp E

WHERE EXISTS(SELECT *

FROM tblEmpDetail

WHERE ntEmpID = E.ntEmpID)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s