Follow Me : https://www.youtube.com/c/SauravAgarwal
○ We can also have a subquery inside of another subquery and so on. This is called a nested
Subquery. Maximum one can have is 32 levels of nested Sub-Queries.
15. What are the SET Operators?
○ SQL set operators allows you to combine results from two or more SELECT statements.
○ Syntax:
SELECT Col1, Col2, Col3 FROM T1
SELECT Col1, Col2, Col3 FROM T2
○ Rule 1: The number of columns in first SELECT statement must be same as the number of columns
in the second SELECT statement.
○ Rule 2: The metadata of all the columns in first SELECT statement MUST be exactly same as the
metadata of all the columns in second SELECT statement accordingly.
○ Rule 3: ORDER BY clause do not work with first SELECT statement. ○ UNION, UNION ALL,
INTERSECT, EXCEPT
16. What is a derived table?
○ SELECT statement that is given an alias name and can now be treated as a virtual table and
operations like joins, aggregations, etc. can be performed on it like on an actual table.
○ Scope is query bound, that is a derived table exists only in the query in which it was defined.
SELECT temp1.SalesOrderID, temp1.TotalDue FROM
(SELECT TOP 3 SalesOrderID, TotalDue FROM Sales.SalesOrderHeader ORDER BY TotalDue
DESC) AS temp1 LEFT OUTER JOIN
(SELECT TOP 2 SalesOrderID, TotalDue FROM Sales.SalesOrderHeader ORDER BY TotalDue
DESC) AS temp2 ON temp1.SalesOrderID = temp2.SalesOrderID WHERE temp2.SalesOrderID IS
NULL
Do'stlaringiz bilan baham: