Tuesday, January 17, 2012

MySQL's Subquery

Subquery is a query inside another query.  While this can be a powerful tool on tables, one is however caution as nested queries may cause ambiguity and confusion.


We can  create a simple query on the table above; for instance, if we need to display only students enrolled in Computer or Science, our SQL command shall be:

SELECT * FROM STUDENT where subject ='Computer' or subject = 'Science'

or we can use IN from a given list:

SELECT * FROM STUDENT where subject  IN ('Computer', 'Science')

Using a subquery, the commands above can be rewritten as:

SELECT * FROM  STUDENT    where subject in (select subject from  STUDENT   where subject ='Computer' or  subject ='Science')

To display students below 15 years old using subquery, it shall be:

SELECT * FROM  STUDENT      where age in (select age from  STUDENT      where age <15)

Just remember that subquery or inner query is performed first before the subquery and that the field or column that is validated must match the resulting set from the inner query.