SQL Subquery interview questions and answers
Here are some beginner to intermediate level SQL interview questions and answers. This post compliments my other posts.
- SQL Interview Questions and Answers
- SQL interview questions and answers - scenarios based
- SQL Interview Questions and Answers on deleting records
- SQL Interview Questions and Answers: storing a tree structure in a database table
- Excel spreadsheet to generate SQL
- SQL Tutorial with HSQLDB
Q. What is a subquery?
A. Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE clause of the sql statement. A subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. Subqueries are an alternate way of returning data from multiple tables.
Q. Can you create a subquery in a From clause?
A. Yes. Subqueries can be used in From, Where and Having clauses. For example, in Sybase
select * from ( select 'A' as colVal union select 'B' as colVal ) dataReturns:
colVal ------ A B
Joining virtual tables is one of the most powerful feature of subqueries. Virtual in this context means the result set you are joining is built on the fly. Here is a more advanced example:
declare @clientId varchar(30), @reportDate date, set nocount on select reportId from Report_Status s, ReportKey k, ReportGroupKey gk, --subquery in from clause (select max(s.createddttm) as maxdate, k1.clientId from Report_Status s, ReportKey k1, ReportGroupKey gk where k1.InactiveFlag ='N' and gk.InactiveFlag ='N' and gk.KeyId = k1.Id and gk.Id = s.GroupKeyId group by k1.clientId ) maxdates where k.InactiveFlag ='N' and gk.InactiveFlag ='N' and gk.KeyId = k.Id and gk.Id = s.GroupKeyId and s.CreatedDtTm = maxdates.maxdate and k.ClientId = @clientId and maxdates.ClientId = k.ClientId and k.reportDate = @reportDate
Q.What is a correlated subquery?
A. A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.
SELECT outer.product_name FROM product outer WHERE outer.product_id = (SELECT inner.product_id FROM order_items inner WHERE outer.product_id = inner.product_id);
If a subquery is not dependent on the outer query it is called a non-correlated subquery.
Q. What are the advantages and disadvantages of using a subquery?
A.
Advantages:
- Subqueries allow you to use the results of another query in the outer query.
- Subqueries in some complex SQL queries can simplify coding and improve maintainability by breaking down the complex query into a series of logical steps.
- In some cases, subqueries are easier to understand than complex joins and unions.
Disadvantages:
- When a subquery is used, the query optimizer of the database server may have to perform additional steps like sorting the results, etc. Hence, in some cases subqueries can be less efficient than using joins. So, favor joins to subqueries.
You may also like:
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home