CASE (Transact-SQL)

Artikel
06/24/2022
8 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Mengevaluasi daftar kondisi dan mengembalikan salah satu dari beberapa kemungkinan ekspresi hasil.

Ekspresi CASE memiliki dua format:

Ekspresi CASE sederhana membandingkan ekspresi dengan sekumpulan ekspresi sederhana untuk menentukan hasilnya.
Ekspresi CASE yang dicari mengevaluasi sekumpulan ekspresi Boolean untuk menentukan hasilnya.

Kedua format mendukung argumen ELSE opsional.

CASE dapat digunakan dalam pernyataan atau klausul apa pun yang memungkinkan ekspresi yang valid. Misalnya, Anda dapat menggunakan CASE dalam pernyataan seperti SELECT, UPDATE, DELETE dan SET, dan dalam klausa seperti select_list, IN, WHERE, ORDER BY, dan HAVING.

Topic link icon Konvensi Sintaksis T-SQL

Sintaks

-- Syntax for SQL Server, Azure SQL Database and Azure Synapse Analytics
  
--Simple CASE expression:   
CASE input_expression   
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END   

--Searched CASE expression:  
CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

-- Syntax for Parallel Data Warehouse  
  
CASE  
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

input_expression
Adalah ekspresi yang dievaluasi ketika format CASE sederhana digunakan. input_expression adalah ekspresi yang valid.

SAAT when_expression
Adalah ekspresi sederhana yang input_expression dibandingkan ketika format CASE sederhana digunakan. when_expression adalah ekspresi yang valid. Jenis data input_expression dan setiap when_expression harus sama atau harus merupakan konversi implisit.

LALU result_expression
Apakah ekspresi dikembalikan saat input_expression sama dengan when_expression dievaluasi ke TRUE, atau Boolean_expression dievaluasi ke TRUE. ekspresi hasil adalah ekspresi yang valid.

else_result_expression ELSE
Apakah ekspresi dikembalikan jika tidak ada operasi perbandingan yang dievaluasi ke TRUE. Jika argumen ini dihilangkan dan tidak ada operasi perbandingan yang dievaluasi ke TRUE, CASE mengembalikan NULL. else_result_expression adalah ekspresi yang valid. Jenis data else_result_expression dan result_expression apa pun harus sama atau harus merupakan konversi implisit.

SAAT Boolean_expression
Apakah ekspresi Boolean dievaluasi saat menggunakan format CASE yang dicari. Boolean_expression adalah ekspresi Boolean yang valid.

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Jenis Kembalian

Mengembalikan jenis prioritas tertinggi dari kumpulan jenis dalam result_expressions dan else_result_expression opsional. Untuk informasi selengkapnya, lihat Prioritas Tipe Data (Transact-SQL).

Nilai yang Dikembalikan

Ekspresi CASE sederhana:

Ekspresi CASE sederhana beroperasi dengan membandingkan ekspresi pertama dengan ekspresi di setiap klausa WHEN untuk kesetaraan. Jika ekspresi ini setara, ekspresi dalam klausa THEN akan dikembalikan.

Hanya mengizinkan pemeriksaan kesetaraan.
Dalam urutan yang ditentukan, mengevaluasi input_expression = when_expression untuk setiap klausa WHEN.
Mengembalikan result_expressionwhen_expressioninput_expression = pertama yang dievaluasi ke TRUE.
Jika tidak ada input_expression = when_expression yang dievaluasi ke TRUE, Mesin Database SQL Server mengembalikan else_result_expression jika klausa ELSE ditentukan, atau nilai NULL jika tidak ada klausa ELSE yang ditentukan.

Ekspresi CASE yang dicari:

Mengevaluasi, dalam urutan yang ditentukan, Boolean_expression untuk setiap klausa WHEN.
Mengembalikan result_expressionBoolean_expression pertama yang dievaluasi ke TRUE.
Jika tidak ada Boolean_expression yang dievaluasi ke TRUE, Mesin Database mengembalikan else_result_expression jika klausa ELSE ditentukan, atau nilai NULL jika tidak ada klausa ELSE yang ditentukan.

Keterangan

SQL Server hanya memungkinkan 10 tingkat bersarang dalam ekspresi CASE.

Ekspresi CASE tidak dapat digunakan untuk mengontrol alur eksekusi pernyataan Transact-SQL, blok pernyataan, fungsi yang ditentukan pengguna, dan prosedur tersimpan. Untuk daftar metode control-of-flow, lihat Control-of-Flow Language (Transact-SQL).

