SubQuery for Beginners

Sub query  - Embedding a SQL statement within another SQL statement. It can be used with sql comparison operators like =,<.>,<=,>= etc.,

Additionally, we can write with LIKE IN, NOT IN, ANY and IN operators. Now you may have question when we have to use LIKE IN etc., and comparison operators while writing query.

Things to remember:

1. Sub query may return one row or more than one row.

2. If it will return more than one row then you should use IN operator.

3.It must be enclosed with parenthesis.

4. You can not use ORDER BY in sub query. Instead of that, you can use GROUP BY.

Sub Query Format:

SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator] or [LIKE IN, ANY etc.,]
(SELECT "column_name3"
FROM "table_name2"
WHERE "condition");

Highlighted in blue color is inner query and highlighted in red color is called outer query. Let see the example one by one, so that we able to understand more clearly.

Example I: Sub Query with IN operator

ID     NAME  AGE PLACE     SALARY  

  1     Rajesh     35    Chennai       2000.00
  2     Velava     25    Mumbai       1500.00
  3     kajol        23    Hyderabad   2000.00
  4     Mukesh   24    Madurai       10000.00

Sub Query:

SELECT * 
FROM CUSTOMERS 
WHERE ID IN (SELECT ID 
             FROM CUSTOMERS 
             WHERE SALARY > 1800) ;

Output:

 ID   NAME      AGE  PLACE      SALARY  

  1     Rajesh        35     Chennai          2000.00 
  3     kajol           23     Hyderabad      2000.00 
  4     Mukesh      24     Madurai        10000.00 

Note: Above query uses IN operator, due to inner query will return more than one rows.

Sub Query with Comparison Operator

        student                                     marks

Student Id         Name                                 Student Id                 Marks      
     1                Kalpana                                       1                               91
     2                Peter                                             2                               89
     3                Rajini                                           3                               97
     4                Ajith                                             4                               96 
     5                Vijay                                             5                               95

Ok, Now the question is , How to identify all students who get high marks than one of the student who`s id is 5. Here, you don't know marks of student id 95.

So we can split this question as two,
 1. What s the mark of student Id 95 ? (95Marks)
 2. Who are all getting high marks than studetn Id 95 marks? (>95 Marks)

For 1st question, query is below,

Select * from marks
where student_id = '5'

Output:

Student Id     Marks
       5                  95

Now second question, query is below,

Select a1.student_id,a1.name,m1.marks 
from student s1, marks m1
where s1.student_id = m1.student_id
AND m1.marks > 95

Output:

studentId     name       marks
3                   Rajini         97
4                   Ajith           96

Now combine these two query, 

SELECT a.studentid, a.name, b.marks  
FROM student a, marks b  
WHERE a.studentid = b.studentid AND b.marks >  
(SELECT marks  
FROM marks  

WHERE studentid =  '95');  

Output:

studentId     name       marks
3                   Rajini         97
4                   Ajith           96