CASE-instructie en geneste case in SQL Server: T-SQL-voorbeeld

Overzicht van Case in het echt!

Ook in het echte leven ondernemen we verschillende acties, afhankelijk van de uitkomst van verschillende omstandigheden. Om dit verder toe te lichten, neem het volgende voorbeeld:

• Als vliegtickets minder dan $ 100 kosten, dan ga ik naar Los Angeles.
• Als de vliegtickets tussen de 100 en 200 dollar kosten, dan ga ik naar New York.
• Als vliegtickets tussen de 200 en 400 dollar kosten, dan ga ik naar Europa.
• Anders ga ik liever naar een toeristische attractie in de buurt.

Laten we, los van het bovenstaande voorbeeld, de voorwaarde en de actie afzonderlijk categoriseren:

In het bovenstaande voorbeeld zien we dat de uitkomst van de verschillende voorwaarden een afzonderlijke actie bepaalt. De bezoeker zal bijvoorbeeld alleen naar New York gaan als het vliegticket tussen de $100 en $200 kost. Op dezelfde manier geldt dit ook voor de andere voorwaarden. MS SQL De CASE-instructie biedt ook de mogelijkheid om verschillende T-SQL-instructies uit te voeren op basis van de uitkomst van verschillende voorwaarden.

Wat is de CASE-instructie in SQL Server?

CASE-verklaring in SQL Server is de uitbreiding van de ALS... ANDERS In tegenstelling tot IF…ELSE, waarbij maximaal één voorwaarde is toegestaan, stelt CASE de gebruiker in staat om meerdere voorwaarden toe te passen om verschillende acties uit te voeren in MS SQL. Het retourneert een overeenkomstige waarde die is gekoppeld aan de door de gebruiker gedefinieerde voorwaarde.

Laten we leren hoe we CASE kunnen gebruiken in SQL en het concept ervan in de volgende secties. In MS SQL zijn er twee soorten CASE:

• Eenvoudig GEVAL
• CASE gezocht

Eenvoudig GEVAL

De syntaxis voor het eenvoudige geval

CASE <Case_Expression>
     WHEN Value_1 THEN Statement_1
     WHEN Value_2 THEN Statement_2
     .
     .
     WHEN Value_N THEN Statement_N
     [ELSE Statement_Else]   
END AS [ALIAS_NAME]

Hier:

• De parameter Case_Expression geeft de expressie aan die uiteindelijk vergeleken zal worden met Value_1, Value_2, enzovoort.
• De parameters Statement_1, Statement_2… geven aan welke instructies worden uitgevoerd als Case_Expression = Value_1, Case_Expression = Value_2, enzovoort.
• Kort gezegd is de voorwaarde of Case_Expression = Value_N, en de actie is het uitvoeren van Statement_N als het bovenstaande resultaat WAAR is.
• ALIAS_NAME is optioneel en is de aliasnaam die wordt gegeven aan het resultaat van de SQL Server CASE-instructie. Het wordt voornamelijk gebruikt wanneer we CASE gebruiken in de SQL Server SELECT-clausule.

Regels voor eenvoudige gevallen

• Simple Case staat alleen een gelijkheidscontrole toe van Case_Expression met Value_1 tot Value_N.
• De Case_Expression wordt vergeleken met een waarde, in volgorde, beginnend bij de eerste waarde, oftewel Value_1. Hieronder volgt de uitvoeringsmethode:
• Als Case_Expression gelijk is aan Waarde_1, worden verdere WHEN…THEN-instructies overgeslagen en wordt de uitvoering van CASE onmiddellijk END.
• Als Case_Expression niet overeenkomt met Value_1, wordt Case_Expression vergeleken met Value_2 om te controleren op gelijkwaardigheid. Dit proces van het vergelijken van Case_Expression met een waarde wordt herhaald totdat Case_Expression een overeenkomende, gelijkwaardige waarde vindt in de verzameling Value_1, Value_2, enzovoort.
• Als er geen overeenkomst is, gaat de controle naar de ELSE-instructie en wordt Statement_Else uitgevoerd.
• ANDERS is optioneel.
• Als ELSE niet aanwezig is en Case_Expression geen van de waarden overeenkomt, wordt NULL weergegeven.

Het onderstaande diagram illustreert de uitvoeringsstroom van een eenvoudig geval:

Voorbeelden

Stel dat we een tabel hebben met de naam 'Guru99' met twee kolommen, Tutorial_ID en Tutorial_name, en vier rijen, zoals hieronder weergegeven. We zullen deze 'GuruTabel 99 in de volgende voorbeelden.

Query 1: EENVOUDIG GEVAL met de optie GEEN ANDERS

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Resultaat: Het onderstaande diagram illustreert de uitvoeringsstroom van een SIMPLE CASE zonder ELSE.

