SUBQUERIES

Title: Subqueries

A subquery, also known as a nested query or inner query, is a query nested within another SQL query. Subqueries are enclosed within parentheses and can be used in various parts of a SQL statement, such as the WHERE clause, FROM clause, HAVING clause, or even the SELECT list. Here's an overview of subqueries in SQL:

1. Purpose of Subqueries:

   • Subqueries are used to retrieve data based on conditions evaluated dynamically at runtime.

   • They allow you to perform operations on the results of another query.

   • Subqueries can simplify complex queries and provide a more concise and readable syntax.

2. Types of Subqueries:

   • Single-Row Subquery: Returns only one row of results and can be used with single-row operators such as =, <, >, etc.

   • Multi-Row Subquery: Returns multiple rows of results and can be used with multi-row operators such as IN, ANY, ALL, etc.

   • Nested Subquery: A subquery nested within another subquery.

3. Syntax:

   • Subqueries are enclosed within parentheses and placed within the WHERE clause, FROM clause, HAVING clause, or SELECT list of the outer query.

   • The subquery is executed first, and its result set is then used by the outer query.

   • Example of a subquery in the WHERE clause:

SELECT column1

FROM table1

WHERE column2 = (SELECT column3 FROM table2 WHERE condition);

4. Subquery Guidelines:

   • Subqueries should be as efficient as possible to avoid performance issues.

   • Use subqueries only when necessary; sometimes, equivalent results can be achieved using joins.

   • Ensure that the subquery returns the expected result set and data types.

5. Examples:

   • Single-Row Subquery:

SELECT employee_name

FROM employees

WHERE employee_id = (SELECT manager_id FROM employees WHERE employee_name = 'John');

5. • Multi-Row Subquery:

SELECT product_name

FROM products

WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

5. • Correlated Subquery:

SELECT employee_name

FROM employees e

WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

6. Correlated Subqueries:

   • A correlated subquery refers to a subquery that depends on the outer query's values.

   • In correlated subqueries, the inner query is executed once for each row processed by the outer query.

Subqueries are a powerful feature of SQL that allow for dynamic and flexible querying of data. By using subqueries, you can write complex queries to retrieve specific data based on various conditions and criteria. However, it's essential to use them judiciously and consider their performance implications.