Subqueries in SQL

Overview

A subquery in SQL could be a query that's nested inside another query. The subquery is utilized to recover information that will be utilized within the main query as a condition or criteria to filter the comes about. Subqueries are commonly utilized to recover information from numerous tables or to perform complex operations on information.

What is Subquery?

A subquery may be a query that's nested inside another query. It is utilized to retrieve information that will be utilized as input for most queries. Subqueries can be utilized within a query's WHERE, FROM, or SELECT clauses. They can be nested to any depth, but it is, most suggested to keep them straightforward and dodge excessive nesting. The result of a subquery can be a single esteem or a set of values, depending on the sort of operation performed. Subqueries are an effective feature of SQL that permits complex queries to be executed with relative ease.

Example:

Assume we need to discover all clients who have made a purchase within the final month. We may utilize a subquery to begin with discover all the customer IDs that show up within the "sales" table for the final month, and after that, utilize that list to filter the "customers" table:

• A "sales" table contains data about each deal, including the date, the customer ID, the product ID, and the whole sum of the sale.
• A "clients" table that contains data on approximately every client, including their ID, name, email, and phone number.

Sales Table:

Customers Table:

SELECT * 
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id 
        FROM sales
    WHERE sale_date >= DATEADD(month, -1, GETDATE())
)

This query would return a list of all the clients who have made a buy within the last month, along with their other data from the "clients" table. The subquery finds all the unique client IDs that show up within the "sales" table for the final month (utilizing the DISTINCT keyword), and the outer query filters the "customers" table to only include those IDs.

Here SELECT DISTINCT customer_id FROM sales WHERE sale_date >= DATEADD(month, -1, GETDATE()) is the subquery and SELECT * FROM customers WHERE customer_id IN () is the outer query.

Rules for Subquery

Here are some rules for subqueries:

1. A subquery must be enclosed in parentheses.

SELECT * 
FROM Sales 
WHERE CustomerID IN (
    SELECT CustomerID 
    FROM Customer 
    WHERE Email='**[jane.doe@example.com](<mailto:jane.doe@example.com>)'**);

2. A subquery can be used in a SQL statement's SELECT, FROM, WHERE, HAVING, and EXISTS clauses.
3. A subquery can return only one value or one row of values.
4. A subquery must return a compatible data type with the column it is being compared to or used in a conditional statement.
5. A subquery must be executed first, and the outer query then uses its results.
6. A subquery can be correlated, meaning it can reference a column from the outer query.
7. A subquery can be nested, meaning it can contain another subquery.
8. A subquery can be utilized with the IN, NOT IN, ANY, ALL, EXISTS, and NOT EXISTS operators.
9. A subquery should be used cautiously because it can affect query execution and proficiency.
10. A subquery can be utilized to perform an assortment of assignments, including filtering information, retrieving information from different tables, and performing calculations on the information.

Sub-query with Select Statement

A subquery with a SELECT statement is frequently utilized to channel or recover information based on a few criteria. Here is the syntax of a subquery with a SELECT statement:

SELECT column1, column2, column3 
FROM table1 
WHERE column1 IN (
        SELECT column1 
        FROM table2 
        WHERE column2 = 'value' );

In this example, the subquery is used to retrieve all the values in column1 of table2 where column2 is equal to ‘value’. The main query then uses these values to filter the results of table1 by only returning rows where column1 matches one of the values returned by the subquery.

The subquery is enclosed in parentheses and is used in place of a literal value or a list of values in the WHERE clause of the main query. The subquery can also be used with other clauses such as FROM, JOIN, HAVING, and ORDER BY.

Example:

SELECT s.Sale_ID, s.Date, s.Total_Amount,
       (SELECT c.Name FROM Customers c WHERE c.Customer_ID = s.Customer_ID) AS Customer_Name,
       (SELECT c.Email FROM Customers c WHERE c.Customer_ID = s.Customer_ID) AS Customer_Email,
       (SELECT c.Phone_Number FROM Customers c WHERE c.Customer_ID = s.Customer_ID) AS Customer_Phone_Number
FROM Sales s;

In this example, it selects the Sale_ID, Date, Total_Amount, and customer information for each sale. The subqueries use the Customer_ID field in the Sales table to match the customer information from the Customers table. The subqueries select the customer name, email, and phone number based on the Customer_ID in the Sales table. The AS keyword is used to alias the column names for the customer information.

