INNER JOIN vs Correlated Subquery !

Hi Pals,

Need some help in understanding the internal execution of the below 2 queries.
Theoritically and upto my knowledge , i know Co-related sub-queries takes more time to execute.

Can anybody explain the reason for this.

Is there any difference in comparing/joining the two tables.

select e.ename, d.dname from Emp e INNER JOIN Dept d ON e.deptno = d.deptno

-- Using Correlated Sub-query
select e.ename,
(select dname from dept where deptno=e.deptno)
from Emp e

-- Sample Tables
create table Dept
( deptno int primary key,
dname varchar(20)
insert into Dept values (10,'Accounts')
insert into Dept values(20,'Sales')
insert into Dept Values(30,'Marketing')
insert into Dept values(40,'Production')

create table Emp
(Empno int primary key,
Ename varchar(20),
deptno int
insert into Emp
select 101,'Manu',10
union all
select 102,'Anu',40
union all
select 103,'Phani',30
union all
select 104,'Sharath',20

Please suggest.

Posted On: Tuesday 20th of November 2012 03:06:50 AM Total Views:  258
View Complete with Replies

Related Messages:

Sp vs query?   (208 Views)
I have a general question, wondered if anyone knew what be the most effect way of getting data returned from my MSSQL database from my asp pages. A stored proccedure or using a query string with a connection from the asp page Basically I need to pull info from our site and I wondered what is the quickest way as my server is struggling as it's on a shared server.
MySQL vs SQL Server   (89 Views)
How different is SQL Server from MySQL I just looked at a book for SQL Server and it seems to have a lot of new stuff that I've never even heard of. Also is there an easier way to learn SQL Server instead of reading a 700+ page book
Table join to fetch details   (245 Views)
everbody, I have table1 userid username 1 peter 2 melissa 3 diedra and table2 userid username 1 peter 2 diedra 3 zach I need to find the entries whose userids are same but the usernames differ i.e. the usernames of userid 2 are different in the 2 tables. What is the query to fetch the results
beginner's login   (137 Views)
hello, i have installed SQL server on my machine with IIS already installed on it. when i type http://localhost/webadmin for login it doesn't take me to login page instead of that it shows me all script. shown below Web Data Administrator - Login
File storage vs database storage   (184 Views)
hi, i need some advise regarding storage of data is it good to store data in files rather than storing into database (eg data like comments or additional features of products that users advertise on our website.) will it help for speed optimization.
Comparing two colums in different tables using a join to find non match?   (200 Views)
and gals, I have two tables and need to delete some strays in two related tables. The column names are idProduct and Product_id. I wrote an asp script to import and reverse an import except I had forgotten to reverse one of the two tables. So consiquently there are 151 stray records that I need to find. How would I write a join, or not to find these strays and delete them Thank you so much in advance. cbadchris
Multiple joins?   (161 Views)
Entity Relationships: Person person_id first_name last_name addr1 addr2 etc Patient person_id med_rec_nbr location_id guar_id I want to have a result set that gives me the demographics from person, but the guarantor information from patient also. Kinda like: [someguid], John, Smith, 103 Main St., [addr2]..., 00001, and then the guarantor information appended, since John Smith is the child of James Smith. James smith is listed in the person table as: [someotherguid], James, Smith, 103 Main St., [addr2]... How do I do two joins to the same table and pull a different set of data in the same rowset I have so far: Code: SELECT P.FIRST_NAME, P.LAST_NAME, P.ADDR1, P.ADDR2, PA.MED_REC_NBR FROM PERSON P INNER JOIN PATIENT PA ON P.PERSON_ID = PA.PERSON_ID But I need to put another join between person and patient based upon p.person_id = pa.guar_id, and grab p.first_name and p.last_name based on this new id in person.
Timeout expired error on SQL Server query with subquery   (394 Views)
hi, i have an ASP page that is querying an SQL database, and a SQL timeout expired '80040e31' error occurs on the line. The query is resonably standard, except there is a correlated subquery to do a COUNT of 'other users' who registered lately against the same school. Is there a way to make the query more efficient, (uncorrelate the subquery or something) so that the timeout error does not occur I could take out the subquery, and then do a seperate query on each userID to see if there are 'other users' for that user, but i think that might be less efficient than using the subquery (only about 1 in 10 users has 'other users'). The users table has about 40000 records, and no field is very large, varchar 300 would be about the biggest.
DTS Beginner question   (266 Views)
In order to learn how to create some DTS packages I created a text file to transfer to a test table. I added text file and SQL Server connections to my package and a transform data task connecting them. A simple 1-1 transfer of each column worked fine. Then I added some test code to try making modifications during the transfer. The simplest code, for example: Function Main() DTSDestination("streetno") = "50" End Function gives me a type mismatch runtime error on DTSDestination. The streetno column is an integer and furthermore any combination of source/destination columns and data does the same thing. Please help
cross database join   (271 Views)
A query running fine in QueryAnalyzer creates an error "Invalid object name" when run through a Java connection. The object in error is a table in a database other then the one connected to. Why is this a problem from Java I am using the same login and default database in Java, as in QueryAnalyzer when testing the SQL.