Ekspresi CASE mengevaluasi kondisinya secara berurutan dan berhenti dengan kondisi pertama yang kondisinya terpenuhi. Dalam beberapa situasi, ekspresi dievaluasi sebelum ekspresi CASE menerima hasil ekspresi sebagai inputnya. Kesalahan dalam mengevaluasi ekspresi ini dimungkinkan. Ekspresi agregat yang muncul dalam argumen WHEN ke ekspresi CASE dievaluasi terlebih dahulu, lalu diberikan ke ekspresi CASE. Misalnya, kueri berikut menghasilkan kesalahan dibagi dengan nol saat menghasilkan nilai agregat MAX. Ini terjadi sebelum mengevaluasi ekspresi CASE.

WITH Data (value) AS   
(   
SELECT 0   
UNION ALL   
SELECT 1   
)   
SELECT   
   CASE   
      WHEN MIN(value) <= 0 THEN 0   
      WHEN MAX(1/value) >= 100 THEN 1   
   END   
FROM Data ;

Anda hanya boleh bergantung pada urutan evaluasi kondisi WHEN untuk ekspresi skalar (termasuk sub-kueri yang tidak berkorelasi yang mengembalikan skalar), bukan untuk ekspresi agregat.

Contoh

A. Menggunakan pernyataan SELECT dengan ekspresi CASE sederhana

Dalam pernyataan SELECT , ekspresi sederhana CASE hanya memungkinkan pemeriksaan kesetaraan; tidak ada perbandingan lain yang dibuat. Contoh berikut menggunakan CASE ekspresi untuk mengubah tampilan kategori baris produk agar lebih mudah dipahami.

USE AdventureWorks2012;  
GO  
SELECT   ProductNumber, Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   Name  
FROM Production.Product  
ORDER BY ProductNumber;  
GO

B. Menggunakan pernyataan SELECT dengan ekspresi CASE yang dicari

SELECT Dalam pernyataan, ekspresi yang dicari CASE memungkinkan nilai diganti dalam tataan hasil berdasarkan nilai perbandingan. Contoh berikut menampilkan harga daftar sebagai komentar teks berdasarkan rentang harga untuk produk.

USE AdventureWorks2012;  
GO  
SELECT   ProductNumber, Name, "Price Range" =   
      CASE   
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'  
         WHEN ListPrice < 50 THEN 'Under $50'  
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'  
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'  
         ELSE 'Over $1000'  
      END  
FROM Production.Product  
ORDER BY ProductNumber ;  
GO

C. Menggunakan CASE dalam klausa ORDER BY

Contoh berikut menggunakan ekspresi CASE dalam klausul ORDER BY untuk menentukan susunan urutan baris berdasarkan nilai kolom tertentu. Dalam contoh pertama, nilai dalam SalariedFlag kolom HumanResources.Employee tabel dievaluasi. Karyawan yang memiliki SalariedFlag set ke 1 dikembalikan sesuai BusinessEntityID urutan turun. Karyawan yang memiliki SalariedFlag set ke 0 dikembalikan sesuai BusinessEntityID urutan dalam urutan naik. Dalam contoh kedua, tataan hasil diurutkan menurut kolom TerritoryName saat kolom CountryRegionName sama dengan 'Amerika Serikat' dan oleh CountryRegionName untuk semua baris lainnya.

SELECT BusinessEntityID, SalariedFlag  
FROM HumanResources.Employee  
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC  
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;  
GO

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL  
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName  
         ELSE CountryRegionName END;

D. Menggunakan CASE dalam pernyataan UPDATE

Contoh berikut menggunakan ekspresi CASE dalam pernyataan UPDATE untuk menentukan nilai yang diatur untuk kolom VacationHours untuk karyawan dengan SalariedFlag diatur ke 0. Ketika mengurangi 10 jam dari VacationHours menghasilkan nilai negatif, VacationHours ditingkatkan 40 jam; jika tidak, VacationHours ditingkatkan 20 jam. Klausa OUTPUT digunakan untuk menampilkan nilai sebelum dan sesudah liburan.

USE AdventureWorks2012;  
GO  
UPDATE HumanResources.Employee  
SET VacationHours =   
    ( CASE  
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40  
         ELSE (VacationHours + 20.00)  
       END  
    )  
OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,   
       Inserted.VacationHours AS AfterValue  
WHERE SalariedFlag = 0;

E. Menggunakan CASE dalam pernyataan SET

Contoh berikut menggunakan ekspresi CASE dalam pernyataan SET dalam fungsi dbo.GetContactInfobernilai tabel . Dalam database AdventureWorks2012 , semua data yang terkait dengan orang disimpan dalam Person.Person tabel. Misalnya, orang tersebut mungkin karyawan, perwakilan vendor, atau pelanggan. Fungsi mengembalikan nama depan dan belakang dari jenis kontak yang diberikan BusinessEntityID dan untuk orang tersebut. Ekspresi CASE dalam pernyataan SET menentukan nilai yang akan ditampilkan untuk kolom ContactType berdasarkan keberadaan BusinessEntityID kolom dalam Employeetabel , Vendor, atau Customer .