Sub-query with Insert Statement

The syntax for inserting data using a subquery is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM another_table
WHERE conditions;

We are inserting data into a table called "table_name" and specifying the columns into which we want to insert data. We then use a subquery to select the data we want to insert from another table called "another_table". We can also specify any conditions we want to apply to the selected data.

Example:

-- First, let's insert a new customer into the Customers table
INSERT INTO Customers (Name, Email, Phone_Number)
VALUES ('Emily Brown', 'emily.brown@example.com', '555-678-9012');

-- Next, let's insert a new sale into the Sales table, using the customer ID from the previous INSERT statement
INSERT INTO Sales (Date, Customer_ID, Product_ID, Total_Amount)
VALUES ('2022-03-15', (SELECT Customer_ID FROM Customers WHERE Name = 'Emily Brown'), 2001, 75.00);

In this example, we first inserted a new customer named "Emily Brown" into the Customers table. Then, we used a subquery to select the customer ID of the newly inserted customer and insert a new sale into the Sales table with the appropriate customer ID. Note that we used the WHERE clause in the subquery to select the customer ID based on the customer name.

Sub-query with UPDATE Statement

The basic syntax for using a subquery with an UPDATE statement is as follows:

UPDATE table_name
SET column_name = new_value
WHERE column_name IN (
    SELECT column_name 
    FROM another_table 
    WHERE conditions);

We are overhauling a column called "column_name" in a table called "table_name". We utilize a subquery to choose the rows we need to overhaul based on the comes about of a query from another table called "another_table". Able to indicate any conditions we need to apply to the chosen information.

Example:

UPDATE sales
SET total_amount = total_amount * 1.1
WHERE customer_id = (
    SELECT customer_id
    FROM customer
    WHERE customer_name = 'John Doe'
);

In this case, the subquery is utilized to recover the customer_id of the client named 'John Doe' from the customer table. Most query then uses this customer_id to upgrade the deals records of that client within the deals table by expanding the sales_amount by 10%.

Sub-query with DELETE Statement

Here is the basic syntax for using a subquery with a DELETE statement:

DELETE FROM table_name
WHERE column_name IN (
    SELECT column_name 
    FROM another_table 
    WHERE condition);

In this syntax, the subquery is enclosed in parentheses and is used to retrieve data from another table that meets a specific condition. The data returned by the subquery is then used to delete rows from the table_name table where the column_name matches the values returned by the subquery.

Example:

DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM sales
    WHERE date < '2022-01-01'
);

In this example, the subquery is used to retrieve the customer_id of all customers who placed an order before January 1, 2022, from the sales table. The main query then uses these customer_id’s to delete the corresponding records from the customers table.

Sub-query with FROM statement

A subquery in the FROM clause of a SQL query is known as a derived table or subselect. It is a query that is used to generate a temporary table that can be used in the main query. The temporary table created by the subquery is often referred to as a derived table. Here is the basic syntax for using a subquery in the FROM clause of a SQL query:

SELECT column1, column2, ...
FROM (SELECT column1, column2, ...
      FROM table_name
      WHERE condition) AS derived_table_name
WHERE condition;

In this syntax, the subquery is enclosed in parentheses and is used to generate the derived table, which is then referenced in the main query. The derived table is given a name (in this example, derived_table_name) and is treated like any other table in the database.

Example:

SELECT c.Customer_Name, Total_Sales
FROM (SELECT Customer_ID, SUM(Total_Amount) AS Total_Sales
      FROM Sales
      GROUP BY Customer_ID
      HAVING SUM(Total_Amount) > 150) AS s, Customers AS c
WHERE c.Customer_ID = s.Customer_ID;

In this example, the subquery is executed first to create a temporary table (aliased as "s") that retrieves the total sales amount for each customer from the sales table using the SUM function and GROUP BY clause. The HAVING clause filters out customers who have made a total sale less than or equal to 150. Then, the main query retrieves the customer names and total sales amounts from the temporary table "s" and the customers table using the WHERE clause to match the customer IDs.

Sub-query with WHERE Statement

The subquery is enclosed in parentheses and placed within the WHERE clause. Here is an example of the syntax:

SELECT column1, column2, ...
FROM table1
WHERE columnN (subquery);

Example:

SELECT name
FROM customer
WHERE customer_id IN (SELECT customer_id FROM sales WHERE amount > 200);

In the above example, we are finding the name of the customers who made a sale of more than $200.

Subquery with IN and NOT IN

Subqueries in the WHERE clause can be used with the IN and NOT IN operators to filter records based on the results of another query. The IN operator includes records that match the result set of the subquery, while the NOT IN operator excludes records that match the result set of the subquery.

Example:

In statement:

SELECT *
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM sales
    WHERE date >= DATEADD(month, -1, GETDATE())
);

This query uses a subquery in the WHERE clause to filter customers based on whether they made a sale in the last month. The subquery returns a list of customer IDs who made a sale in the last month, and the IN operator includes customers who match that list.

Not In statement:

SELECT *
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM sales
    WHERE date >= DATEADD(year, -1, GETDATE())
);

This query uses a subquery with the NOT IN operator to filter out customers who made a purchase in the last year. The subquery returns a list of customer IDs who made a sale in the last year, and the NOT IN operator excludes customers who match that list.

Subquery with EXISTS and Not EXIST

Subqueries within the WHERE clause can also be utilized with the EXISTS and NOT EXISTS operators to filter records based on whether a subquery returns any rows. The EXISTS operator is utilized to incorporate records that have a coordinate within the result set of the subquery, whereas the NOT EXISTS operator is used to prohibit records that have a coordinate within the result set of the subquery.

Examples:

Exists

SELECT *
FROM customers
WHERE EXISTS (
    SELECT *
    FROM sales
    WHERE sales.customer_id = customers.customer_id
);

This query uses a subquery with the EXISTS operator to filter customers based on whether they have made a sale or not. The subquery returns rows where the customer ID matches the customer ID in the outer query, and the EXISTS operator includes customers with a match.

Not Exists

SELECT *
FROM customers
WHERE NOT EXISTS (
    SELECT *
    FROM sales
    WHERE sales.customer_id = customers.customer_id
);

This query uses a subquery with the NOT EXISTS operator to filter out customers who have made a sale. The subquery returns rows where the customer ID matches the customer ID in the outer query, and the NOT EXISTS operator excludes customers with a match.

Conclusion

A subquery may be a query nested inside another SQL query. It is used to recover information that's used as input for the most query, and it can be utilized within the WHERE, FROM, or SELECT clauses of a query. Subqueries can be correlated, nested, and utilized with different operators like IN, NOT IN, ANY, ALL, EXISTS, and NOT EXISTS. However, subqueries should be cautiously utilized as they can affect query execution and effectiveness.

Key Takeaways

• A subquery in SQL is a query nested inside another query used to retrieve data as input for the main query.
• Subqueries can be utilized within a query's WHERE, FROM, or SELECT clauses and can be settled to any depth.
• Subqueries can return a single esteem or a set of values, depending on the sort of operation performed.
• Subqueries can be utilized to channel information, recover information from different tables, and perform calculations on the information.
• Subqueries must be encased in parentheses and utilized with the IN, NOT IN, ANY, ALL, EXISTS, and NOT EXISTS operators.
• Subqueries should be cautiously utilized as they can affect query execution and effectiveness.

Quiz

1. What is a subquery in SQL? 
   1. A query that's utilized to form a new table 
   2. A query that's utilized to recover information that will be utilized within the main query 
   3. A query that's utilized to erase information from a table 
   4. A query that's utilized to overhaul information in a table

Answer: b. A query that's utilized to recover information that will be utilized within the main query

2. Where can a subquery be utilized in a SQL statement? 
   1. SELECT, Update, Delete 
   2. SELECT, FROM, WHERE, HAVING, EXISTS  
   3. SELECT, FROM, WHERE

Answer:b. SELECT, FROM, WHERE, HAVING, EXISTS

3. Can a subquery return multiple rows? 
   1. Yes 
   2. No

Answer:a. Yes

4. Can a subquery reference a column from the outer query?  
   1. Yes  
   2. No

Answer: a. Yes

5. Which operators can be used with a subquery in SQL?  
   1. IN, NOT IN, EXISTS 
   2. BETWEEN, LIKE, = 
   3. GROUP BY, ORDER BY, HAVING 
   4. UNION, JOIN, INTERSECT

Answer: a. IN, NOT IN, EXISTS