Subquery in SQL

Subquery in SQL

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Subqueries are often used in situations where a query depends on the results of another query. SQL Server supports noncorrelated and correlated subqueries.

In a noncorrelated subquery, the inner query is independent and gets evaluated first, then passes results to the outer query. A noncorrelated (independent) subquery can be independently evaluated and relies only on its own SELECT clause for instructions.

Syntax:

                   select columns from TableName

               where condition_columns in (select column from tablename where Condition)

This syntax of a subquery in SQL is not standard it may use in many different ways.

Example:-

                      We have two tables to explain subquery in SQL as shown below:-

Query:-

              select CustomerName,AccountNumber from Customer

               where CityId in (select id from City where CityName='Punjab')

OutPut:-

Explanation:- In the subquery example, we use the Customer and City table. We fetch all the customers those are from Punjab.

This subquery result is also obtained by using sql joins as shown below:-

Query:

                        SELECT Customer.CustomerName, Customer.AccountNumber

                   FROM   City INNER JOIN

                   Customer ON City.Id = Customer.CityId where CityName='Punjab'

Output:

We can also apply nested subquery there is not any condition to using only one subquery in SQL.