Although when you think about which one executes faster & retrieval time of the query using joins vs subquery, join always wins. but in some cases using subqueries is a wise decision instead of join because of some obvious reasons like joins can get complex sometimes and may not be that easy to read, you know there are a lot of types in join and which one to choose can be confusing so subquery can rescue, still I know the performance of joins is better.
so to follow with me, you will need the data set which you can create by just simply opening and running the file in MySQL. download file here .
download the above file and open it in MySQL then select all and just run it, then open the new SQL tab... and here starts the real stuff.
Understanding the data
here we are getting to know our dataset, with the below queries you will be returned with a list of tables available in our data set here we have only three tables.
use school;
show tables;
# returns
# Tables_in_school
# subject
# teacher
# teacher_subject
let's look at fields and attributes available in the tables
desc teacher;
desc subject;
desc teacher_subject;
Run each of the above queries individually, you will understand the fields and primary keys and data types of different columns as well as connecting factors between these three tables.
here you can easily conclude that there are three columns in the teacher table where tid is the primary key, then the subject table has two columns where sid is the primary key, and lastly, in the third table, we have two columns tid and sid which is together a composite key.
with this basic understanding, we can conclude that the teacher_subject table is a junction table for the other two tables.
now we understood the data and table structure, let's start with writing subqueries for getting the following answer
write a subquery to retrieve all subject names taken by teacher Arun
disclaimer: I know it's very easy to retrieve the same by using the inner join but here we care about subquery.
here we are going to approach the same with steps, likewise, we are going to divide the query into small steps, and then we will merge the same to form a subquery.
follow these steps
# step 1 Retrieve tid for tutor Arun
select tid from teacher where tname ='Arun';
# step 2 Retrieve sid for tid received in step 1 query
select sid from teacher_subject where tid = 102;
# step 3 merge step 2 and step 1 queries
select sid from teacher_subject
where tid = (select tid from teacher where tname ='Arun');
# step 4 Retrieve sname for sid received in step 3 query
select sname from subject where sid in (12,14);
# Final step merge step 4 and step 3 queries
select sname from subject where sid in (select sid from teacher_subject
where tid = (select tid from teacher where tname ='Arun'));
This is how we write our subquery, here I want to tell you that what we want is just the final step but while writing we might face some errors, and fixing those errors will make a different level of headache in this approach we have a proper understanding of our subquery in each stage so even if get caught into some errors it will be very easy to traceback and most importantly this approach gets some sort of clarity in mind.
so writing subquery is a very important skill, sometimes these queries may get too much longer so practicing it in a clear way makes it easy to document.
here is the end of this small exercise, if want something to do on this same data set then I have a challenge for you, try to retrieve the below query.
Write a query to retrieve all the tutors who are taking mathematics subject using a subquery
The answer key is here
I would love to hear out from you if you find some errors or issues while doing so. reach out here
thanks for reading, see you in the next one.