types of subqueries in sql
Subqueries, also known as nested queries, are a powerful feature in SQL that allows you to perform complex operations by nesting one query within another. They enable you to target specific rows and perform various operations in SQL, such as SELECT, UPDATE, INSERT, and DELETE. Subqueries come in various types, each serving a unique purpose in data manipulation. In this article, we will explore the different types of subqueries in SQL using practical subquery in SQL examples and provide insights into their applications.
Structured Query Language (SQL) is the foundation of managing and manipulating relational databases, allowing us to extract meaningful insights and valuable information from vast datasets. Within the realm of SQL, subqueries stand as a versatile and indispensable tool for unleashing the full potential of database systems.
Subqueries in SQL, often referred to as nested queries, represent a dynamic and sophisticated facet of SQL. They empower database professionals and developers to explore the intricate relationships within data, facilitating intricate operations and enabling precise data extraction. Subqueries are not merely a feature of SQL; they are the embodiment of analytical thinking and data-driven decision-making.
These nested queries bring forth a realm of possibilities, offering a means to scrutinise and traverse data hierarchies, refine results with precision, and pave the way for efficient data modification. Whether you seek to perform complex calculations, correlate datasets, or filter records based on intricate criteria, subqueries serve as your trusted companions in the journey through the database landscape.
In this comprehensive exploration, we delve into the depths of SQL subqueries, dissecting their various forms and demonstrating their real-world applications. As we navigate through the intricacies of single-row and multiple-row subqueries, delve into the nuances of correlated subqueries, and unlock the power of nested subqueries, you'll embark on a journey that enhances your ability to harness the full potential of SQL.
Through practical subquery example in sql and structured insights, this article illuminates the path to mastering SQL subqueries. Join us in unravelling the complexity of nested queries, and empower yourself to transform raw data into actionable intelligence.
Scenario: E-Commerce Store and Employee Management
In the bustling world of modern e-commerce, there exists an innovative and dynamic online store that caters to the diverse needs and desires of its customers. This online marketplace offers a wide range of electronic products, from cutting-edge laptops to sleek smartphones and stylish tablets. Let's dive into the narrative of this thriving e-commerce store and its dedicated team of employees.
The "Products" table represents the heart and soul of the online store's offerings. Each product is carefully curated and priced to meet the demands of tech-savvy shoppers. The "product_id" uniquely identifies every item, while the "product_name" provides a glimpse into its specifications and features. The "price" column reveals the cost at which these technological marvels are made available to customers.
Behind the scenes, a dedicated workforce drives the operations of the online store. The "Employees" table introduces us to the talented individuals who manage various aspects of the business. Employee details include their "employee_id," "last_name," "job_id" (highlighting their roles within the organization), "salary" (reflecting their compensation), and "department_id" (indicating the department to which they belong).
Within the e-commerce company, different departments collaborate to ensure smooth functioning. The "Departments" table sheds light on these organizational units. Each department is uniquely identified by its "department_id," and the "department_name" provides insight into its purpose. The "location_id" indicates the geographical location or region in which the department operates, whether in the United States (US) or the United Kingdom (UK).
Job History Table:
As employees progress in their careers, their roles may evolve. The "Job History" table maintains a historical record of these transitions. It records "employee_id," "job_id" (indicating their past or current roles), and "department_id" (revealing the department associated with each role change).
Orders Table and Order Items Table:
In the e-commerce realm, customer orders are the lifeblood of the business. The "Orders" table stores essential order information, including the "order_id" (a unique identifier for each order), "customer_id" (identifying the customer making the purchase), and "order_total" (the total amount of the order).
The "Order Items" table provides a granular view of each order, detailing the specific products and quantities included in each purchase. It references "order_id" to associate each item with its respective order and lists "product_id" and "quantity" to reveal the products selected by customers and the quantities ordered.
Job History Table
Order Items Table
Now let us explore the various types of subquery in SQL using these example tables.
Single-row subqueries return a single row of data as output to the parent query. They are typically used with SQL SELECT statements and can appear in clauses like WHERE, HAVING, or FROM.
SELECT product_name, price, (SELECT AVG(price) FROM products) AS avg_price FROM products;
In this example, the single-row subquery calculates the average price of all products (700.00) and displays it alongside each product's price.
Multiple-row subqueries return multiple rows of data as output to the parent query. They can be used with SQL SELECT statements and clauses like WHERE, HAVING, FROM, and logical operators.
SELECT AVG(salary) FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 'US');
This query uses a multiple-row subquery to calculate the average salary of employees in departments located in the 'US' (departments with location_id = 'US'). The result is an average salary of approximately 6166.67.
Multiple-column subqueries return multiple columns as output to the parent query. They are used to retrieve sets of data that include multiple columns.
SELECT employee_id, last_name, job_id FROM employees WHERE (job_id, department_id) IN (SELECT job_id, department_id FROM job_history);
This query employs a multiple-column subquery to retrieve employee information based on their job history. It selects employees whose (job_id, department_id) matches records in the job_history table.
Correlated subqueries return multiple columns as output and depend on information from the parent query. They can be used in SELECT statements using WHERE and FROM clauses.
SELECT department_name, (SELECT AVG(salary) FROM employees e WHERE d.department_id = e.department_id) AS avg_salary FROM departments d;
In this query, the correlated subquery calculates the average salary for each department based on the department ID in the parent query. However, it returns (NULL) for departments with no employees in the sample data.
Nested subqueries are subqueries within other subqueries in SQL. They are executed in a nested manner, with the innermost subquery executed first, followed by the outer ones.
SELECT order_id, customer_id, (SELECT SUM(order_total) FROM order_items WHERE order_id = orders.order_id) AS total_order_amount FROM orders;
This query demonstrates a nested subquery that calculates the total order amount for each order based on data from the "order_items" table. The output includes the order ID, customer ID, and the total order amount for each order.
Let's explore more SQL subquery examples using different tables:
Job History Table
Order Items Table
Subqueries can be used with the INSERT statement to insert selected rows from one table into another.
INSERT INTO new_orders SELECT * FROM orders WHERE order_date >= '2023-01-01';
This query inserts rows from the "orders" table with an order date on or after January 1, 2023, into the "new_orders" table.
Subqueries can be used with the UPDATE statement to set new values in a column based on the output of the subquery.
UPDATE employees SET salary = salary * 1.1 WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 'US');
This query updates the salaries of employees in departments located in the 'US' by increasing them by 10%.
Subqueries can also be used with the DELETE statement to delete specific rows based on the subquery's output.
DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM job_history WHERE job_id = 'Manager');
This query deletes employees whose job history includes a 'Manager' role.
In this comprehensive exploration of SQL subqueries, we've covered various subquery types, their practical applications, and provided illustrative examples. Subqueries are a versatile tool in SQL, enabling complex data retrieval and manipulation tasks. Understanding these different subquery types and how to use them effectively empowers you to perform advanced operations on your data efficiently. As you gain more experience and practice, you'll become a proficient SQL learner.
1. What is the primary purpose of a correlated subquery in SQL?
A) To return a single-row result
B) To return multiple rows of data
C) To calculate an average value
D) To depend on information from the parent query
Answer: D) To depend on information from the parent query
2. Which type of subquery returns a single row of data as output to the parent query?
A) Multiple-Row Subquery
B) Multiple-Column Subquery
C) Correlated Subquery
D) Single-Row Subquery
Answer: D) Single-Row Subquery
3. In SQL, what does a multiple-column subquery return as output to the parent query?
A) A single value
B) Multiple rows of data
C) Multiple columns of data
D) Correlated data
Answer: C) Multiple columns of data
4. What does a nested subquery in SQL involve?
A) Subqueries that cannot be executed
B) Subqueries that are unrelated to each other
C) Subqueries executed in a nested manner
D) Subqueries with no parent query
Answer: C) Subqueries executed in a nested manner
5. Which SQL statement can be used with subqueries to insert selected rows from one table into another?
Answer: C) INSERT
Related Tutorials to watch
Top Articles toRead