Contoh SELECT (Transact-SQL)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Artikel ini menyediakan contoh penggunaan pernyataan SELECT .

Artikel ini memerlukan AdventureWorks2022 database sampel, yang dapat Anda unduh dari beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

J. Menggunakan SELECT untuk mengambil baris dan kolom

Contoh berikut menunjukkan tiga contoh kode. Contoh kode pertama ini mengembalikan semua baris (tidak ada klausa WHERE yang ditentukan) dan semua kolom (menggunakan *) dari Product tabel dalam AdventureWorks2022 database.

USE AdventureWorks2022;
GO

SELECT *
FROM Production.Product
ORDER BY Name ASC;

-- Alternate way.
USE AdventureWorks2022;
GO

SELECT p.*
FROM Production.Product AS p
ORDER BY Name ASC;
GO

Contoh ini mengembalikan semua baris (tidak ada klausa WHERE yang ditentukan), dan hanya subset kolom (Name, , ProductNumberListPrice) dari Product tabel dalam AdventureWorks2022 database. Selain itu, judul kolom ditambahkan.

USE AdventureWorks2022;
GO

SELECT Name,
    ProductNumber,
    ListPrice AS Price
FROM Production.Product
ORDER BY Name ASC;
GO

Contoh ini hanya mengembalikan baris untuk Product yang memiliki lini R produk dan yang memiliki hari untuk diproduksi yang kurang dari 4.

USE AdventureWorks2022;
GO

SELECT Name,
    ProductNumber,
    ListPrice AS Price
FROM Production.Product
WHERE ProductLine = 'R'
    AND DaysToManufacture < 4
ORDER BY Name ASC;
GO

B. Menggunakan SELECT dengan judul dan perhitungan kolom

Contoh berikut mengembalikan semua baris dari Product tabel. Contoh pertama mengembalikan total penjualan dan diskon untuk setiap produk. Dalam contoh kedua, total pendapatan dihitung untuk setiap produk.

USE AdventureWorks2022;
GO

SELECT p.Name AS ProductName,
    NonDiscountSales = (OrderQty * UnitPrice),
    Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID
ORDER BY ProductName DESC;
GO

Ini adalah kueri yang menghitung pendapatan untuk setiap produk di setiap pesanan penjualan.

USE AdventureWorks2022;
GO

SELECT 'Total income is',
    ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)),
    ' for ',
    p.Name AS ProductName
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID
ORDER BY ProductName ASC;
GO

C. Gunakan DISTINCT dengan SELECT

Contoh berikut menggunakan DISTINCT untuk mencegah pengambilan judul duplikat.

USE AdventureWorks2022;
GO

SELECT DISTINCT JobTitle
FROM HumanResources.Employee
ORDER BY JobTitle;
GO

D. Membuat tabel dengan SELECT INTO

Contoh pertama berikut membuat tabel sementara bernama #Bicycles di tempdb.

USE tempdb;
GO

