Home » Other » Training & Certification » Joins
Joins [message #327905] Wed, 18 June 2008 06:15 Go to next message
imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad
Member

Hi

The question comes infront of me in an interview for DBA. The employeer wanted to know about any alternative way for fetching data from two tables (join together with a primary-foreign key relationship) other than joins.
I became answerless as i have never come across such situation... The employeer was of the view that using joins requires large query processing time and therefore degrade the system performance.

Anyone having an idea about it?
Re: Joins [message #327906 is a reply to message #327905] Wed, 18 June 2008 06:17 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Alternative: Use Subquery..

SELECT EMPID FROM EMP 
WHERE DEPT_ID IN ( SELECT DEPT_ID FROM DEPT);


DEPT_ID IN DEPT IS THE PRIMARY KEY.
DEPT_ID IN EMP IS THE FOREIGN KEY...

[Updated on: Wed, 18 June 2008 06:20]

Report message to a moderator

Re: Joins [message #327910 is a reply to message #327906] Wed, 18 June 2008 06:26 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
better option:
SELECT EMPID FROM EMP 
WHERE EXISTS ( SELECT DEPT_ID FROM DEPT WHERE DEPT_ID = EMP.DEPT_ID);


faster in case there is too many data in dept table

[Updated on: Wed, 18 June 2008 06:27]

Report message to a moderator

Re: Joins [message #327912 is a reply to message #327905] Wed, 18 June 2008 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course if statistics are up to date, optimizer will rewrite your query.

Regards
Michel
Re: Joins [message #327914 is a reply to message #327910] Wed, 18 June 2008 06:32 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@sarwagya

Yeah, It is. But here are we not using the concept of Joins?
Re: Joins [message #327917 is a reply to message #327914] Wed, 18 June 2008 06:35 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
I think this is not a join.
If I am right, it is a correlated query.

Joins start with cartesian product. But, correlated query doesn't.

If I am wrong, please correct me.
Re: Joins [message #327925 is a reply to message #327917] Wed, 18 June 2008 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Joins start with cartesian product.

Can we way that if there is no join condition it is a join?
Just a philosophical question. Smile

SQL Reference, Joins, Subqueries

Regards
Michel
Re: Joins [message #327926 is a reply to message #327917] Wed, 18 June 2008 06:49 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I believe that here JOIN is being used.


In other way
SELECT EMPID FROM EMP 
WHERE EXISTS ( SELECT DEPT_ID  FROM DEPT D WHERE D.DEPT_ID = EMP.DEPT_ID);



Am I wrong? Suggestion please...

[Updated on: Wed, 18 June 2008 06:50]

Report message to a moderator

Re: Joins [message #327927 is a reply to message #327926] Wed, 18 June 2008 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a matter of definition, see Oracle ones (not saying it is not ANSI or other ones) in the links I posted.

Regards
Michel
Re: Joins [message #328037 is a reply to message #327926] Wed, 18 June 2008 12:24 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Olivia
I believe that here JOIN is being used.


... WHERE D.DEPT_ID = EMP.DEPT_ID

Well, it does look like a join, doesn't it?
Re: Joins [message #328125 is a reply to message #327905] Thu, 19 June 2008 00:52 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
imran_nu wrote on Wed, 18 June 2008 13:15
The employeer was of the view that using joins requires large query processing time and therefore degrade the system performance.

Anyone having an idea about it?

You should have told him that this isn't the case in Oracle, so the question would be irrelevant.

Nice to see that so many people dive into answering the question instead of thinking about its relevance first.
Re: Joins [message #328188 is a reply to message #328125] Thu, 19 June 2008 05:25 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Frank wrote on Thu, 19 June 2008 00:52
imran_nu wrote on Wed, 18 June 2008 13:15
The employeer was of the view that using joins requires large query processing time and therefore degrade the system performance.

Anyone having an idea about it?

You should have told him that this isn't the case in Oracle, so the question would be irrelevant.

Nice to see that so many people dive into answering the question instead of thinking about its relevance first.


Really? Is it?
Re: Joins [message #328382 is a reply to message #328188] Fri, 20 June 2008 00:31 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
Is what?

Joins not expensive?
In SQL-Server/Sybase (at least some older versions), joining tables, especially more than two or three, is costly. This is the reason most Sybase/SQL-Server programs depend heavily on storing intermediate results in temporary tables.
In Oracle on the other hand, joining tables is not costly; the databae was made to do joins, it is good at it, so you should let it do its work.
This is the reason for all the 'Lose the loops, don't do in PL/SQL if you can do it in a single SQL' advises you can see throughout this forum.
Re: Joins [message #328389 is a reply to message #328382] Fri, 20 June 2008 00:55 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Just for the sake of knowledge, I want to ask - which of the following queries is good from the point of view of performance?

SELECT EMP_ID 
FROM EMP 
WHERE EXISTS ( SELECT DEPT_ID 
               FROM DEPT 
               WHERE DEPT_ID = EMP.DEPT_ID);


SELECT EMP_ID 
FROM EMP, DEPT
WHERE EMP.DEPT_ID = DEPT.DEPT_ID;



Re: Joins [message #328394 is a reply to message #328389] Fri, 20 June 2008 01:02 Go to previous message
Frank
Messages: 7877
Registered: March 2000
Senior Member
These are two different queries. The results can differ (if dept.dept_id would not have been the Primary Key, then there could have been multiple DEPTs with the same DEPT_ID, resulting in duplicate rows in the resultset for the join)
Previous Topic: oracle 9i rman
Next Topic: help with basic select statments
Goto Forum:
  


Current Time: Wed Apr 16 18:12:12 CDT 2014

Total time taken to generate the page: 0.09081 seconds