Subqueries in WHERE

There are times when subqueries are used in the WHERE clause. Both correlated and uncorrelated subqueries and aggregate and non-aggregate subqueries may be used in the WHERE clause.

Uncorrelated subqueries

simple subquery

Below query finds all order lines whose sales price is more than the unit price of product 1.

aggregate subquery

Below query finds all order lines whose sales price is less than or equal to the avg unit price of all the products.

IN subquery

Correlated Sub-queries

simple sub-query

Below query finds all order lines whose sales price is more than the unit price of the product.

joins and aggregates in sub-query

Below query finds all order lines whose sales price is more than the average unit price of all the products within the same category as the product of the order line.

Subqueries in WHERE

Uncorrelated subqueries​

simple subquery​

aggregate subquery​

IN subquery​

Correlated Sub-queries​

simple sub-query​

joins and aggregates in sub-query​

IN subquery​

Uncorrelated subqueries

simple subquery

aggregate subquery

IN subquery

Correlated Sub-queries

simple sub-query

joins and aggregates in sub-query

IN subquery