Subqueries Cheat Sheet Views and Subqueries SQL

 

Subqueries

We can write nested queries, i.e., a query inside another query. Let's understand the scenarios where subqueries can be used with the following database.

Database: The database stores the sample data of an e-commerce aplication. Here, the database consists of

user

,

order_details

and

product

tables that store the information of products, orders placed, and the products on the platform.

Refer the tables in the code playground for a better understanding of the database.

Examples

Example 1:

Get the rating variance of products in the "WATCH" category. Rating variance is the difference between average rating and rating of a product.

Here, we need to write an expression to subtract rating of each product from the average rating as following.

1

2

3

SELECT name,

(average_rating - rating) AS rating_variance

...

SQL

Replace average_rating with a query which computes the average.

1

2

3

4

5

6

7

8

9

10

SELECT

name,

(

SELECT AVG(rating)

FROM product

WHERE category = "WATCH"

) - rating AS rating_variance

FROM product

WHERE category = "WATCH";

SQL

Output

name	rating_variance
Analog-Digital	-0.766666666666667
Fastfit Watch	-0.366666666666666
Fastrack M01	0.333333333333334
...	...

Example 2:

Fetch all the products whose ratings is greater than average rating of all products.

Output

1

2

3

4

5

6

7

SELECT *

FROM product

WHERE rating > (

SELECT AVG(rating)

FROM product

);

SQL

Expected Output

product_id	name	price_per_unit	rating	category	brand
202	Biotique Almond Soap	34	4.5	SOAP	BIPTIQUE
203	Boat Stone Speaker	1999	4.3	SPEAKER	BOAT
...	...	...	...	...	...

Example 3:

Fetch all the order_ids in which order consists of mobile (product_ids : 291, 292, 293, 294, 296) and not ear phones (product_ids : 227, 228, 229, 232, 233).

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

SELECT

order_id

FROM

order_details

WHERE

order_id IN (

SELECT

order_id

FROM

order_product

WHERE

product_id IN (291, 292, 293, 294, 296)

)

AND NOT order_id IN (

SELECT

order_id

FROM

order_product

WHERE

product_id IN (227, 228, 229, 232, 233)

);

SQL

Collapse

Output

order_id
801
802
806
807

Possible Mistakes

In SELECT Clause

A subquery in the SELECT clause can have only one column.

Query

1

2

3

4

5

6

7

SELECT name, (

SELECT AVG(rating), MAX(rating)

FROM product

WHERE category = "WATCH"

) - rating AS rating_variance

FROM product

WHERE category = "WATCH";

SQL

Output

1

2

Error:

sub-select returns 2 columns - expected 1

SQL

In WHERE Clause

Query

In WHERE clause, a subquery can have only one column.

1

2

3

4

5

6

7

SELECT

order_id, total_amount

FROM order_details

WHERE total_amount > (

SELECT total_amount, order_id

FROM order_details

);

SQL

Output

1

Error: Row value misused

SQL

Try it Yourself!

Question 1

Get the rating variance of products in the "MOBILE" category. Rating variance is the difference between average rating and rating of a product. Rating variance is the difference between average rating and rating of a product

Expected Output Format:

name	rating_variance
Oneplus 8 Pro	-0.040000000000000924
Oneplus 8t Pro	0.2599999999999989
...	...

Question 2

Get all the products from the "MOBILE" category, where rating is greater than average rating.

Expected Output Format:

name	rating
Oneplus 8 Pro	4.5
Mi 10T	4.5
Samsung S21 Ultra	4.7
...	...