Subqueries, also known as inner queries or nested queries, are a powerful feature in SQL that allow you to perform more complex and flexible queries.
- Schema
CREATE TABLE Department (
Department_id INT PRIMARY KEY,
Department_name VARCHAR(50) NOT NULL
);
CREATE TABLE Employee (
Employee_id INT PRIMARY KEY,
Employee_name VARCHAR(50) NOT NULL,
Salary DECIMAL(10,2) NOT NULL,
Department_id INT,
FOREIGN KEY (Department_id) REFERENCES Department(Department_id)
);
INSERT INTO Department (Department_id, Department_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Marketing'),
(4, 'Sales'),
(5, 'Finance');
INSERT INTO Employee (Employee_id, Employee_name, Salary, Department_id) VALUES
(1, 'Alice', 5000, 1),
(2, 'Bob', 7000, 1),
(3, 'Carol', 6000, 2),
(4, 'Dave', 6000, 2),
(5, 'Eve', 8000, 3),
(6, 'Frank', 9000, 3),
(7, 'Grace', 3000, 4),
(8, 'Hank', 4000, 4),
(9, 'Irene', 10000, 5),
(10, 'Jack', 9500, 5);
- Subqueries in the SELECT Clause Subqueries can be used in the SELECT clause to return a single value that will be included in the result set.
Example:
SELECT
Employee_id,
Employee_name,
(SELECT Department_name FROM Department WHERE Department_id = e.Department_id) AS DepartmentName
FROM
Employee e;
- Subqueries in the FROM Clause Subqueries can create a temporary table that can be joined with other tables in the FROM clause.
Example:
SELECT
e.Employee_id,
e.Employee_name,
dept.Department_name
FROM
Employee e
INNER JOIN
(SELECT Department_id, Department_name FROM Department) dept
ON
e.Department_id = dept.Department_id;
- Subqueries in the WHERE Clause Subqueries can filter rows based on the result of another query.
Example:
SELECT
Employee_id,
Employee_name,
Salary
FROM
Employee e
WHERE
Salary = (SELECT MAX(Salary) FROM Employee WHERE Department_id = e.Department_id);
- Subqueries in the HAVING Clause Subqueries can filter groups based on aggregate functions in the HAVING clause.
Example:
SELECT
Department_id,
AVG(Salary) AS AvgSalary
FROM
Employee
GROUP BY
Department_id
HAVING
AVG(Salary) > (SELECT AVG(Salary) FROM Employee);
- Subqueries in the JOIN Condition Subqueries can be part of the join condition to dynamically determine the join criteria.
Example:
SELECT
e.Employee_id,
e.Employee_name,
d.Department_name
FROM
Employee e
INNER JOIN
Department d ON e.Department_id = d.Department_id
AND
e.Salary > (SELECT AVG(Salary) FROM Employee WHERE Department_id = e.Department_id);
- Subqueries in the INSERT Statement Subqueries can provide the values to insert into a table.
Example:
INSERT INTO
Employee (Employee_id, Employee_name, Salary, Department_id)
SELECT
new_employee_id,
new_employee_name,
new_salary,
new_department_id
FROM
(SELECT
11 AS new_employee_id,
'John Doe' AS new_employee_name,
5000 AS new_salary,
1 AS new_department_id
) new_employee;
- Subqueries in the UPDATE Statement Subqueries can determine the values to update in a table.
Example:
UPDATE
Employee
SET
Salary = (SELECT AVG(Salary) FROM Employee WHERE Department_id = Employee.Department_id)
WHERE
Employee_id = 1;
- Subqueries in the DELETE Statement Subqueries can determine which rows to delete from a table.
Example:
DELETE FROM
Employee
WHERE
Department_id IN (SELECT Department_id FROM Department WHERE Department_name = 'HR');
Conclusion
Subqueries are a versatile and essential tool in SQL, allowing for powerful and flexible data retrieval and manipulation. Understanding where and how to use subqueries can significantly enhance your ability to write complex SQL queries efficiently. By mastering the use of subqueries, you can tackle a wide range of data challenges in SQL.
Top comments (0)