IF OBJECT_ID(N'#Bicycles', N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO

SELECT *
INTO #Bicycles
FROM AdventureWorks2022.Production.Product
WHERE ProductNumber LIKE 'BK%';
GO

Contoh kedua ini membuat tabel NewProductspermanen .

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL
DROP TABLE dbo.NewProducts;
GO

ALTER DATABASE AdventureWorks2022 SET RECOVERY BULK_LOGGED;
GO

SELECT *
INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25
    AND ListPrice < $100;
GO

ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL;
GO

E. Menggunakan subkueri berkorelasi

Subkueri yang berkorelasi adalah kueri yang bergantung pada kueri luar untuk nilainya. Kueri ini dapat dijalankan berulang kali, satu kali untuk setiap baris yang dapat dipilih oleh kueri luar.

Contoh pertama menunjukkan kueri yang setara secara semantik untuk menggambarkan perbedaan antara menggunakan EXISTS kata kunci dan IN kata kunci. Keduanya adalah contoh subkueri valid yang mengambil satu instans dari setiap nama produk yang model produknya adalah jersey logo lengan panjang, dan ProductModelID angkanya cocok antara Product tabel dan ProductModel .

USE AdventureWorks2022;
GO

SELECT DISTINCT Name
FROM Production.Product AS p
WHERE EXISTS (
    SELECT *
    FROM Production.ProductModel AS pm
    WHERE p.ProductModelID = pm.ProductModelID
        AND pm.Name LIKE 'Long-Sleeve Logo Jersey%'
);
GO

-- OR
USE AdventureWorks2022;
GO

SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN (
    SELECT ProductModelID
    FROM Production.ProductModel AS pm
    WHERE p.ProductModelID = pm.ProductModelID
        AND Name LIKE 'Long-Sleeve Logo Jersey%'
);
GO

Contoh berikutnya menggunakan IN dan mengambil satu instans nama depan dan nama keluarga setiap karyawan di mana bonus dalam SalesPerson tabel adalah 5000.00, dan di mana nomor identifikasi karyawan cocok dalam Employee tabel dan SalesPerson .

USE AdventureWorks2022;
GO

SELECT DISTINCT p.LastName,
    p.FirstName
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = p.BusinessEntityID
WHERE 5000.00 IN (
    SELECT Bonus
    FROM Sales.SalesPerson AS sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID
);
GO

Subkueri sebelumnya dalam pernyataan ini tidak dapat dievaluasi secara independen dari kueri luar. Ini memerlukan nilai untuk Employee.EmployeeID, tetapi nilai ini berubah saat Mesin Database SQL Server memeriksa baris yang berbeda di Employee.

Subkueri yang berkorelasi juga dapat digunakan dalam HAVING klausul kueri luar. Contoh ini menemukan model produk yang harga daftar maksimumnya lebih dari dua kali rata-rata untuk model.

USE AdventureWorks2022;
GO

SELECT p1.ProductModelID
FROM Production.Product AS p1
GROUP BY p1.ProductModelID
HAVING MAX(p1.ListPrice) >= (
    SELECT AVG(p2.ListPrice) * 2
    FROM Production.Product AS p2
    WHERE p1.ProductModelID = p2.ProductModelID
);
GO

Contoh ini menggunakan dua subkueri yang berkorelasi untuk menemukan nama karyawan yang menjual produk tertentu.

USE AdventureWorks2022;
GO

SELECT DISTINCT pp.LastName,
    pp.FirstName
FROM Person.Person pp
INNER JOIN HumanResources.Employee e
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE pp.BusinessEntityID IN (
    SELECT SalesPersonID
    FROM Sales.SalesOrderHeader
    WHERE SalesOrderID IN (
        SELECT SalesOrderID
        FROM Sales.SalesOrderDetail
        WHERE ProductID IN (
            SELECT ProductID
            FROM Production.Product p
            WHERE ProductNumber = 'BK-M68B-42'
        )
    )
);
GO

F. Gunakan GROUP BY

Contoh berikut menemukan total setiap pesanan penjualan dalam database.

USE AdventureWorks2022;
GO

SELECT SalesOrderID,
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID;
GO

Karena klausa GROUP BY , hanya satu baris yang berisi jumlah semua penjualan yang dikembalikan untuk setiap pesanan penjualan.

G. Menggunakan GROUP BY dengan beberapa grup

Contoh berikut menemukan harga rata-rata dan jumlah penjualan tahunan hingga saat ini, dikelompokkan menurut ID produk dan ID penawaran khusus.

USE AdventureWorks2022;
GO

SELECT ProductID,
    SpecialOfferID,
    AVG(UnitPrice) AS [Average Price],
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID,
    SpecialOfferID
ORDER BY ProductID;
GO

H. Gunakan GROUP BY dan WHERE

Contoh berikut menempatkan hasilnya ke dalam grup setelah mengambil hanya baris dengan harga daftar yang lebih besar dari $1000.

USE AdventureWorks2022;
GO

SELECT ProductModelID,
    AVG(ListPrice) AS [Average List Price]
FROM Production.Product
WHERE ListPrice > $1000
GROUP BY ProductModelID
ORDER BY ProductModelID;
GO

I. Menggunakan GROUP BY dengan ekspresi

Contoh berikut mengelompokkan menurut ekspresi. Anda dapat mengelompokkan menurut ekspresi jika ekspresi tidak menyertakan fungsi agregat.

USE AdventureWorks2022;
GO

SELECT AVG(OrderQty) AS [Average Quantity],
    NonDiscountSales = (OrderQty * UnitPrice)
FROM Sales.SalesOrderDetail
GROUP BY (OrderQty * UnitPrice)
ORDER BY (OrderQty * UnitPrice) DESC;
GO

j. Menggunakan GROUP BY dengan ORDER BY

Contoh berikut menemukan harga rata-rata setiap jenis produk dan memesan hasilnya berdasarkan harga rata-rata.

USE AdventureWorks2022;
GO

SELECT ProductID,
    AVG(UnitPrice) AS [Average Price]
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY AVG(UnitPrice);
GO

K. Menggunakan klausa HAVING

Contoh pertama yang mengikuti menunjukkan HAVING klausul dengan fungsi agregat. Ini mengelompokkan baris dalam SalesOrderDetail tabel berdasarkan ID produk dan menghilangkan produk yang jumlah pesanan rata-ratanya lima atau kurang. Contoh kedua menunjukkan HAVING klausa tanpa fungsi agregat.

USE AdventureWorks2022;
GO

SELECT ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO

Kueri ini menggunakan klausa LIKE dalam HAVING klausa.

USE AdventureWorks2022;
GO
SELECT SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, CarrierTrackingNumber
HAVING CarrierTrackingNumber LIKE '4BD%'
ORDER BY SalesOrderID ;
GO

L. Menggunakan HAVING dan GROUP BY

Contoh berikut menunjukkan penggunaan GROUP BYklausa , HAVING, WHERE, dan ORDER BY dalam satu SELECT pernyataan. Ini menghasilkan grup dan nilai ringkasan tetapi melakukannya setelah menghilangkan produk dengan harga lebih dari $ 25 dan jumlah pesanan rata-rata di bawah 5. Ini juga mengatur hasilnya dengan ProductID.

USE AdventureWorks2022;
GO

SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE UnitPrice < 25.00
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO

M. Menggunakan HAVING dengan SUM dan AVG

Contoh berikut mengelompokkan SalesOrderDetail tabel menurut ID produk dan hanya menyertakan grup produk yang memiliki pesanan yang berjumlah lebih dari $1000000.00 dan yang jumlah pesanan rata-ratanya kurang dari 3.

USE AdventureWorks2022;
GO

SELECT ProductID,
    AVG(OrderQty) AS AverageQuantity,
    SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $1000000.00
    AND AVG(OrderQty) < 3;
GO

Untuk melihat produk dengan total penjualan yang lebih besar dari $2000000.00, gunakan kueri ini:

USE AdventureWorks2022;
GO

SELECT ProductID, Total = SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00;
GO

Jika Anda ingin memastikan setidaknya ada 1.500 item yang terlibat dalam perhitungan untuk setiap produk, gunakan HAVING COUNT(*) > 1500 untuk menghilangkan produk yang mengembalikan total kurang dari 1500 item yang dijual. Kueri terlihat seperti ini:

USE AdventureWorks2022;
GO

SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500;
GO

N. Menggunakan petunjuk pengoptimal INDEKS

Contoh berikut menunjukkan dua cara untuk menggunakan petunjuk pengoptimal INDEX . Contoh pertama menunjukkan cara memaksa pengoptimal untuk menggunakan indeks non-klusifikasi untuk mengambil baris dari tabel. Contoh kedua memaksa pemindaian tabel dengan menggunakan indeks 0.

USE AdventureWorks2022;
GO

SELECT pp.FirstName,
    pp.LastName,
    e.NationalIDNumber
FROM HumanResources.Employee AS e WITH (INDEX (AK_Employee_NationalIDNumber))
INNER JOIN Person.Person AS pp
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

-- Force a table scan by using INDEX = 0.
USE AdventureWorks2022;
GO

SELECT pp.LastName,
    pp.FirstName,
    e.JobTitle
FROM HumanResources.Employee AS e WITH (INDEX = 0)
INNER JOIN Person.Person AS pp
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

M. Menggunakan OPTION dan petunjuk GRUP

Contoh berikut menunjukkan bagaimana OPTION (GROUP) klausa digunakan dengan GROUP BY klausa.

USE AdventureWorks2022;
GO

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO

O. Menggunakan petunjuk kueri UNION

Contoh berikut menggunakan MERGE UNION petunjuk kueri.

USE AdventureWorks2022;
GO

SELECT BusinessEntityID,
    JobTitle,
    HireDate,
    VacationHours,
    SickLeaveHours
FROM HumanResources.Employee AS e1

UNION

SELECT BusinessEntityID,
    JobTitle,
    HireDate,
    VacationHours,
    SickLeaveHours
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

P. Menggunakan UNION

Dalam contoh berikut, kumpulan hasil menyertakan konten ProductModelID kolom dan Name tabel ProductModel dan Gloves .

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO

-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

-- Here is the simple union.
USE AdventureWorks2022;
GO

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

T. Gunakan SELECT INTO dengan UNION

Dalam contoh berikut, INTO klausul dalam pernyataan kedua SELECT menentukan bahwa tabel bernama ProductResults menyimpan kumpulan hasil akhir dari penyatuan kolom yang ditunjuk dari ProductModel tabel dan Gloves . Tabel Gloves dibuat dalam pernyataan pertama SELECT .

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.ProductResults', 'U') IS NOT NULL
DROP TABLE dbo.ProductResults;
GO

IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO

-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

USE AdventureWorks2022;
GO

SELECT ProductModelID, Name
INTO dbo.ProductResults
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name
FROM dbo.Gloves;
GO

SELECT ProductModelID, Name
FROM dbo.ProductResults;

R. Menggunakan UNION dari dua pernyataan SELECT dengan ORDER BY

Urutan parameter tertentu yang digunakan dengan klausul UNION penting. Contoh berikut menunjukkan penggunaan UNION yang salah dan benar dalam dua SELECT pernyataan di mana kolom akan diganti namanya dalam output.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO

-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

/* INCORRECT */
USE AdventureWorks2022;
GO

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
ORDER BY Name

UNION

SELECT ProductModelID, Name
FROM dbo.Gloves;
GO

/* CORRECT */
USE AdventureWorks2022;
GO

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

S. Gunakan UNION dari tiga pernyataan SELECT untuk memperlihatkan efek SEMUA dan tanda kurung

Contoh berikut digunakan UNION untuk menggabungkan hasil tiga tabel yang semuanya memiliki lima baris data yang sama. Contoh pertama menggunakan UNION ALL untuk menampilkan rekaman duplikat, dan mengembalikan semua 15 baris. Contoh kedua menggunakan UNION tanpa ALL untuk menghilangkan baris duplikat dari hasil gabungan dari tiga SELECT pernyataan, dan mengembalikan lima baris.

Contoh ketiga menggunakan ALL dengan tanda kurung pertama UNION dan mengapit yang kedua UNION yang tidak menggunakan ALL. Yang kedua UNION diproses terlebih dahulu karena dalam tanda kurung, dan mengembalikan lima baris karena ALL opsi tidak digunakan dan duplikat dihapus. Kelima baris ini dikombinasikan dengan hasil yang pertama SELECT dengan menggunakan UNION ALL kata kunci. Contoh ini tidak menghapus duplikat antara dua set lima baris. Hasil akhir memiliki 10 baris.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.EmployeeOne', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeOne;
GO

IF OBJECT_ID('dbo.EmployeeTwo', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeTwo;
GO

IF OBJECT_ID('dbo.EmployeeThree', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeThree;
GO

SELECT pp.LastName, pp.FirstName, e.JobTitle
INTO dbo.EmployeeOne
FROM Person.Person AS pp
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

SELECT pp.LastName, pp.FirstName, e.JobTitle
INTO dbo.EmployeeTwo
FROM Person.Person AS pp
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

SELECT pp.LastName, pp.FirstName, e.JobTitle
INTO dbo.EmployeeThree
FROM Person.Person AS pp
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

-- Union ALL
SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeOne

UNION ALL

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeTwo

UNION ALL

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeThree;
GO

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeOne

UNION

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeTwo

UNION

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeThree;
GO

SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeOne

UNION ALL

(
    SELECT LastName, FirstName, JobTitle
    FROM dbo.EmployeeTwo

    UNION

    SELECT LastName, FirstName, JobTitle
    FROM dbo.EmployeeThree
);
GO

Konten terkait