Using subquery as table in the FROM clause


There are times when you need to use a subquery in a FROM clause. This article outlines the correct way of using a subquery in the form clause so that you do not get an common syntax error while doing so.

There are times when you need to use a subquery in a FROM clause. This article outlines the correct way of using a
subquery in the form clause so that you do not get an common syntax error while doing so. If you don't use alias with such subqueries you will get an syntax error.

For example create a table as following:


create table T1
(
col1 varchar(20),
col2 datetime
)


Now insert some data into T1 as following:

insert into T1 (col1,col2)
values
('aa','2014-12-01'),
('bb','2014-12-10'),
('cc','2014-01-02')


Now lets say I want to use subquery in the FROM clause as shown below:

Select * from
(
select col1, max(col2) as maxcol2 from T1 group by col1
)


If you execute the above query you will get an syntax error message.

The correct way is that we need to add an alias to the subquery as shown in the below query:

Select * from
(
select col1, max(col2) as maxcol2 from T1 group by col1
) t


Above query will run successfully and will not give syntax error, as we have used alias "t" with subquery.

You can also join multiple subqueries as long as they have an alias. for example:

Select t1.col1, t1.maxcol2, t2.mincol2 from
(
select col1, max(col2) as maxcol2 from T1 group by col1
) t1
left join
(
select col1, min(col2) as mincol2 from T1 group by col1
) t2
on t1.col1 = t2.col2


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: