MySql - Subqueries in SELECT, INSERT, UPDATE, and DELETE

Subqueries in MySQL

Key Characteristics of Subqueries

• Nested Structure: A subquery is executed within the context of an outer query.
• Parentheses: Subqueries must always be enclosed in parentheses ().
• Comparison Operators: Subqueries can be used with operators like =, >, <, IN, NOT IN, EXISTS, etc.
• Single-Row vs. Multi-Row Subqueries: Subqueries may return a single value or multiple values, affecting how they can be used in the outer query.

Subqueries in SELECT Statements

Subqueries are commonly used in SELECT statements to filter data based on the results of another query.

Example: Employees in USA Offices

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
                       officeCode
                   FROM
                       offices
                   WHERE
                       country = 'USA');

In this example, the subquery retrieves the office codes for offices located in the USA. The outer query then selects employees who work in those offices.

Subqueries in INSERT Statements

Subqueries can be used in INSERT statements to insert data retrieved from another table.

Example: Copying Data to a Backup Table

INSERT INTO products_bkp (product_id, product_name, price)
SELECT product_id, product_name, price
FROM products
WHERE category = 'Electronics';

This query copies all electronic products from the products table into the products_bkp backup table.

Subqueries in UPDATE Statements

Subqueries can be used in UPDATE statements to update records based on data from another table.

Example: Updating Prices Based on Average

UPDATE products
SET price = price * 1.1
WHERE price < (SELECT AVG(price) FROM products);

This query increases the price by 10% for all products priced below the average price.

Subqueries in DELETE Statements

Subqueries can be used in DELETE statements to delete records based on conditions derived from another query.

Example: Deleting Inactive Customers

DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURDATE() - INTERVAL 1 YEAR
);

This query deletes customers who have not placed any orders in the past year.

Types of Subqueries

• Scalar Subquery: Returns a single value.
• Row Subquery: Returns a single row with multiple columns.
• Table Subquery: Returns multiple rows and columns.
• Correlated Subquery: References columns from the outer query and is evaluated once for each row processed by the outer query.

Example of a Correlated Subquery

SELECT e1.employee_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

This query selects employees whose salary is above the average salary of their respective departments.

Best Practices

• Ensure subqueries are optimized for performance, especially correlated subqueries.
• Use indexes on columns involved in subquery conditions to improve efficiency.
• Consider rewriting subqueries as joins when appropriate for better performance.

Subqueries are powerful tools in MySQL that allow for complex and dynamic data operations. By understanding how to effectively use subqueries in SELECT, INSERT, UPDATE, and DELETE statements, you can write more efficient and maintainable SQL queries.