I am still new to TD (9 months) but am getting more involved with writing code instead of making simple changes. I am sure this question has been asked before, but I was unable to find an absolute answer.
Along with this forum I was reading http://www.coffingdw.com/sql/tdsqlutp/derived_tables.htm
I am putting together some cheat sheets for my colleagues on a variety of subjects, including one on derived tables.
With this code...
FROM (SELECT ,a,b,c FROM table1) AS tbl1 (ca, cab, cc)
...the result will always be a derived table, correct?
Therefore, whenever a SELECT is in the FROM clause of an outer SELECT, the result will always be a derived table? Whereas when a SELECT is located in the WHERE clause it is a correlated subquery? No exceptions?
Thanks and God Bless,
The example that you have shown is perfect example of derived table. The corelated subquery is different. If the SELECT is loacted in WHERE clause then it is subquery and not corelated sub query. In the corelated sub query the select will have some columns from the main query, and if you run alone the subquery it will give error like column does not exist.
SEL * FROM EMP WHERE SALARY=(SEL MAX(SALARY) FROM EMP ); -> example of sub query
SEL * FROM (SEL c1,c2 FROM EMP)dt INNER JOIN DEPT ON EMP.C3=DEPT.C3 WHERE DEPT.C3 IN (SEL C1 FROM DT); -> example of corelated sub query.
The above example might not be correct by syntax.