A Subquery, also named as the inner query or nested query is a query within another SQL query and embedded within the WHERE clause. A subquery helps to return data used by the main query as a condition to restrict the data retrieval further. Subqueries are majorly used with SELECT, INSERT, UPDATE, and DELETE statements along with comparison operators like
=, <, >, >=, <=, IN, BETWEEN, etc.
Here are the considerations that are followed by each subquery in the SQL:
BLOB, CLOB, ARRAY, or NCLOB, etc.
SQL subqueries are majorly used with SELECT statement, and its basic syntax is given below.
SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])
The SELECT statement in SQL is used to fetch data from a database table, and this data is returned in the form of the result table. These result tables can also be named as result-sets. The basic syntax for the SQL SELECT statement is given below.
SELECT Column1, column2, column from table_name;
Here, column1, column2, is fields of a table whose value you want to fetch. To fetch all fields together from a database table, you can use the following command:
SELECT * From Table_name;
After SELECT statement, the second-best option to use with a subquery is INSERT clause. The INSERT statement uses data returned from the subquery to enter into another table. The selected data in the subquery can be further modified through any of the characters, date, or number functions. The basic syntax of the INSERT statement within a subquery is given below.
INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ]
In general, the SQL INSERT INTO statement is used to add new rows to an existing database table. Here column1, column2, is the name of columns where you want to add data. You don’t have to specify column names when you want to copy the whole table data together to a new table. Further, you can also populate the data of one table to another table using the SELECT statement in conjunction with the INSERT statement. You just have to give the other table as a set of fields that are needed to populate the first table. Take an example of Customer_New table whose structure is almost similar to the Customer table. You can use the following code to copy the complete data from the customer table to the Customer_New table.
SQL> INSERT INTO CUSTOMERS_BKP SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS) ;
The SQL subqueries are generally used in conjunction with the UPDATE statement. You can update multiple columns together when using a subquery with the UPDATE statement. The basic syntax for using UPDATE statement with SQL subqueries is given below.
UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
As we know, the UPDATE statement is used to modify an existing record within a database table. To modify selected rows, WHERE clause should be used with UPDATE statement; otherwise, it may disturb the overall functioning of the table. You can combine multiple conditions here using AND, OR operators.
The AND, OR operators in SQL are used to combine multiple conditions to narrow data in an SQL statement. These two operators are also named as conjunctive operators in SQL. These operators help conduct multiple comparisons with different operators in the same SQL statement.
AND operator allows the existence of multiple conditions in an SQL statement using the WHERE clause. For an action to be performed by the SQL statement, either it is a query or transaction, multiple conditions separated by AND must be true. The basic syntax of AND operator in SQL is given below.
SELECT Column1, Column2, ColumnN FROM table_name WHERE [condition] AND [condition2]…… AND [conditionN];
Similar, the OR operator allows the existence of multiple conditions in an SQL statement using the WHERE clause. For an action to be performed by the SQL statement, either it is a query or transaction, multiple conditions separated by OR must be true. The basic syntax of the OR operator in SQL is given below.
SELECT Column1, Column2, ColumnN FROM table_name WHERE [condition] or [condition2]…… or [conditionN];
The WHERE clause in SQL is used to specify a condition while fetching data from a single table or combining multiple tables together. If the given condition is true, then it returns the specific value from the table; otherwise it returns NULL values. The SQL WHERE clause is basically used to filter the data and fetch necessary records. The basic syntax of the WHERE clause in SQL is given below.
SELECT Column1, Column2, ColumnN FROM table_name WHERE [condition];
You can use comparison or logical operators with WHERE clause to make it more meaningful.
An operator in SQL is a reserved word to perform a specific function and used along WHERE clause most of the times. They are used in conjunction with the WHERE clause to specify multiple conditions within a statement. Operators in SQL are majorly divided into four categories – Arithmetic, Logical, Comparison, etc. Let us discuss each of them one by one.
|Addition (+)||It is used to add values on either side of the operator.|
|Subtraction (-)||It is used to subtract right-hand operand from the left-hand operand.|
|Multiplication (*)||It is used to multiply values on either side of the operator.|
|Division (/)||It is used to divided left-hand operand by right-hand operand.|
|Modulus (%)||It is used to divided left-hand operand by the right-hand operand and returns the remainder.|
A complete list of comparison operators in SQL is given below for your reference with operator name and its description.
|Comparison Operator||What does it mean?|
|<>||Not Equal to|
|!=||Not Equal to|
|<=||Less than or Equal to|
|>=||Greater than or Equal to|
|LIKE '%expression%'||Contains 'Expression'|
|IN ('exp1', 'exp2', 'exp3')||Contains only of 'exp1', 'exp2' or 'exp3'|
Here is the list of logical operators to use with SQL subqueries and WHERE clause.
|ALL||It is used to compare the complete value set from another value set.|
|AND||AND operator allows the existence of multiple conditions in an SQL statement using the WHERE clause.|
|ANY||This operator is used to compare the particular value from the list as per the given condition.|
|BETWEEN||This operator is used to search values within a set of values where minimum are maximum values are given separately.|
|EXISTS||It is used to check the existence of a particular row based on the specified condition.|
|IN||It is used to compare a particular value from the list of literal variables|
|LIKE||It is used to compare similar values using wildcard operators.|
|NOT||It is used to reverse the meaning of a logical operator that is used with the statement.|
|OR||OR operator allows the existence of multiple conditions in an SQL statement using the WHERE clause|
|IS NULL||It is used to compare a specific value with the NULL value.|
|UNIQUE||This operator will check all rows for uniqueness. There should not be any duplicate values.|
A subquery can be used in conjunction with DELETE statement too like any other statement discussed above. The basic syntax is given below.
DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
As we know that DELETE statement is used to delete the existing records from a table. You can combine N number of conditions here using AND, OR operators. The depth idea of these two operators is already given earlier with examples for your reference. To delete a particular record from the table, you should use the WHERE clause. When you are using the WHERE clause, it will delete the selected data based on the condition; At the same time, if you want to delete the complete data from a database table, then you should not use WHERE clause in that case.
How to figure out how many incidents are reported on each day of the week. What to do if you want to check how many incidents happen on average, on a Friday in October. There are two steps to complete this process. First, you should count the number of incidents happening each day using the inner query and calculate the monthly average with the outer query.
SELECT LEFT(sub.date, 2) AS cleaned_month, sub.day_of_week, AVG(sub.incidents) AS average_incidents FROM ( SELECT day_of_week, date, COUNT(incidnt_num) AS incidents FROM tutorial.sf_crime_incidents_2014_01 GROUP BY 1,2 ) sub GROUP BY 1,2 ORDER BY 1,2
If you are having trouble in figuring what is happening, then you may run inner query independently and check how the result looks like. It will help you in getting a depth idea of the concept. In general, it is easy writing subqueries and modifying them as per the requirement. You can revise them until the result satisfies you or make sense to you, then you can move to the outer query.
Subqueries can be used in conjunction with conditional logic WHERE, JOIN, IN, etc. the following query returns the entries from earliest data from a dataset. Here is the syntax for your reference.
SELECT * FROM tutorial.sf_crime_incidents_2014_01 WHERE Date = (SELECT MIN(date) FROM tutorial.sf_crime_incidents_2014_01 )
The above query works perfectly because the final output is a single cell only. Most conditional logic working with subqueries contains one-cell results. However, there is only the operator “IN” that may return multiple outputs with subqueries. Make sure you are not using an alias when using conditional logic with subqueries. This is because that a subquery is considered as an individual value, not a table.
If you can filter subqueries, then it is pretty obvious that you can join them as well. Here is the syntax for the same.
SELECT * FROM tutorial.sf_crime_incidents_2014_01 incidents JOIN ( SELECT date FROM tutorial.sf_crime_incidents_2014_01 ORDER BY date LIMIT 5 ) sub ON incidents.date = sub.date
It is more useful when combined with aggregations. When you are joining subqueries, you will notice that outputs are not so stringent as when you use the WHERE clause. For example, one particular inner query may produce multiple outputs together. The following query will rank the output based on how many incidents were reported in a given day. It can be done by aggregating the total number of incidents every day and using the same to sort the outer query.
SELECT incidents.*, sub.incidents AS incidents_that_day FROM tutorial.sf_crime_incidents_2014_01 incidents JOIN ( SELECT date, COUNT(incidnt_num) AS incidents FROM tutorial.sf_crime_incidents_2014_01 GROUP BY 1 ) sub ON incidents.date = sub.date ORDER BY sub.incidents DESC, time
You must be wondering where to use the subqueries. In practice, subqueries are useful for improving the overall performance of your queries. Imagine you want to aggregate the overall Company receiving investments and acquired by the Company each month. It is possible with the help of subqueries only. They take only minutes to return the final value and easy to manage as well.
Basically, when you are joining every row in a given month from one table on to every month in a given row to another table, the overall number of rows returned is incredibly great. This multiplicative effect says that you should use Count (Distinct) operator here instead of using simply Count. It helps you in aggregating two tables separately then join them together to perform the accurate counts across far smaller datasets.
The SQL UNION operator is generally used to combine result data from two or more SELECT statements by returning unique values only. For using UNION clause, each SELECT statement should have –
They need not have in the same length. The syntax of the UNION operator in SQL is given below where a specified condition could be any expression based on your requirements.
SELECT Column1, [, Column2 ] FROM table1 [, table 2] [WHERE condition] UNION SELECT Column1, [, Column2 ] FROM table1 [, table 2] [WHERE condition]
If you want to combine results from two SELECT statements including duplicate values, then you should use UNION ALL operator in that case. The same rules will be applied to both the operators and its syntax can be written as below.
SELECT Column1, [, Column2 ] FROM table1 [, table 2] [WHERE condition] UNION ALL SELECT Column1, [, Column2 ] FROM table1 [, table 2] [WHERE condition]
Union operator can be used in conjunction with subqueries as well. Here is the syntax of how to use UNION operator with subqueries:
SELECT * FROM tutorial.crunchbase_investments_part1 UNION ALL SELECT * FROM tutorial.crunchbase_investments_part2
Splitting a dataset into parts is not logical especially when data has to be passed through Excel at any point. These two parts are considered different when you apply operations on them. The best idea is applying operations on a complete dataset together using subqueries. Here you should use UNION ALL operator instead of UNION. Try it yourself; it is pretty easy suing UNIONS with subqueries.
A subquery can be nested to multiple levels within a statement. Here, you should use JOIN operator to connect multiple subqueries. In a few cases, queries are evaluated by executing the subquery once and put the result value into the outer query.
For example, if there is one correlated subquery, then inner subquery depends on the outer query for its values. Here, you have to execute the subquery repeatedly, once for each row that might be selected by the outer query later.
USE AdventureWorks2016; GO SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID FROM Person.Person AS c JOIN HumanResources.Employee AS e ON e.BusinessEntityID = c.BusinessEntityID WHERE 5000.00 IN (SELECT Bonus FROM Sales.SalesPerson sp WHERE e.BusinessEntityID = sp.BusinessEntityID) ; GO
Here is the result set.
LastName FirstName BusinessEntityID -------------------------- ---------- ------------ Ansman-Wolfe Pamela 280 Saraiva José 282 (2 row(s) affected)
The previous subquery in this statement is not evaluated independently of the outer query. This is exactly the right way how subqueries are executed and evaluated when they are correlated.
Subqueries are specified at multiple places. Let us discuss each of them one by one.
Many subqueries where the inner query and the outer query refer to the same table, they are connected by self-joins. Here, table aliases are required when the same table used for two different roles in the same query. Aliases are also used in nested queries that refer to the same table for inner and outer query.
When subqueries are used with IN operator, it may return zero or multiple values depends on conditions. Once the subquery returns the result, it is used by the outer query. The same result can be produced with JOIN operator too, but you have to make sure where to use subquery and where to use JOIN versions. A join can always be expressed as a subquery. At the same time, a subquery can be expressed as a join sometimes not always. This is because joins are symmetric, you can join two tables in either order, results will be the same. The same is not true when the subquery is involved.
When subqueries are used with NOT IN operator, it may return zero, or multiple values depends on conditions. The subquery with NOT IN operator cannot be converted to a join because joins have different meanings in this case. Moving ahead, subqueries are used with many more operators and most of them we have discussed in the blog like SELECT, UPDATE, INSERT, DELETE, UNION, conditional logic and more.
To understand this concept a little better, you can make the research at your own part otherwise we tried to cover the maximum information in the blog to help you with tough database subqueries.
We strongly encourage you to play with syntax and run queries without some of the given operators and check how they work. You may also run each of the subqueries independently and understand how they can improve the database performance for your Company. To know more on SQL subqueries, you may join the SQL server course at JanBask training and get in touch with us.
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Receive Latest Materials and Offers on SQL Server Course