1. What is a non-correlated
subquery?
A non-correlated subquery is a subquery that does not depend on any columns
from the outer query. It can be executed independently of the outer query and
returns a result used by the outer query. It is evaluated only once, unlike
correlated subqueries, which are evaluated once for each row in the outer
query.
2. How is a non-correlated subquery different from a correlated subquery?
- Non-Correlated Subquery: Independent of the outer query. It is evaluated once and can be executed separately.
- Correlated Subquery: Dependent on the outer query. It references columns from the outer query and is executed for each row processed by the outer query.
3. Where are non-correlated
subqueries used in SQL?
Non-correlated subqueries are commonly used in the WHERE, HAVING, or FROM
clauses to filter data or perform comparisons. They can also be used in SELECT
clauses to compute values based on a subquery.
4. Can a non-correlated subquery
return multiple rows?
Yes, a non-correlated subquery can return multiple rows, especially when used
with operators like IN, ANY, or ALL. However, if you're using operators like =,
the subquery must return only a single value.
Example (with IN):
SELECT employee_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1400
);
5. Can a non-correlated subquery
return multiple columns?
Yes, a non-correlated subquery can return multiple columns. In this case, the
outer query must compare these multiple columns, typically using tuple-based
comparisons like IN or = (column1, column2).
Example (with multiple columns):
SELECT e.employee_name
FROM employees e
WHERE (e.salary, e.department_id) IN (
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id
);
6. How do I use a non-correlated
subquery in the WHERE clause?
A non-correlated subquery can be used in the WHERE clause to compare a column
to a value (scalar subquery) or a set of values (multi-row subquery).
Example:
SELECT employee_name
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
7. What are some common operators used with non-correlated subqueries?
- =: Used when the subquery returns a single value.
- IN: Used when the subquery returns a set of values.
- ANY or SOME: Compares a value to any value in a set returned by the subquery.
- ALL: Compares a value to all values in a set returned by the subquery.
Example with IN:
SELECT employee_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1400
);
8. Can a non-correlated subquery be
used with aggregate functions?
Yes, non-correlated subqueries can be used with aggregate functions like AVG,
MAX, SUM, and MIN.
Example:
SELECT employee_name, salary
FROM employees
WHERE salary > (
SELECT MAX(salary)
FROM employees
WHERE department_id = 10
);
9. What is the performance impact of
using a non-correlated subquery?
Non-correlated subqueries are generally more efficient than correlated
subqueries because they are evaluated only once and do not need to be executed
for each row of the outer query. However, if the subquery involves large
tables, proper indexing is necessary to ensure good performance.
10. Can a non-correlated subquery be
used in the SELECT clause?
Yes, non-correlated subqueries can be used in the SELECT clause to compute
values based on the results of the subquery.
Example:
SELECT employee_name, (
SELECT MAX(salary)
FROM employees
WHERE department_id = e.department_id
) AS department_max_salary
FROM employees e;
11. How do non-correlated subqueries
improve query readability?
Non-correlated subqueries can make complex queries more readable by allowing
you to isolate complex logic into a self-contained query. Instead of having to
join multiple tables or perform complicated conditions in the outer query, you
can use a subquery to cleanly encapsulate the logic.
12. Can I use a non-correlated
subquery in a JOIN clause?
Yes, non-correlated subqueries can be used in a JOIN clause, although this is
less common than using them in WHERE or HAVING. A non-correlated subquery in
the FROM clause essentially turns the subquery into a derived table.
Example:
SELECT e.employee_name, d.department_name
FROM employees e
JOIN (
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
) d ON e.department_id = d.department_id
WHERE e.salary > d.max_salary;
13. What happens if a non-correlated
subquery returns no rows?
If a non-correlated subquery returns no rows, the outer query will behave
differently depending on the operator used:
- For =, IN, ANY, or ALL, the outer query will return no results, as there is no matching value from the subquery.
- For EXISTS, the outer query will return all rows, as EXISTS only cares about the existence of rows.
14. What are the advantages of using a non-correlated subquery over a join?
- Simplicity: Non-correlated subqueries are often easier to read and write for filtering conditions, as they can isolate specific logic in a compact form.
- Avoiding Duplicate Rows: If you need to filter based on an aggregated value (e.g., maximum salary per department), using a subquery avoids the need for grouping and aggregation in the outer query.
However, in cases where performance is critical and the subquery could be converted into a JOIN, JOINs are generally more efficient, especially for large datasets.
15. Can a non-correlated subquery
return a null value?
Yes, a non-correlated subquery can return a NULL value. If the subquery returns
NULL, it will be compared as NULL in the outer query. In cases where NULL is
involved, you might need to handle it explicitly with IS NULL or COALESCE().
Example:
SELECT employee_name
FROM employees
WHERE salary > (
SELECT MAX(salary)
FROM employees
WHERE department_id = 20
AND salary IS NOT NULL
);
16. Are there any limitations to using non-correlated subqueries?
- Non-correlated subqueries can only be used in specific places like WHERE, HAVING, and SELECT. They cannot replace JOIN operations in some complex queries.
- They may not always be the most efficient choice for very large datasets. Depending on the complexity, JOINs might outperform subqueries.
No comments:
Post a Comment