Which of the following operators can be used between main query and sub query?

In SQL a Subquery can be simply defined as a query within another query. In other words we can say that a Subquery is a query that is embedded in WHERE clause of another SQL query. Important rules for Subqueries:

• You can place the Subquery in a number of SQL clauses: WHERE clause, HAVING clause, FROM clause. Subqueries can be used with SELECT, UPDATE, INSERT, DELETE statements along with expression operator. It could be equality operator or comparison operator such as =, >, =, <= and Like operator.
• A subquery is a query within another query. The outer query is called as main query and inner query is called as subquery.
• The subquery generally executes first when the subquery doesn’t have any co-relation with the main query, when there is a co-relation the parser takes the decision on the fly on which query to execute on precedence and uses the output of the subquery accordingly.
• Subquery must be enclosed in parentheses.
• Subqueries are on the right side of the comparison operator.
• ORDER BY command cannot be used in a Subquery. GROUPBY command can be used to perform same function as ORDER BY command.
• Use single-row operators with singlerow Subqueries. Use multiple-row operators with multiple-row Subqueries.

Syntax: There is not any general syntax for Subqueries. However, Subqueries are seen to be used most frequently with SELECT statement as shown below:

SELECT column_name
FROM table_name
WHERE column_name expression operator 
    ( SELECT COLUMN_NAME  from TABLE_NAME   WHERE ... );

Sample Table:

DATABASE

STUDENT

Sample Queries

:

• To display NAME, LOCATION, PHONE_NUMBER of the students from DATABASE table whose section is A

Select NAME, LOCATION, PHONE_NUMBER from DATABASE 
WHERE ROLL_NO IN
(SELECT ROLL_NO from STUDENT where SECTION=’A’); 

• Explanation : First subquery executes “ SELECT ROLL_NO from STUDENT where SECTION=’A’ ” returns ROLL_NO from STUDENT table whose SECTION is ‘A’.Then outer-query executes it and return the NAME, LOCATION, PHONE_NUMBER from the DATABASE table of the student whose ROLL_NO is returned from inner subquery. Output:

• Insert Query Example:

Table1: Student1

Table2: Student2

• To insert Student2 into Student1 table:

INSERT INTO Student1  SELECT * FROM Student2;

• Output:

• To delete students from Student2 table whose rollno is same as that in Student1 table and having location as chennai

DELETE FROM Student2 
WHERE ROLL_NO IN ( SELECT ROLL_NO 
                   FROM Student1 
                   WHERE LOCATION = ’chennai’);

• Output:

1 row delete successfully.

• Display Student2 table:

• To update name of the students to geeks in Student2 table whose location is same as Raju,Ravi in Student1 table

UPDATE Student2 
SET NAME=’geeks’ 
WHERE LOCATION IN ( SELECT LOCATION 
                    FROM Student1 
                    WHERE NAME IN (‘Raju’,’Ravi’));

• Output:

1 row updated successfully.

• Display Student2 table:

This article is contributed by RanjaniRavi. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to [email protected]. See your article appearing on the GeeksforGeeks main page and help other Geeks. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

You can use a subquery in a SELECT, INSERT, DELETE, or UPDATE statement to perform the following tasks:

• Compare an expression to the result of the query.
• Determine if an expression is included in the results of the query.
• Check whether the query selects any rows.

Syntax :

• The subquery (inner query) executes once before the main query (outer query) executes.
• The main query (outer query) use the subquery result.

SQL Subqueries Example :

In this section, you will learn the requirements of using subqueries. We have the following two tables 'student' and 'marks' with common field 'StudentID'.

Now we want to write a query to identify all students who get better marks than that of the student who's StudentID is 'V002', but we do not know the marks of 'V002'.
- To solve the problem, we require two queries. One query returns the marks (stored in Total_marks field) of 'V002' and a second query identifies the students who get better marks than the result of the first query.

First query:

SELECT *  
FROM `marks`  
WHERE studentid = 'V002';

Query result:

