Lesson #6 Working With SubQueries Posted by Unknown , Wednesday, June 8, 2011 at 9:21 PM, in Labels: Query

subquery vs join :

1. join menujukkan performa lebih cepat dari subquery dalam beberapa kasus namun eksistensi harus dicek
2. subquery lebih berguna untuk menjawab pertanyaan yang terlalu kompleks dijawab dengan join
3. subquery lebih cocok jika RAM kita tinggi karena terjadi adi memory
4. subquery lebih mudah di maintain daripada pada temporary table yang membutuhkan resources harddisk ketika dieksekusi
5. Gunakan temporary table untuk memecah sub query yang kompleks

contoh subquery vs join :

/* SELECT statement built using a subquery. */
SELECT Name
FROM Production.Product
WHERE ListPrice =
    (SELECT ListPrice
     FROM Production.Product
     WHERE Name = 'Chainring Bolts' )

/* SELECT statement built using a join that returns the same result
set. */
SELECT Prd1.Name
FROM Production.Product AS Prd1
     JOIN Production.Product AS Prd2
       ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.Name = 'Chainring Bolts';

contoh sub query dengan EXIST keyword :

SELECT Name
FROM Production.Product
WHERE EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND Name = 'Wheels'); 

1. local temporary table : memiliki single number sign (#), muncul hanya pada koneksi yang sekarang digunakan user, dan terhapus ketika user diskoneksi
2. global temporary table :  memiliki double number sign (##), muncul pada seluruh user dan terhapus ketika diskoneksi

contoh create temporary table :

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);
INSERT INTO #MyTempTable VALUES (1);
SELECT * FROM #MyTempTable; 

CET : common expression table --> temporary result yang didapat dari select query dan dapat digunakan dalam select, insert, update, atau delete

keuntungan penggunaan CET adalah : query yang diturunkan dari table mudah dibaca dan menyediakan traversal hieraki recursive

contoh CET yang recursive :

USE AdventureWorks2008;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate,
ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID,
p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO 

contoh Store prosedure:



CREATE Procedure pGetPersonName
 @PersonID nvarchar(15)
AS
 SELECT BusinessEntityID,
   FirstName,
   MiddleName,
   LastName
  FROM dbo.vwPersonName
  WHERE BusinessEntityID = @PersonID
GO