Query 2: SIMPLE CASE met de ELSE-optie

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Resultaat: Het onderstaande diagram illustreert de uitvoeringsstroom van een SIMPLE CASE met ELSE.

CASE gezocht

De syntaxis voor gezochte gevallen

CASE 
     WHEN <Boolean_Expression_1> THEN Statement_1
     WHEN <Boolean_Expression_2> THEN Statement_2
     .
     .
     WHEN <Boolean_Expression_N> THEN Statement_N
     [ELSE Statement_Else]   
END AS [ALIAS_NAME]

Hier:

• De parameter Boolean_Expression_1… geeft de expressie aan die zal worden geëvalueerd op WAAR of ONWAAR.
• De parameters Statement_1, Statement_2… geven de instructies aan die worden uitgevoerd als het corresponderende resultaat van Boolean_Expression_1, Boolean_Expression_2 WAAR is.
• Kort gezegd is de voorwaarde Boolean_Expression_1… en de actie is het uitvoeren van Statement_N als de bovenstaande Boolean_Expression_1 WAAR is.
• ALIAS_NAME is optioneel en is de aliasnaam die aan het resultaat van de CASE-instructie wordt gegeven. Het wordt meestal gebruikt wanneer we CASE in de SELECT-clausule gebruiken.

Regels voor gezochte casus

• In tegenstelling tot het eenvoudige geval is het doorzochte geval niet beperkt tot een gelijkheidscontrole, maar staat het ook een Booleaanse expressie toe.
• De Booleaanse expressie wordt in volgorde geëvalueerd, te beginnen met de eerste Booleaanse expressie, oftewel Booleaanse_expressie_1. Hieronder volgt de uitvoeringsmethode:
• Als Boolean_Expression_1 WAAR is, worden verdere WHEN…THEN-instructies overgeslagen en eindigt de CASE-uitvoering onmiddellijk.
• Als Boolean_Expression_1 ONWAAR is, wordt Boolean_Expression_2 geëvalueerd op een WAAR-voorwaarde. Dit proces van het beoordelen van de Booleaanse expressies gaat door totdat een van de Booleaanse expressies WAAR retourneert.
• Als er geen overeenkomst is, gaat de controle naar de ELSE-instructie en wordt Statement_Else uitgevoerd.
• Net als in het Simple Case is ELSE ook in het Searched Case optioneel.
• Als ELSE niet aanwezig is en geen van de Booleaanse expressies TRUE retourneert, wordt NULL weergegeven.

Het onderstaande diagram illustreert de uitvoeringsstroom van de gezochte casus:

Voorbeelden

Query 1: ZOEKEN CASE met de optie GEEN ANDERS

SELECT Tutorial_ID, Tutorial_name,
CASE 
 	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Resultaat: Het onderstaande diagram illustreert de uitvoeringsstroom van de SEARCHED CASE zonder ELSE.

Query 2: SEARCHED CASE met de ELSE-optie

SELECT Tutorial_ID, Tutorial_name,
CASE 
	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Resultaat: Het onderstaande diagram illustreert de uitvoeringsstroom van de SEARCHED CASE met ELSE.

Verschil tussen uitvoeringsaanpak: SIMPLE en SEARCH CASE

Laten we eens kijken naar het eenvoudige voorbeeld hieronder:

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Hier maakt 'Tutorial_name' deel uit van de CASE-expressie in SQL. Vervolgens wordt de waarde van 'Tutorial_name' vergeleken met elke WHEN-waarde, oftewel 'SQL'… totdat 'Tutorial_name' overeenkomt met een WHEN-waarde.

Integendeel, het SEARCH CASE-voorbeeld bevat geen CASE-uitdrukking:

SELECT Tutorial_ID, Tutorial_name,
CASE 
 	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Hier heeft elke WHEN-instructie een eigen voorwaardelijke Booleaanse expressie. Elke Booleaanse expressie, bijvoorbeeld Tutorial_name = 'SQL'…, wordt geëvalueerd op WAAR/ONWAAR totdat de eerste Booleaanse expressie die als WAAR wordt geëvalueerd, wordt gevonden.

Verschil tussen eenvoudige en doorzochte gevallen

Geneste CASE: CASE in IF ELSE

We kunnen CASE gebruiken binnen een IF…ELSE-instructie. We declareren een variabele voor het vliegticket en de bijbehorende waarde. Hieronder staat een voorbeeld van MS-SQL-code:

DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 190;
IF @Flight_Ticket > 400
   PRINT 'Visit Nearby Tourist Location';
ELSE 
BEGIN
    SELECT
	CASE 
	WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
	WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
	WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
	END AS Location	
END

