SQL Tutorial

🔍 Subqueries in SQL

A Subquery is a query nested inside another query. It's used to retrieve data that will be used in the main query. Subqueries can be placed in SELECT, INSERT, UPDATE, or DELETE statements and can help in filtering or creating dynamic queries.

🔑 Types of Subqueries

There are different types of subqueries based on where and how they are used:

• Single-row Subquery: Returns only one row and one column.
• Multi-row Subquery: Returns more than one row.
• Correlated Subquery: Uses values from the outer query.

💡 Let's break down each one with examples!

🔍 Single-row Subquery

A Single-row subquery is used when a subquery returns only one value. It’s typically used with comparison operators like =, >, <, etc.

SELECT employee_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

This query retrieves the name of the employee who has the highest salary from the employees table.

🔍 Multi-row Subquery

A Multi-row subquery returns more than one value. It is typically used with operators like IN, NOT IN, ANY, or ALL.

SELECT employee_name
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Sales');

Here, the subquery returns the salaries of employees in the "Sales" department. The outer query then checks for employees with a salary greater than any of those values.

🔍 Correlated Subquery

A Correlated subquery is one where the subquery refers to a column in the outer query. It’s evaluated once for each row processed by the outer query.

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

In this example, the subquery is correlated with the outer query because it refers to the department column of the outer query's current row. It calculates the average salary for each department and compares it with the employee's salary.

🔧 Subqueries in SELECT Clause

A subquery can also be used in the SELECT clause to return calculated results for each row.

SELECT employee_name, department, 
(SELECT MAX(salary) FROM employees e2 WHERE e2.department = e1.department) AS highest_salary
FROM employees e1;

This query will return the employee names, their departments, and the highest salary in each department.

⚙️ Subqueries with EXISTS

You can use the EXISTS operator in a subquery to check whether a subquery returns any rows.

SELECT employee_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM sales s WHERE s.employee_id = e.employee_id);

In this case, the outer query retrieves employees who have made at least one sale, based on whether any rows are returned by the subquery.

📝 Key Takeaways

• Single-row Subquery: Returns a single value, often used with comparison operators like =, >, <.
• Multi-row Subquery: Returns multiple values, often used with IN or ANY operators.
• Correlated Subquery: References a column in the outer query and is evaluated for each row.
• EXISTS: Used to check if a subquery returns any rows, often with conditional queries.