Sub quires are easy to understand and can be developed by using simple quires. These queries will have two parts , one is inner queries or inner select and outer queries or outer select.
These queries are easy to develop and most of the time preferred over complex joins.
There are some restriction in using sub queries. We cant modify the structure of a table by using sub query.
Let us start with some examples by using different types of queries using various sql commands.
select * from student where name Like (Select name from student where mark =86)
Subquery with not in
This query will return if any record is there where class data is not there.
select * from student where class not in (Select distinct(class) from student)
Subquery By using ANY
We will get all the records as we don't have any blank class data
select * from student where class = any (Select distinct(class) from student)
select * from student where mark = (Select max(mark) from student)
The above query will return the details of the student who got maximum mark. This query will also work if there are two students with same maximum mark. But if we use only Max command then we will get only one record.
Subquery with Not In
select * from student where class not in (Select max(mark) from student where class= any (select distinct class from student)) .
Using Union and NOT IN
We have one student table and two games table football and baseball. Some students are selected for football team and for baseball team.
Find out the students who are not selected for any of the teams. Or find the records in student table which doesn't have matching record in football table or baseball table.
You can get similar result by using LEFT JOIN of three tables.
SELECT * FROM `student` WHERE id NOT IN
(SELECT f_id from student_football UNION select b_id from student_baseball)
Dowload the sql dump of our student table