SQL – Subqueries

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

sql-table1

Table2:

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

sql-table59

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:

sql-table60

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





Share this:
Facebook Twitter Linkedin