Home / Database / SQL Server / Sub queries In SQL Server
query

Sub queries In SQL Server

What is Query ?

A statement that request data from database is called as query. e.g. Select statement request data to the database so select statement is a query statement.

What is Sub Query ?Definition ?

A select statement used inside another select statement is called subquery statement. Subquery must be enclosed within parenthesis [“()“] to give the subquery high priority & execute it first.

Subqueries are differentiated into following types.

  • Single Row Subquery.
  • Multi Row Subquery.
  • Nested Sub Query.
  • Co-related Subquery.

Sample Data for understanding

query_1 Sub queries In SQL Server SQL Server

  • Single Row Subquery

 

Definition: The subquery which returns single row as a result is called Single Row Subquery.

Query:Select the student from “Electronics” department.

Syntax: 

SELECT * FROM STUDENT WHERE [DEPARTMENT NUMBER] = (SELECT [DEPARTMENT NUMBER] FROM DEPARTMENT WHERE [DEPARTMENT]=’Electronics’ )

Result will be :

query_2 Sub queries In SQL Server SQL Server

  • Multi Row Subquery

 

Definition: The subquery which returns multiple rows as a result is called Multi Row Subquery.

Description: With multi row subquery we cannot use the arithmetic operators like ‘EQUAL’, ‘!=’, ‘<‘, ‘>’, ‘<=’ & ‘>=’ because these operators are used only to compare one value at a time. So to deal with the situation where we need to use these operators in multi row subquery, we have predicates like “Some“, “Any” & “ALL” etc.

Some” & “Any” : “Some” & “Any” predicates  will return true if at least one (row)value of multi row subquery satisfies the condition.

ALL” : “ALL”  predicate will return true only if all the rows(values) returned by multi row subquery satisfies the condition.

Query:Select the student which are not in “Electronics” (i.e. Department Number 4 ) department & whose marks are greater than any one student from “Electronics” department.

Syntax: 

SELECT * FROM STUDENT WHERE [DEPARTMENT NUMBER] != 4

And MARKS> ANY (SELECT DISTINCT MARKS FROM STUDENT WHERE [DEPARTMENT NUMBER]=4 )

 

Result will be :

query_3 Sub queries In SQL Server SQL Server

Query:Select the student which are not in “Electronics“(i.e. Department Number 4 )  department & whose marks are greater than all the student from “Electronics” department.

Syntax: 

SELECT * FROM STUDENT WHERE [DEPARTMENT NUMBER] != 4

And MARKS> ALL (SELECT DISTINCT MARKS FROM STUDENT WHERE [DEPARTMENT NUMBER]=4 )

 

Result will be :

query_4 Sub queries In SQL Server SQL Server

  • Nested Subquery

 

Definition: When subquery is written inside another subquery then it is called as nested subquery.

 

Query:Find the student whose marks are more than average marks of “Electronics” (i.e. Department Number 4 ) .

Syntax: 

SELECT * FROM STUDENT WHERE MARKS>

(SELECT AVGMARKS FROM STUDENT WHERE [DEPARTMENT NUMBER]=

(SELECT [DEPARTMENT NUMBER] FROM DEPARTMENT WHERE [DEPARTMENT]=’Electronics‘ )

)

  • Co-related Subquery

Definition: When subquery refers to the one of the column from the main query in the where clause then the subquery is called as Co-related subquery.

Description: A normal subquery is executed only once whereas the co-related subquery gets executed for every of main query.

Query:Select the student whose having maximum marks.

Syntax: 

SELECT * FROM STUDENT S WHERE [MARKS] = (SELECT MAX( [MARKS])  STUDENT S1 WHERE S.[Department Number]=S1.[Department Number]  )

About Helpservices

About This site (allhelpservices.com) is dedicated to helping other bloggers learn the skills of blogging, wordpress, education, technology, social media sharing , SEO, Electronics, Earn Money From Blogging, Web hosting and web share their own experiences and promote the blogging medium. We started Allhelpservices in August 2015 mainly because we wanted to keep a record of what we wear learning from development and because we wanted to connect with other learners.

Check Also

Delete Command

Difference between Delete and Truncate command

Difference between Delete and Truncate command Delete Command 1. It allows to delete all rows as ...

Leave a Reply