Menu
MySQL

Using Subqueries in Statements

Subqueries can be used in SQL to perform more complex queries in the context of SELECT, INSERT, UPDATE, and DELETE statements. Below, I’ll explain how subqueries are used in each of these operations.

 

 

1. Using Subqueries in SELECT Statements

Subqueries in SELECT statements allow you to retrieve data based on results from another query. They are often used for filtering, calculating, or joining data.

Example 1: SELECT with a Subquery

You can use a subquery to retrieve data and compare it to the data in the main query.

SELECT FirstName, LastName
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Here, the subquery (SELECT AVG(Salary) FROM Employees) calculates the average salary of all employees, and the outer query returns employees whose salary is higher than the average.

 

 

2. Using Subqueries in INSERT Statements

Subqueries in INSERT allow you to insert data into a table based on the result of another query. The subquery can select rows from one or more tables and insert them into another.

Example: INSERT with a Subquery

Suppose we have an Employees_Backup table, and we want to insert the details of employees who have a salary greater than the average salary into this table.

INSERT INTO Employees_Backup (EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Here, the subquery selects the employees whose salary is greater than the average salary, and those records are inserted into the Employees_Backup table.

 

 

3. Using Subqueries in UPDATE Statements

Subqueries can also be used in UPDATE statements to modify data in a table based on a calculation or condition from another query.

Example: UPDATE with a Subquery

Let’s say we want to give employees who earn less than the average salary a 10% raise.UPDATE Employees


UPDATE Employees
SET Salary = Salary * 1.10
WHERE Salary < (SELECT AVG(Salary) FROM Employees);

In this query, the subquery (SELECT AVG(Salary) FROM Employees) calculates the average salary, and the UPDATE statement increases the salary of all employees whose salary is less than the average by 10%.

 

 

4. Using Subqueries in DELETE Statements

You can use subqueries in DELETE statements to remove data based on the result of another query.

Example: DELETE with a Subquery

Suppose we want to delete all employees from the Employees table who work in a department located in 'New York'. We can use a subquery to find those departments.

DELETE FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

Here, the subquery selects the DepartmentID of departments in New York, and the outer query deletes all employees who work in those departments.

 

 

 

MySQL