How to use sub-query to get required data from multiple tables

 When the end result we need, requires us to take data from multiple tables and put it together in one table, this can be accomplished in 2 ways.

Either we join tables using primary keys or use sub-query.

Let's see how can we do it using sub-query by following an example.

We are given 3 tables...

students table has data related to student age, student number and student name

courses table has data related to course_number, course_title and credits per course

student_enrollment table has data related to student_number and course_number

Consider following aspects about our data...

• The students table is not directly related to the courses table. It just contains student details. 
• The courses table just contains courses information. 
• The table that relates both the students table and courses table is the student_enrollment table. Which student is enrolled in which course is captured in the student_enrollment table.

The puzzle to solve is...

Without joining tables, Find out names of students who are taking Physics and US History courses

We can start by writing query that gives us students taking those 2 courses from courses table. 

Remember courses table has only course_no that we can use to get further information from other tables.

Using these course_no, we can get student_no from student_enrollment table

Now, using student_no received from these queries, we can obtain student_name from students table

This is our end result.

Remember that we have not used any table join but got this result only by writing sub-query under WHERE clause for each table.

We can repeat this for as many table as we need to get our desired result.