In het bovenstaande voorbeeld is de CASE-instructie genest in de IF…ELSE-instructie. Eerst wordt de IF-instructie uitgevoerd en als de CASE-voorwaarde in SQL Server onwaar is, wordt de ELSE-instructie uitgevoerd. Het ELSE-gedeelte bevat een geneste CASE-instructie in SQL. Afhankelijk van de waarde van het vliegticket wordt een van de volgende resultaten weergegeven:

• Het systeem geeft de melding 'Bezoek een toeristische locatie in de buurt' weer als de vliegtickets meer dan $400 kosten.
• Het systeem print 'Bezoek Los Angeles' als de vliegtickets TUSSEN $0 EN $100 kosten.
• Het systeem print 'Bezoek New York' als de vliegtickets TUSSEN $101 EN $200 kosten.
• Het systeem print 'Bezoek Europa' als de vliegtickets TUSSEN $201 EN $400 kosten.

Het onderstaande resultaat toont de locatie die wordt geretourneerd wanneer de geneste CASE-instructie binnen de ELSE-tak wordt uitgevoerd:

Geneste CASE: CASE binnen CASE

In SQL kunnen we een CASE-instructie binnen een andere CASE-instructie gebruiken. Hieronder staat een voorbeeld van MS-SQL-code:

DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 250;
SELECT
CASE 
WHEN @Flight_Ticket >= 400 THEN 'Visit Nearby Tourist Location.'
WHEN @Flight_Ticket < 400 THEN 
    	CASE 
		WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
		WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
		WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
		END	
END AS Location

In het bovenstaande voorbeeld is een CASE-instructie genest in een andere CASE-instructie. Het systeem voert eerst de buitenste CASE-instructie uit; als Flight_Ticket < $400, wordt de binnenste CASE-instructie uitgevoerd. De geretourneerde locatie volgt dezelfde ticketprijsbereiken als in het vorige voorbeeld, dus een ticket van $250 valt binnen het bereik van $201–$400 en retourneert 'Visit Europe'.

Het onderstaande resultaat toont de locatie die door de interne CASE-query is geretourneerd voor een ticket van $250:

CASE met UPDATE

Laten we er opnieuw van uitgaan dat we de 'GuruTabel 99 met twee kolommen en vier rijen, zoals hieronder weergegeven:

We kunnen CASE gebruiken met UPDATE. Hieronder ziet u het voorbeeld van MS-SQL-code:

UPDATE Guru99
SET Tutorial_Name = 
	(
	CASE
	WHEN Tutorial_Name = 'SQL' THEN 'Structured Query language.'
	WHEN Tutorial_Name = 'PL/SQL' THEN 'Oracle PL/SQL'
	WHEN Tutorial_Name = 'MSSQL' THEN 'Microsoft SQL.'
	WHEN Tutorial_Name = 'Hadoop' THEN 'Apache Hadoop.'
	END
	)

In het bovenstaande voorbeeld wordt CASE gebruikt in de UPDATE-instructie. Afhankelijk van de waarde van Tutorial_Name wordt de kolom Tutorial_Name bijgewerkt met de waarde uit de THEN-instructie:

• Als Tutorial_Name = 'SQL' is, wijzig dan Tutorial_Name in 'Structured Query language'.
• Als Tutorial_Name = 'PL/SQL' is, werk Tutorial_Name dan bij naar 'Oracle PL/SQL'.
• Als Tutorial_Name = 'MSSQL' is, werk Tutorial_Name dan bij naar 'Microsoft SQL'.
• Als Tutorial_Name = 'Hadoop' is, wijzig dan Tutorial_Name in 'Apache Hadoop'.

Door de instructie uit te voeren, worden de overeenkomende rijen bijgewerkt, zoals hieronder weergegeven:

Laten we de 'GuruTabel 99 om de bijgewerkte waarden te controleren:

CASE met Bestel op

We kunnen CASE gebruiken in combinatie met ORDER BY. Hieronder staat een voorbeeld van MS-SQL-code:

Declare @Order Int;
Set @Order = 1
Select * from Guru99 order by 
CASE 
	WHEN @Order = 1 THEN Tutorial_ID
	WHEN @Order = 2 THEN Tutorial_Name
	END
DESC

Hier wordt CASE gebruikt in combinatie met ORDER BY. @Order wordt ingesteld op 1, en aangezien de eerste Booleaanse expressie WHEN waar is, wordt Tutorial_ID geselecteerd voor de ORDER BY-voorwaarde. Het resultaat wordt hieronder weergegeven:

Interessante feiten!

• Een CASE-statement kan zowel in een ander CASE-statement als in een IF…ELSE-statement genesteld worden.
• Naast SELECT kan CASE ook worden gebruikt met andere SQL-clausules zoals UPDATE en ORDER BY.

Veelgestelde vragen