| Joins [message #327905] |
Wed, 18 June 2008 06:15  |
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  |
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 |
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 #327917 is a reply to message #327914] |
Wed, 18 June 2008 06:35 |
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 #327926 is a reply to message #327917] |
Wed, 18 June 2008 06:49 |
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 #328125 is a reply to message #327905] |
Thu, 19 June 2008 00:52 |
Frank
Messages: 7901
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 |
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 |
Frank
Messages: 7901
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 |
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 |
Frank
Messages: 7901
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)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
