Embedded Queries in SQL

An embedded query in SQL is a query that is included within another SQL statement. The embedded query can be a subquery, a correlated subquery, or a derived table. Embedded queries are commonly used to retrieve data from one or more tables based on specific criteria or conditions.

Here's an example of an embedded query that uses a subquery to retrieve data from two tables:

SELECT *
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate BETWEEN '2022-01-01' AND '2022-12-31');
 

In this example, the embedded query is a subquery (SELECT CustomerID FROM Orders WHERE OrderDate BETWEEN '2022-01-01' AND '2022-12-31'), which retrieves all customer IDs who have placed an order between January 1, 2022, and December 31, 2022. This subquery is then embedded within the main query's WHERE clause, which retrieves all customer data where the CustomerID is in the subquery's result set.

Another type of embedded query is a derived table, which is a query that is used to create a temporary table within another SQL statement. Here's an example:

SELECT *
FROM (
  SELECT CustomerID, SUM(OrderAmount) as TotalAmount
  FROM Orders
  GROUP BY CustomerID
) as OrderTotals
WHERE TotalAmount > 1000;
 

In this example, the embedded query is a derived table (SELECT CustomerID, SUM(OrderAmount) as TotalAmount FROM Orders GROUP BY CustomerID) as OrderTotals, which calculates the total order amount for each customer by using the SUM function and the GROUP BY clause. This derived table is then used in the main query's FROM clause, where it is given an alias OrderTotals. The main query then retrieves all customer data where the total order amount is greater than 1000.

 Finally, embedded queries are a powerful tool in SQL that allow for complex data retrieval and manipulation. They can be used to combine data from multiple tables, calculate aggregate values, and filter data based on specific criteria.