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.
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:
|Sale ID||Date||Customer ID||Product ID||Total Amount|
|Customer ID||Name||Phone Number|
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:
SELECT * FROM Sales WHERE CustomerID IN ( SELECT CustomerID FROM Customer WHERE Email='**[firstname.lastname@example.org](<mailto:email@example.com>)'**);
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.
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.
-- First, let's insert a new customer into the Customers table INSERT INTO Customers (Name, Email, Phone_Number) VALUES ('Emily Brown', 'firstname.lastname@example.org', '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.
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.
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.
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);
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.
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.
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.
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.
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.
Answer: b. A query that's utilized to recover information that will be utilized within the main query
Answer:b. SELECT, FROM, WHERE, HAVING, EXISTS
Answer: a. Yes
Answer: a. IN, NOT IN, EXISTS
Related Tutorials to watch
Top Articles toRead