How to write subquery in SQL?

Question

Can anyone tell me how to write subquery in SQL?

Answer 1

The subquery is a normal query nested inside another query. Here is the basic syntax to use subquery with IN operator:

SELECT column_names

FROM table_name1

WHERE value IN (SELECT column_name

                               FROM table_name2

                               WHERE condition)

For example, if you want to list the product details with orders quantities greater than 100.

SELECT Product_Name

FROM Product_table

 WHERE Id IN (SELECT Product_Id

                          FROM OrderItem_table

                          WHERE Quantity > 100)

You can check out this SQL Tutorial blog learns subqueries and writing complex sub-queries.