QUERY

TYPES OF SUBQUERIES IN SQL

ON BASIS OF RESULT RETURN-

1. Scalar subquery- A scalar subquery is a type of subquery that returns exactly one value. This value can be used as a single value expression within a larger SQL statement. Scalar subqueries are commonly used in situations where you need to retrieve a single value from a subquery and use it in comparisons, calculations, or as part of another query.

Ex- Suppose you have two tables: employees and departments. You want to retrieve the names of all departments along with the total number of employees in each department.

SQL QUERY-

SELECT department_name, (SELECT COUNT(*) FROM employees
WHERE department_id =departments.department_id) AS total_employees
FROM departments;

2. Row subquery- A row subquery, also known as a row-valued subquery or a table subquery, is a type of subquery that returns a set of rows. Unlike scalar subqueries, which return a single value, row subqueries return multiple rows. Row subqueries can be used in various contexts within a SQL statement where a table or set of values is expected.

SQL QUERY-

SELECT * FROM employees WHERE department_id
IN (SELECT department_id FROM departments WHERE location = 'New York')

3. Table subquery- A table subquery, also known as a derived table or inline view, is a subquery that appears in the FROM clause of a SQL statement and behaves like a temporary table within the query. It allows you to treat the result of the subquery as if it were an actual table, enabling you to perform further operations on it such as joining with other tables, filtering rows, or aggregating data.

SQL QUERY-

SELECT o.order_id, o.order_date, o.customer_id, c.customer_name, c.city
FROM orders o INNER JOIN (SELECT customer_id, customer_name, city
                           FROM customers WHERE city = 'New York')
 ASC ON o.customer_id = c.customer_id;

ON BASIS OF WORKING-

1. Independent subquery- An independent subquery, also known as a uncorrelated subquery, is a type of subquery that can be executed independently of the outer query. In other words, the subquery can be evaluated once and its result can be used in multiple contexts or executed on its own without reference to the outer query.

SQL QUERY-

SELECT * FROM employees WHERE department_id IN
(SELECT department_id FROM
(SELECT department_id, COUNT(*) AS num_employees FROM employees
 GROUP BY department_id ORDER BY num_employees DESC LIMIT 1));

2. Correlated subquery- A correlated subquery, also known as a correlated nested query, is a type of subquery in SQL that references one or more columns from the outer query. Unlike independent subqueries, which can be evaluated independently of the outer query, a correlated subquery depends on the outer query for its execution. Each time the outer query is executed, the correlated subquery is re-evaluated using the current row of the outer query.

SQL QUERY-

SELECT employee_id, first_name, last_name, salary FROM employees e
 WHERE salary < (SELECT AVG(salary) FROM employees
 WHERE department_id = e.department_id);