The result of the query is 80.
- Using the result of this query, here we have written another query to identify the students who get better marks than 80. Here is the query :

Second query:

SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid
AND b.total_marks >80;

Relational Algebra Expression:

Relational Algebra Tree:

Query result:

Above two queries identified students who get the better number than the student who's StudentID is 'V002' (Abhay).

You can combine the above two queries by placing one query inside the other. The subquery (also called the 'inner query') is the query inside the parentheses. See the following code and query result :

SQL Code:

SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid AND b.total_marks >
(SELECT total_marks
FROM marks
WHERE studentid =  'V002');

Query result:

Pictorial Presentation of SQL Subquery:

Subqueries: General Rules

A subquery SELECT statement is almost similar to the SELECT statement and it is used to begin a regular or outer query. Here is the syntax of a subquery:

Syntax:

(SELECT [DISTINCT] subquery_select_argument
FROM {table_name | view_name}
{table_name | view_name} ...
[WHERE search_conditions]
[GROUP BY aggregate_expression [, aggregate_expression] ...]
[HAVING search_conditions])

Subqueries: Guidelines

There are some guidelines to consider when using subqueries :

• A subquery must be enclosed in parentheses. 
• A subquery must be placed on the right side of the comparison operator. 
• Subqueries cannot manipulate their results internally, therefore ORDER BY clause cannot be added into a subquery. You can use an ORDER BY clause in the main SELECT statement (outer query) which will be the last clause.
• Use single-row operators with single-row subqueries. 
• If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.

Type of Subqueries

• Single row subquery : Returns zero or one row.
• Multiple row subquery : Returns one or more rows.
• Multiple column subqueries : Returns one or more columns.
• Correlated subqueries : Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.
• Nested subqueries : Subqueries are placed within another subquery.

In the next session, we have thoroughly discussed the above topics. Apart from the above type of subqueries, you can use a subquery inside INSERT, UPDATE and DELETE statement. Here is a brief discussion :

Subqueries with INSERT statement

INSERT statement can be used with subqueries. Here are the syntax and an example of subqueries using INSERT statement.

Syntax:

INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ];

If we want to insert those orders from 'orders' table which have the advance_amount 2000 or 5000 into 'neworder' table the following SQL can be used:

Sample table: orders

SQL Code:

INSERT INTO neworder
SELECT * FROM  orders
WHERE advance_amount in(2000,5000);

Output:

To see more details of subqueries using INSERT statement click here.

Subqueries with UPDATE statement

In a UPDATE statement, you can set new column value equal to the result returned by a single row subquery. Here are the syntax and an example of subqueries using UPDATE statement.

Syntax:

UPDATE table  SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)     
[ WHERE) ]

If we want to update that ord_date in 'neworder' table with '15-JAN-10' which have the difference of ord_amount and advance_amount is less than the minimum ord_amount of 'orders' table the following SQL can be used:

Sample table: neworder

SQL Code:

UPDATE neworder
SET ord_date='15-JAN-10'
WHERE ord_amount-advance_amount<
(SELECT MIN(ord_amount) FROM orders);

Output:

To see more details of subqueries using UPDATE statement click here.

Subqueries with DELETE statement

DELETE statement can be used with subqueries. Here are the syntax and an example of subqueries using DELETE statement.

Syntax:

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME    
FROM TABLE_NAME)    
[ WHERE) ]

If we want to delete those orders from 'neworder' table which advance_amount are less than the maximum advance_amount of 'orders' table, the following SQL can be used:

Sample table: neworder

SQL Code:

DELETE FROM neworder
WHERE advance_amount<
(SELECT MAX(advance_amount) FROM orders);

Output:

To see more details of subqueries using DELETE statement click here.

What Next?

• SQL Subqueries - Slide Presentation
• Single Row Subqueries
• Multiple Row and Column Subqueries
• Correlated subqueries using aliases
• Nested subqueries

Note : Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Which of the following operators Cannot be used in a sub query?

Which of the following operator when applied to a sub query will return greater than the minimum value in the list?

Can subqueries be used with the comparison operators?