Multiple Select Queries vs Single Join Query

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
csailor
Member
Posts: 1
Joined: Thu Dec 03, 2009 2:49 pm

Multiple Select Queries vs Single Join Query

Post by csailor » Thu Dec 03, 2009 2:58 pm

Hi,
Which one of the 2 approaches below is better provided indexes are availabe on the columns - id & dno in respective tables.

Code: Select all

select f.dno, d.dname, c.sname.... 
into :f-dno, :d-dname, :c-sname... 
from 
faculty f,  dept d, stud s 
where f.dno = d.dno 
   and f.dno = s.dno 
   and f.id = :host-var-fac-id 
 
------OR----------

Code: Select all

select f.dno,... into :f-dno,... 
from faculty f where f.id = :host-var-fac-id 

select d.dno, d.dname.... into :d-dno,.... 
from dept d where d.dno = :f-dno 

select s.dno, s.sname... into :s-dno,... 
from stud s where s.dno = :d-dno 


----------------------------------------------------
Elaborate explanation would be appreciated[/code]

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Fri Dec 04, 2009 1:00 pm

This thread is alraedy got an answer in other Forum, why don't you follow up there?
Regards,
Anuj

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others