join issue, what are the best alternatives?

Q:

I have 5 or 6 tables , and i need data from these tables : i make the following join statement to accomplish my query.

SELECT d.batch_no,d.studytype_code,d.camp_code,d.dep_code,d.start_date,d.end_date,a.year,a.term_code,c.studytype_name ,e.dep_name_ar,f.camp_name_ar
FROM llkbatch a , llkbatch_category b , mm19studytype c ,llkbatch_exception d  ,llkdepartment e,llkcamp f
WHERE   a.batch_no = b.batch_no
AND  b.studytype_code = c.studytype_code 
AND  b开发者_运维百科.batch_no = d.batch_no
AND b.studytype_code = d.studytype_code 
AND d.dep_code = e.dep_code
AND d.camp_code = f.camp_code

but i think that the join sometimes is less performance way to do things like that,is there any alternative to this in a programming way or in the database layer, i mean alternatives to joins in general and when should i go away from the joins. thanks a lot.

There is a more modern ANSI join syntax that will make your joins more readable, but it should not affect performance:

SELECT d.batch_no,d.studytype_code,d.camp_code,d.dep_code,d.start_date,d.end_date,
       a.year,a.term_code,c.studytype_name ,e.dep_name_ar,f.camp_name_ar
FROM ra1batch a 
JOIN ra1batch_category b ON a.batch_no = b.batch_no
JOIN ra1studytype c ON b.studytype_code = c.studytype_code
JOIN ra1batch_exception d ON b.batch_no = d.batch_no
                         AND b.studytype_code = d.studytype_code 
JOIN rr1department e ON d.dep_code = e.dep_code
JOIN rr2camp f ON d.camp_code = f.camp_code

The query you have written in your question follows the syntax of Oracle. But you use joins as if you are using SQL Server. It will give you better performance in comparison with your current query.

The below query will parse faster compared to your current query.

SELECT d.batch_no, d.studytype_code, d.camp_code, …
FROM ra1batch a
INNER JOIN ra1batch_category b on a.batch_no = b.batch_no