What are single row and multiple row subqueries?

Introduction

Queries inside another SQL query are called subqueries. For various SQL tasks, they assist us in focusing on certain rows. They're utilized in SQL to SELECT, UPDATE, INSERT, and DELETE records. Single-row, multiple-row, multiple-column, correlated, and nested subqueries are among the several kinds of SQL subqueries. According to the user's requirements, every type performs specific roles and employs the outcomes.

Sub Queries

Queries that are nested inside another SQL query are called subqueries. There can be many queries, one after the other, contained inside a subquery. When certain requirements are met during execution, we can pick particular rows. They can also be referred to as the inner query or inner select, while the outer query or outer select is the query that includes it.

Like SELECT, INSERT, UPDATE, DELETE, or any other subquery, subqueries are inside statements.

Subqueries can be found in the PARENT SQL query's WHERE, FROM, or HAVING clauses. They are employed with operators for comparing and reasoning, such as >, \, >=, \>, \=, SOME, ANY, ALL, and IN.

They finish the expression by running ahead of the outer query at run time and passing the outcome. Subqueries determine whether any row is picked by comparing an expression to the output.

A WHERE clause may contain up to 255 subquery levels. When using nested subqueries, the FROM clause is unrestricted. We come across a maximum of five subqueries in the real world. For this reason, 255 cannot be used as a limit.

Syntax:

( SELECT [DISTINCT] subquery_select_statement

  FROM {table_name | view_name}

               {table_name | view_name} ...

  [WHERE condition]

  [GROUP BY column_name [,column_name ] ...]

  [HAVING condition] )

Types of Subqueries

Let's imagine we have given tables in our database as below:

Table name: Art_galleries

create table Art_galleries(id int ,state varchar(20));   

insert into Art_galleries values(1,'Telangana');

insert into Art_galleries values(2,'Kerala');

insert into Art_galleries values(3,'Tamil Nadu');

Table name: Arts

create table Arts(id int ,name varchar(20),gallery_id int,cost int);

insert into Arts(1,'Nature',2,6000);

insert into Arts values(2,'Music',1,7000);

insert into Arts values(3,'House',1,4000);

insert into Arts values(4,'Lady',3,8000);

insert into Arts values(5,'Love',2,6500);

Table name: sellers

create table sellers(id int ,last_name varchar(20),first_name varchar(20),gallery_id int,selling_fee int);

insert into sellers values(1,'Nigh','Kala',2,1900);

insert into sellers values(2,'Latha','Nivda',3,2100);

insert into sellers values(3,'Krithi','Leven',2,1500);

insert into sellers values(4,'Naina','Tenin',1,4500);

insert into sellers values(5,'Shrada','Das',3,4300);

Table name: managers

create table Managers(id int ,gallery_id varchar(20));

insert into managers values(1,2);

insert into managers values(2,3);

insert into managers values(3,1);

Single Row Subquery

Single-row subqueries give a single row to be returned to the parent query. Single-row subqueries are utilized with a FROM condition and a comparison operator, a WHERE phrase, a HAVING phrase, or a SQL SELECT statement. The SELECT statement employs single-row subqueries. Use an example to better understand it.

Query:

select name AS Art,cost,(select AVG(cost) from Arts) AS avg_cost from Arts;

Output:

Note: When the inner query executes independently of the outer query, it yields a significant outcome.

To further filter the output of the outer query, single-row subqueries can be used with the WHERE clause in the SELECT statement. Now, let's look at an example.

Query:

select * from sellers where selling_fee>(select AVG(selling_fee) from sellers);

The SQL mentioned above query displays the seller records whose selling fee exceeds the total fee average. The statement's subquery determines the average selling fee, which is 2860. After that, the parent statement filters out the data

related to the sellers with selling fees greater than regular using the returned value. The outcome appears in the following table:

Output:

Multiple Row Subquery

Multiple row subqueries give multiple rows return to the parent query. An SQL SELECT statement that includes a FROM clause, a WHERE clause, and a HAVING clause, and the logical operators (ALL, IN, NOT IN, and ANY) can take advantage of multiple-row subqueries. Let's examine it with an illustration.

Query:

select AVG(selling_fee) from sellers where id not in (select id from

managers);

The average seller charge for each seller who is not a manager is determined using the SQL above query. The subquery returns the list of all managers' IDs. Subsequently, the outside query refines the dataset to identify the information of sellers who are not managers and computes the average seller fee for such sellers. It gives back the selling fee as a single average amount. The result appears as follows:

Output: