Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

What is SQL Subquery? Types of Subqueries in SQL

What is SQL Subqueries?

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:

  • Usually, a subquery consists of a single column only with the SELECT clause unless there are multiple columns in the main query to compare its selected columns.
  • The SQL subqueries must be enclosed within parenthesis.
  • An SQL subquery cannot use ORDER BY command while the main query can use ORDER BY command when required. Instead, you can use Group By command for the same purpose.
  • If there is a subquery that returns multiple rows together, it must be used with multiple value operators only like IN operator.
  • The SELECT list does not include any references to values that evaluate to a BLOB, CLOB, ARRAY, or NCLOB, etc.
  • We cannot enclose the subquery immediately in a set function.
  • We cannot use BETWEEN operator within a SQL subquery; However, it can be used with the main query.

Subqueries with SELECT Statement

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;

Subqueries with INSERT Statement

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. SQL Server Curriculum 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&gt; INSERT INTO CUSTOMERS_BKP
   SELECT * FROM CUSTOMERS 
   WHERE ID IN (SELECT ID 
   FROM CUSTOMERS) ;

Subqueries with UPDATE Statement

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.

AND, OR Statements in SQL

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.

Read: How To Become Expert In Sql Server Developer?

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];

WHERE Clause in the SQL

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.

Operators in SQL

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.

Arithmetic Operators Description
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?
= Equal to
<> Not Equal to
!= Not Equal to
< Less Than
<= Less than or Equal to
> Greater Than
>= 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.

Logical Operators Description
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.

Subqueries with DELETE Statement

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. SQL Server quiz 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.

Subqueries for aggregating multiple stages

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 with Conditional Logic

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.

Read: How to Install Microsoft SQL Server Express

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.

How to join subqueries in SQL?

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.

Subqueries and UNIONs

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 –

  • The same number of columns
  • The same number of expressions
  • The similar data types
  • And having them in the same order

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.

Correlated 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.

Read: Future Growth of a SQL Server Developer

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 Types – What you should know?

Subqueries are specified at multiple places. Let us discuss each of them one by one.

A). Subqueries with Aliases

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.

B). Subqueries with IN

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. free SQL Server demo 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.

C). Subqueries with Not IN

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.

Final Words:

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.

Read: Different Type of SQL Joins

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

1 day 14 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

2 days 15 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

2 days 15 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

3 days 16 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

1 day 14 Nov 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

20 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

2 days 15 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

6 days 19 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews