SQL SERVER

Joins

Subqueries

EXISTS Predicate

Set Operators
UNION Operator
UNION ALL Operator
INTERSECT Operator
EXCEPT Operator
Precedence

Procedures






Subqueries
* subquery can b self-contained or correlated.

* self-contained has no dependency on tables of outer query
*


EXISTS
* accepts subquery as input
* returns TRUE if subquery returns any rows and FALSE otherwise.
* e.g. customers from spain who placesd orders:
                        SELECT custid, companyname

                        FROM Sales.Customers AS C

                        WHERE country = N'Spain'

                                        AND EXISTS
(SELECT *
                                                                 FROM Sales.Orders AS O

                                                                 WHERE O.custid = C.custid);



Returning previous or next values
SELECT  orderid
 , orderdate
 , empid
 , custid
 , (
   SELECT MAX(O2.orderid)
    FROM Sales.Orders AS O2
    WHERE O2.orderid < O1.orderid

  ) AS prevorderid
 FROM Sales.Orders AS O1;
SELECT  orderid
 , orderdate
 , empid
 , custid
 , (
   SELECT MIN(O2.orderid)
    FROM Sales.Orders AS O2
    WHERE O2.orderid > O1.orderid

  ) AS nextorderid
 FROM Sales.Orders AS O1;
=================================================================
Set Operators
* operators that combine rows from two query result sets (or multisets)


UNION







Procedures
* is a stored program that u can pass parameters into. [1]
* does not return a value like function [1]
* can return success/failure status to the procedure that called it. [1]


https://mva.microsoft.com/
https://technet.microsoft.com/en-us/
FAQ: Microsoft SQL Server Data Tools [https://msdn.microsoft.com/en-us/data/hh322942]