Masterpiece SQL Tutotial 4 Sub Queries | Single and Multi Row
A subquery, also known as an inner query or nested query, is a query within another SQL query. It is often used to retrieve data that will be used in the main query as a condition to filter the result set further. Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements.
Here’s an example of a subquery:
Suppose you have two tables: “Orders” and “Customers.” The “Orders” table has columns like “OrderID,” “CustomerID,” and “OrderDate.” The “Customers” table has columns like “CustomerID,” “CustomerName,” and “City.”
Now, let’s say you want to retrieve all the orders made by customers from a specific city, for example, “New York.” You can use a subquery to achieve this.
SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE CustomerID IN (SELECT CustomerID
FROM Customers
WHERE City = ‘New York’);
In this example, the subquery (SELECT CustomerID FROM Customers WHERE City = ‘New York’) retrieves all the CustomerIDs of customers who live in New York. The main query then uses this result to retrieve all orders made by customers from New York.
Subqueries can be used in various ways and can be quite powerful in filtering and retrieving data. They can be used with comparison operators such as <, >, =, >=, <=, <>, or with the IN or NOT IN operators. Subqueries can also be correlated, meaning they depend on the outer query, or they can be non-correlated, meaning they can run independently of the outer query.
Types of Sub Queries
Subqueries can be classified into various types based on their usage and the way they interact with the outer query. Here are some common types of subqueries:
Single-row subquery: A subquery that returns only one row of results, typically used with single-row operators such as =, >, <, >=, <=, or <>. For example:
SELECT column_name(s)
FROM table_name
WHERE column_name = (SELECT column_name FROM table_name WHERE condition);
Multiple-row subquery: A subquery that returns multiple rows of results, typically used with multiple-row operators such as IN, ANY, or ALL. For example:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);
Correlated subquery: A subquery that depends on the outer query for its values. It references a column from the outer query, allowing the inner query to be executed repeatedly, once for each row processed by the outer query. For example:
SELECT column_name(s)
FROM table_name outer
WHERE column_name operator (SELECT column_name FROM table_name inner WHERE outer.column_name = inner.column_name);
Nested subquery: A subquery that is embedded within another subquery, typically used for complex conditions or when more than one level of nesting is required. For example:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM table_name WHERE column_name IN (SELECT column_name FROM table_name WHERE condition));
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM table_name WHERE column_name IN (SELECT column_name FROM table_name WHERE condition));
Scalar subquery: A subquery that returns a single column and a single row, often used in expressions where a single value is expected. For example:
SELECT column_name, (SELECT MAX(column_name) FROM table_name) AS max_value
FROM table_name;
Understanding these different types of subqueries can help you utilize them effectively for various SQL operations, such as filtering, comparison, and data retrieval.
Single Row Subqueries with example
Single-row subqueries are subqueries that return only one row of results. They are commonly used with single-row operators such as =, >, <, >=, <=, or <>. Here’s an example of a single-row subquery:
Suppose you have a database with two tables: “Employees” and “Salaries.” The “Employees” table contains information about employees, including their IDs and names.
The “Salaries” table contains information about their respective salaries, with columns like “EmployeeID” and “SalaryAmount.”
Let’s say you want to find the name of an employee who has a salary greater than $50,000. You can use a single-row subquery for this purpose.
SELECT Name
FROM Employees
WHERE EmployeeID = (SELECT EmployeeID
FROM Salaries
WHERE SalaryAmount > 50000);
In this example, the single-row subquery (SELECT EmployeeID FROM Salaries WHERE SalaryAmount > 50000) retrieves the EmployeeID of the employee who has a salary greater than $50,000.
The main query then uses this result to retrieve the name of that particular employee from the “Employees” table.
Single-row subqueries are useful when you want to retrieve a single value or perform comparisons with a single value in a table. They are helpful in scenarios where you want to find specific data points or make comparisons based on a specific condition in a subquery.
Multiple Row Subqueries with example
Multiple-row subqueries are subqueries that return multiple rows of results. They are commonly used with multiple-row operators such as IN, ANY, or ALL. Here’s an example of a multiple-row subquery:
Suppose you have a database with two tables: “Products” and “Orders.” The “Products” table contains information about various products, including their IDs and names.
The “Orders” table contains information about orders placed by customers, with columns like “ProductID” and “OrderDate.”
Let’s say you want to find the names of products that have been ordered at least once. You can use a multiple-row subquery for this purpose.
SELECT Name
FROM Products
WHERE ProductID IN (SELECT ProductID
FROM Orders);
In this example, the multiple-row subquery (SELECT ProductID FROM Orders) retrieves all the ProductIDs from the “Orders” table. The main query then uses this result to retrieve the names of products that have been ordered at least once from the “Products” table.
Multiple-row subqueries are useful when you want to compare a value against multiple values returned by a subquery. They are often used when you need to find data that satisfies a condition based on multiple rows in a related table.