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
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…
convertionshould be spelled
- abbreviations should be consistent in names. Since you have
personal_dataand fully named columns like
convertion_marks, then it follows that you should not have abbreviations for
quals_data. Those should be full names.
As you can tell, the only issues I can see are really trivial.