USE AdventureWorks2012;  
GO  
CREATE FUNCTION dbo.GetContactInformation(@BusinessEntityID INT)  
    RETURNS @retContactInformation TABLE   
(  
BusinessEntityID INT NOT NULL,  
FirstName NVARCHAR(50) NULL,  
LastName NVARCHAR(50) NULL,  
ContactType NVARCHAR(50) NULL,  
    PRIMARY KEY CLUSTERED (BusinessEntityID ASC)  
)   
AS   
-- Returns the first name, last name and contact type for the specified contact.  
BEGIN  
    DECLARE   
        @FirstName NVARCHAR(50),   
        @LastName NVARCHAR(50),   
        @ContactType NVARCHAR(50);  
  
    -- Get common contact information  
    SELECT   
        @BusinessEntityID = BusinessEntityID,   
@FirstName = FirstName,   
        @LastName = LastName  
    FROM Person.Person   
    WHERE BusinessEntityID = @BusinessEntityID;  
  
    SET @ContactType =   
        CASE   
            -- Check for employee  
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e   
                WHERE e.BusinessEntityID = @BusinessEntityID)   
                THEN 'Employee'  
  
            -- Check for vendor  
            WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec  
                WHERE bec.BusinessEntityID = @BusinessEntityID)   
                THEN 'Vendor'  
  
            -- Check for store  
            WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v            
                WHERE v.BusinessEntityID = @BusinessEntityID)   
                THEN 'Store Contact'  
  
            -- Check for individual consumer  
            WHEN EXISTS(SELECT * FROM Sales.Customer AS c   
                WHERE c.PersonID = @BusinessEntityID)   
                THEN 'Consumer'  
        END;  
  
    -- Return the information to the caller  
    IF @BusinessEntityID IS NOT NULL   
    BEGIN  
        INSERT @retContactInformation  
        SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType;  
    END;  
  
    RETURN;  
END;  
GO  
  
SELECT BusinessEntityID, FirstName, LastName, ContactType  
FROM dbo.GetContactInformation(2200);  
GO  
SELECT BusinessEntityID, FirstName, LastName, ContactType  
FROM dbo.GetContactInformation(5);

F. Menggunakan CASE dalam klausa HAVING

Contoh berikut menggunakan ekspresi CASE dalam klausa HAVING untuk membatasi baris yang dikembalikan oleh pernyataan SELECT. Pernyataan mengembalikan tarif per jam maksimum untuk setiap jabatan HumanResources.Employee dalam tabel. Klausul HAVING membatasi judul untuk mereka yang dipegang oleh pria dengan tingkat pembayaran maksimum lebih besar dari dolar 40 atau wanita dengan tingkat pembayaran maksimum lebih besar dari dolar 42.

USE AdventureWorks2012;  
GO  
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate  
FROM HumanResources.Employee AS e  
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID  
GROUP BY JobTitle  
HAVING (MAX(CASE WHEN Gender = 'M'   
        THEN ph1.Rate   
        ELSE NULL END) > 40.00  
     OR MAX(CASE WHEN Gender  = 'F'   
        THEN ph1.Rate    
        ELSE NULL END) > 42.00)  
ORDER BY MaximumRate DESC;

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

G. Menggunakan pernyataan SELECT dengan ekspresi CASE

Dalam pernyataan SELECT, ekspresi CASE memungkinkan nilai diganti dalam tataan hasil berdasarkan nilai perbandingan. Contoh berikut menggunakan ekspresi CASE untuk mengubah tampilan kategori baris produk agar lebih mudah dipahami. Ketika nilai tidak ada, teks "Tidak dijual' ditampilkan.

-- Uses AdventureWorks  
  
SELECT   ProductAlternateKey, Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   EnglishProductName  
FROM dbo.DimProduct  
ORDER BY ProductKey;

H. Menggunakan CASE dalam pernyataan UPDATE

-- Uses AdventureWorks   
  
UPDATE dbo.DimEmployee  
SET VacationHours =   
    ( CASE  
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40  
         ELSE (VacationHours + 20.00)   
       END  
    )   
WHERE SalariedFlag = 0;

Lihat juga

Ekspresi (Transact-SQL)
SELECT (Transact-SQL)
COALESCE (Transact-SQL)
IIF (Transact-SQL)
PILIH (Transact-SQL)