Subqueries in SQL
This SQL cheat sheet explains how to nest queries for powerful data filtering and manipulation within a single statement.
56. Single-row Subquery: Returns One Row of Result
SELECT first_name, last_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
In this example, the subquery (SELECT MAX(salary) FROM employees) returns a single row containing the maximum salary, and it’s used to filter employees who have the maximum salary.
57. Multiple-row Subquery: Returns Multiple Rows of Result
SELECT department_name
FROM departments
WHERE department_id IN (SELECT department_id FROM employees);
In this example, the subquery (SELECT department_id FROM employees) returns multiple rows containing department IDs, and it’s used to filter department names based on those IDs.
58. Correlated Subquery: References a Column from the Outer Query
SELECT first_name, last_name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);
In this example, the subquery (SELECT AVG(salary) FROM employees WHERE department = e.department) is correlated with the outer query by referencing the department column from the outer query. It calculates the average salary for each department and is used to filter employees whose salary is greater than the average salary of their respective department.
59. Nested Subquery: A Subquery Inside Another Subquery
SELECT first_name, last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE department_name = 'IT'
);
In this example, the subquery (SELECT department_id FROM departments WHERE department_name = ‘IT’) is nested within the outer query. It retrieves the department ID for the IT department, which is then used in the outer query to filter employees belonging to the IT department.
SQL Cheat Sheet
In this article, we will explore the ultimate SQL cheat sheet with the PDF, covering a wide range of SQL commands, Joins in SQL, CRUD Operations, SQL Trigger, SQL Transactions, and advanced topics to help master SQL effectively.
SQL (Structured Query Language) is a Query language used for managing and manipulating relational databases databases. It allows users to interact with databases. SQL allows users to perform various tasks such as querying data, updating data, inserting new records, deleting records, creating and modifying database schemas, and managing permissions.
SQL Cheat Sheet
Table of Content
- Create a Database in SQL
- Creating Data in SQL
- Reading/Querying Data in SQL
- Updating/Manipulating Data in SQL
- Deleting Data in SQL
- Filtering Data in SQL
- SQL Operator
- Aggregation Data in SQL
- Constraints in SQL
- Joins in SQL
- SQL Functions
- Subqueries in SQL
- Views in SQL
- Indexes in SQL
- Transactions in SQL
- Advanced Mixed Data in SQL
- SQL Cheat Sheet PDF