Subqueries are a fundamental component of SQL that let you execute intricate queries within of more comprehensive queries. They are an effective tool in SQL for carrying out intricate data analysis and manipulation. There are three primary categories of subqueries: scalar subqueries, correlated subqueries, and EXISTS/NOT EXISTS subqueries.

Correlated Subqueries
Correlated subqueries are subqueries that reference a column from the outer query. This makes them dependent on the outer query's results, meaning the subquery is executed once for each row of the outer query.

Example. Finding employees whose salary is greater than the average salary of their department.

SELECT employee_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees AS dept_avg
  WHERE dept_avg.department = employees.department
);

In this example, the subquery calculates the average salary for each department, and the outer query compares each employee's salary against that average. The subquery depends on the DepartmentID from the outer query, making it correlated.

When to Use?
When you need to compare each row in a table against a calculated value from related data.
For filtering data based on conditions that vary with each row.

Scalar Subqueries
A scalar subquery returns a single value, making it ideal for use in expressions where a single value is required. Scalar subqueries can be used in SELECT, WHERE, and ORDER BY clauses, among others.

Example. Finding the average salary in the company.
SELECT employee.name, (SELECT AVG(salary) FROM employee) AS average_salary
FROM employee;

Here, the subquery calculates the average salary for all employees, and the result is assigned to the average_salary column in the outer query.

When to Use?
When you need to include a single value in your main query, such as a count, average, or specific calculation.
For calculating values dynamically based on related data.

EXISTS and NOT EXISTS

EXISTS and NOT EXISTS subqueries are used to check for the existence or non-existence of rows in a subquery. These operators return a boolean value (TRUE or FALSE), making them useful for conditional queries.

Example. Finding departments that have employees earning more than 100,000.
SELECT department
FROM departments
WHERE EXISTS (
  SELECT 1
  FROM employees
  WHERE employees.department = departments.department
    AND employees.salary > 100000
);

In this query, the outer query returns departments. The subquery checks if there are any employees for that department who have a salary greater than 100,000.

When to Use?
Use EXISTS when you need to check if a related record exists.
Use NOT EXISTS to filter out records that have a related record.

Conclusion

Subqueries are a fundamental tool in SQL for performing complex data manipulations and retrieving specific subsets of data. By understanding the different types of subqueries, you can effectively leverage them to solve a wide range of data-related problems.

HostForLIFEASP.NET SQL Server 2022 Hosting