Prev     Next

• Subquery is also called as nested query or inner query in SQL. Subquery is a query within the query.
• Always, inner most subqueries will be executed at the beginning, and then, this query output will be given as input to its immediate main query and so on.
• Subquery should always have/return single column only. And, each subquery should be enclosed with parenthesis.
• Subquery is also a normal query which can be used with SELECT, UPDATE, INSERT and DELETE SQL statements and any conditions can be applied in WHERE clause.
• Subquery can use same table or different tables to fetch the data. And, even each condition in WHERE clause could have a subquery. But, please note that whenever you use a subquery, overall performance will get reduced. So, you also can use SQL joins wherever possible. Syntax for SQL subquery is given below.

SQL Syntax for subquery:

SQL Syntax for subquery with SELECT statement	SELECT column_name1, column_name2, etc FROM table_name1,table_name2, etc WHERE column_name1 OPERATOR ( SELECT column_name1, column_name2, etc FROM table_name1,table_name2, etc [WHERE condition] ) AND column_name2 OPERATOR ( SELECT column_name1, column_name2, etc FROM table_name1,table_name2, etc [WHERE condition] );etc…
SQL Syntax for subquery with INSERT statement	INSERT INTO table_name column_name1, column_name2, etc ( SELECT column_name1, column_name2, etc FROM table_name1,table_name2, etc [WHERE condition] );
SQL Syntax for subquery with UPDATE statement	UPDATE table_name SET column_name = new_value WHERE column_name OPERATOR ( SELECT column_name1, column_name2, etc FROM table_name1,table_name2, etc [WHERE condition] );
SQL Syntax for subquery with DELETE statement	DELETE FROM table_name WHERE column_name OPERATOR ( SELECT column_name1, column_name2, etc FROM table_name1,table_name2, etc [WHERE condition] );

Example for how to use SQL subquery with SELECT statement:

Please consider following 2 tables with few records as given below.

Table1:

Table name (for example): student1
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows

Table2:

Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows

SQL query:

SELECT Student_name, City, Age
FROM student1
WHERE student_ID = (SELECT student_ID FROM student2 WHERE Rank=1);

Description:

In above query, then inner query ‘SELECT student_ID FROM student2 WHERE Rank=1’ is the subquery which is executed at first, and then, this output ‘student_ID’ is given as input to the main query. Then, the main query is executed as a normal query and returns the result.

SQL query Output:

Note: For all remaining statements like INSERT, UPDATE and DELETE, you can follow the same as given in above syntax to perform these operations on tables.

Prev     Next