SQL:Target a SubQuery

SQL-Tables
SQL-Tables

SQL:Target a SubQuery

Welcome to CodeSpread!

People ask me a lot about subquery so I thought let’s answer with a article touching a bit of everything about subquery.

Before discussing SubQuery, there are few things which we should know like:

What is a Query?

I will go with its English Definition first. As a verb, “Put a question or questions to”.

Technical definition w.r.t. SQL “These are the commands issued to a database for retrieval of required information.” If we see closely,both the definitions complement each other. For example:

select all data the in employees table.


select * from employees

Lets put a condition like- select all employees with salary>5000


select * from employee where salary>5000

What is a SubQuery?

SubQuery can be treated as a ‘query on query’. A subquery is the inner query which provides a targeted result to the outer main query . We can try few examples to learn it

Example:

select employee name with its manager name


select emp.name,(select mgr.name from employee AS mgr
where emp.mgrid=mgr.empid) from employee AS emp

We mostly see subqueries in where clause like – select employees having average salary


select * from employee where salary=(select AVG(salary) from employee)

What is Correlated SubQuery?

A correlated sub-query  is a sub-query that uses values from the outer query in its WHERE clause.Let’s try with an example

select employees having salary greater than average salary of employees of department ‘IT’


select * from employee where salary=(select AVG(salary) from employee
where department=’IT’)

The main difference is that the subquery will be executed for each row before the result can be used by outer query.

Why do we require SubQuery or advantages of SubQuery?

  1. SubQuery holds the results like a temporary table which can be used by outer query.
  2. SubQuery are easier to understand
  3. SubQuery breaks down a complex query into small and simple queries.
  4. SubQuery are easy to use as a replacement of joins.There is no major difference in performance.

SubQuery Rules

A subquery is subject to the following restrictions:

  • Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query
  • If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output
  • The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).
  • If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.
  • The ntext, text, and image data types cannot be used in the select list of subqueries.
  • Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
  • The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
  • The COMPUTE and INTO clauses cannot be specified.
  • ORDER BY can only be specified when TOP is also specified.
  • A view created by using a subquery cannot be updated.
  • The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are the same as those for a standard select list, because a subquery introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.

ref:http://msdn.microsoft.com/en-us/library/ms189543(v=sql.105).aspx

Join Vs SubQueries

I was looking for this answer and though it’s not a verified answer but yes,it’s true in most cases. refer this :http://stackoverflow.com/questions/2577174/join-vs-subquery

In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.

In JOINs RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.

The good thing in sub-queries is that they are more readable than JOINs: that’s why most new SQL people prefer them; it is the easy way; but when it comes to performance, JOINS are better in most cases even though they are not hard to read too.

Conclusion

I am not building something new but assembled all the questions and answers related to subquery which keeps on bugging me day and night. I hope this will be useful for people looking for answers at one place.please mail me at admin@codespread.com

Author: sush

Share This Post On

6 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

More from CodeSpread:

  • Few lines about SQL Use statementFew lines about SQL Use statement We see this statement in Management Studio now and then and it is used quite extensively. For Example, [code] USE MyDatabaseName GO [/code] MyDatabaseName : MyDatabaseName is the n...
  • Transact-SQL : Try-Catch BlockTransact-SQL : Try-Catch BlockSimilar to exception-handling features of .Net, T-SQL code provides a TRY-CATCH block. It is one of the implicit features of T-SQL and a famous error handling technique to catch errors. A TRY-CA...
  • SQL: Common Table Expression called CTESQL: Common Table Expression called CTEI remember somebody talked about CTE or Common Table Expression with me and tried helplessly to make me understand it. Honestly, I listened very peacefully but was not able to figure out the exact ...
  • SQL : Clustered and NonClustered IndexSQL : Clustered and NonClustered IndexWe read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL. We have clustered and non-clustered index in SQL, let’s see...
  • Verify existence of SQL tables using Object_ID()Verify existence of SQL tables using Object_ID()Why Object_ID() ? Let’s create a SQL table “Employee” [code] CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NUL...
  • SQL: Story of IndexesSQL: Story of IndexesI know many people know everything about this topic but as this is one of my favorite topics, I will make sure that I will give you some additional information or knowledge. My first thought abo...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • SQL:Target a TriggerSQL:Target a TriggerI remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver,...