Subquery Statement

Question: How is the SQL Subquery Statement used as a filtering mechanism to limit the overall results from a query?
A SQL subquery, also known as a nested query or inner query, is a query embedded within another SQL query. The subquery is used to retrieve data that will be used in the main query as a condition to further restrict the data that is being fetched. Subqueries can be used in various parts of a query, including the SELECT, FROM, WHERE, and HAVING clauses.
In the context of filtering results, subqueries are commonly used in the WHERE clause. They allow you to filter the main query's results based on complex conditions that can't be defined using standard SQL operators alone.
Consider a scenario where you have a "Sales" table that records the sales of various products in a store. Each row in the table represents a transaction, with columns for transaction_id, product_id, sales_date, and sales_amount. Suppose you want to find all transactions where the sales amount is above the average.
The SQL statement with a subquery would look something like this:

SELECT transaction_id, product_id, sales_amount 
FROM Sales 
WHERE sales_amount > (SELECT AVG(sales_amount) FROM Sales);

In this case, the subquery (SELECT AVG(sales_amount) FROM Sales) calculates the average sales amount from the "Sales" table. This subquery is run first and the result is used by the main query as a filter. The main query then returns all transactions from the "Sales" table where the sales amount is greater than this average value. Here, the subquery acts as a dynamic filter that is evaluated at runtime. It allows for more flexible conditions, as the filter value doesn't need to be known in advance and can be calculated based on the data itself.
In essence, SQL subqueries provide a powerful mechanism to enhance the precision of your data retrieval, permitting intricate relationships to be defined between tables and offering more flexibility in constructing complex queries.

Often, you need to limit a SELECT statement to values that relate to another table, but a join is not necessarily the answer. In these cases, the subquery comes into play. You can use a SELECT statement as the filtering mechanism to limit the overall results from a query.
For example, from the PUBS database, you can say, "Give me all the titles for publishers who are located in CA."
The subquery in the query below limits the publisher IDs to only those who live in the state of California:

SELECT Title
FROM Titles
WHERE Pub_ID
IN (SELECT Pub_ID
FROM Publishers
WHERE State= 'CA')    

The result is a list of titles, but only those published by California publishers.

Subqueries are regular queries placed inside parenthesis. Subqueries can be used in different ways and at different locations inside a query: Here is an subquery with the IN operator

SELECT columnNames
  FROM tableName1
 WHERE value IN 
(SELECT columnName
   FROM tableName2 
   WHERE condition)

SELECT column1 = (SELECT columnName 
FROM tableName WHERE condition),
 columnNames
 FROM tableName
 WEHRE condition