Joining tables by skipping the intermediate parent table We've tables adm_app, quals_data to which personal_data is a parent table. We are retrieving data from another table sub_marks which has a foreign key of quals_data and the query is like this

select
      sub_marks.obtain_marks,
      sub_marks.convertion_marks,
      sub_marks.max_marks
from sub_marks
inner join quals_data ON quals_data.id = sub_marks.quals_data_id
inner join adm_app on quals_data.personal_data_id = adm_app.personal_data_id
where adm_app.app_no = 2000;


You can see that we've skipped the join with personal_data because we are not using its data anywhere. Is this a good way of writing a query? Previously we wrote query like this


select
      sub_marks.obtain_marks,
      sub_marks.convertion_marks,
      sub_marks.max_marks
from sub_marks
inner join quals_data ON quals_data.id = sub_marks.quals_data_id
inner join personal_data ON personal_data.id = quals_data.personal_data_id
inner join adm_app on adm_app.personal_data_id = personal_data.id
where adm_app.app_no = 2000;
Your changes to the query are good changes.

Just because the both adm_app and quals_data have the same child relationship to the personal_data table does not mean you have to include the personal_data table in a join if there's no relevant data on it.

You should run both of your queries with the query-plan EXPLAIN to show the difference in the queries. Avoiding the join will make for a simpler query with better performance.

Having said that, what about the rest of your query, are there improvements that can be made? Nothing big, really. I am not a big fan of snake_case for table and column names, but it is more important that you are consistent, and neat with the concept.

If I were to have any complaints, it would just be two really small things:


a spelling error.... I know, it's seriously pedantic, but in my "real job" I would point out the error soon because it is the sort of thing that is hard to correct later... convertion should be spelled conversion
abbreviations should be consistent in names. Since you have personal_data and fully named columns like convertion_marks, then it follows that you should not have abbreviations for adm_app and quals_data. Those should be full names.


As you can tell, the only issues I can see are really trivial.