{"id":84,"date":"2020-05-18T09:33:11","date_gmt":"2020-05-18T09:33:11","guid":{"rendered":"https:\/\/turtleflyblog.com\/?p=84"},"modified":"2022-02-28T10:12:48","modified_gmt":"2022-02-28T10:12:48","slug":"sql-case","status":"publish","type":"post","link":"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/","title":{"rendered":"SQL CASE"},"content":{"rendered":"\n

When you are familiar with CASE, <\/strong>you can make your SQL statement more flexible and reduce redundant semantics.<\/p>\n\n\n\n

CASE can be categorized into two cases. One is simple case<\/p>\n\n\n\n

CASE<\/strong><\/p>\n\n\n\n

  WHEN condition THEN result<\/strong><\/p>\n\n\n\n

  [WHEN\u00b7\u00b7\u00b7]<\/strong><\/p>\n\n\n\n

  [ELSE result]<\/strong><\/p>\n\n\n\n

END<\/strong>;<\/p>\n\n\n\n

and the other one is searched case<\/p>\n\n\n\n

CASE expression<\/strong><\/p>\n\n\n\n

  WHEN value THEN result<\/strong><\/p>\n\n\n\n

  [WHEN\u00b7\u00b7\u00b7]<\/strong><\/p>\n\n\n\n

  [ELSE result]<\/strong><\/p>\n\n\n\n

END<\/strong>;<\/p>\n\n\n\n

Notice:<\/p>\n\n\n\n

  1. The return type of each case statement must be equal<\/li>
  2. Add End<\/strong><\/li>
  3. Must add ELSE<\/strong> statement<\/li><\/ol>\n\n\n\n

    The following examples manifest how to use CASE to minimize SQL redundant statement<\/p>\n\n\n\n

    Condition 1<\/strong><\/p>\n\n\n\n

    Update Employee<\/p>\n\n\n\n

    SET salary = salary * 0.9<\/p>\n\n\n\n

    WHERE salary >= 30000<\/p>\n\n\n\n

    Condition 2<\/strong><\/p>\n\n\n\n

    Update Employee<\/p>\n\n\n\n

    SET salary = salary * 1.2<\/p>\n\n\n\n

    WHERE salary >= 25000 AND salary < 28000<\/p>\n\n\n\n

    Now we use CASE<\/strong><\/p>\n\n\n\n

    UPDATE Employee<\/p>\n\n\n\n

    SET salary = CASE WHEN salary >= 30000<\/p>\n\n\n\n

    THEN salary * 0.9<\/p>\n\n\n\n

    WHEN salary >= 25000 AND salary < 28000<\/p>\n\n\n\n

    THEN salary * 1.2<\/p>\n\n\n\n

    ELSE salary END;<\/p>\n\n\n\n

    We only need one sql statement by using SQL<\/p>\n\n\n\n

    Another two extra examples:<\/p>\n\n\n\n

    Example one<\/strong><\/p>\n\n\n\n

    Table : sq1Q1<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Try to get the maximum value of x and y<\/p>\n\n\n\n

    The result is<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Example two<\/strong><\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

    knowing how to use CASE is a compulsory skill for DBA. I hope this article gives you a basic knowledge of SQL CASE.<\/p>\n","protected":false},"excerpt":{"rendered":"

    When you are familiar with CASE, you can make your SQL statement more flexible and reduce redundant semantics. CASE can be categorized into two cases. One is simple case CASE   WHEN condition THEN result   [WHEN\u00b7\u00b7\u00b7]   [ELSE result] END; and the other one is searched case CASE expression   WHEN value THEN result   [WHEN\u00b7\u00b7\u00b7]   [ELSE […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[29],"tags":[],"class_list":["post-84","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"\nSQL CASE - Who said turtle cannot fly<\/title>\n<meta name=\"description\" content=\"When you are familiar with CASE, you can make your SQL statement more flexible and reduce redundant semantics.CASE can be categorized into two cases. One is simple case\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL CASE - Who said turtle cannot fly\" \/>\n<meta property=\"og:description\" content=\"When you are familiar with CASE, you can make your SQL statement more flexible and reduce redundant semantics.CASE can be categorized into two cases. One is simple case\" \/>\n<meta property=\"og:url\" content=\"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/\" \/>\n<meta property=\"og:site_name\" content=\"Who said turtle cannot fly\" \/>\n<meta property=\"article:published_time\" content=\"2020-05-18T09:33:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-28T10:12:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/turtleflyblog.com\/wp-content\/uploads\/2020\/11\/image.png\" \/>\n<meta name=\"author\" content=\"Joseph\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/\",\"url\":\"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/\",\"name\":\"SQL CASE - Who said turtle cannot fly\",\"isPartOf\":{\"@id\":\"https:\/\/turtleflyblog.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/turtleflyblog.com\/wp-content\/uploads\/2020\/11\/image.png\",\"datePublished\":\"2020-05-18T09:33:11+00:00\",\"dateModified\":\"2022-02-28T10:12:48+00:00\",\"author\":{\"@id\":\"https:\/\/turtleflyblog.com\/#\/schema\/person\/130c2e1bdce4a8ba27bfe14c65988172\"},\"description\":\"When you are familiar with CASE, you can make your SQL statement more flexible and reduce redundant semantics.CASE can be categorized into two cases. One is simple case\",\"breadcrumb\":{\"@id\":\"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/#breadcrumb\"},\"inLanguage\":\"en-AU\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-AU\",\"@id\":\"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/#primaryimage\",\"url\":\"https:\/\/i0.wp.com\/turtleflyblog.com\/wp-content\/uploads\/2020\/11\/image.png?fit=345%2C144&ssl=1\",\"contentUrl\":\"https:\/\/i0.wp.com\/turtleflyblog.com\/wp-content\/uploads\/2020\/11\/image.png?fit=345%2C144&ssl=1\",\"width\":345,\"height\":144},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/turtleflyblog.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL CASE\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/turtleflyblog.com\/#website\",\"url\":\"https:\/\/turtleflyblog.com\/\",\"name\":\"Who said turtle cannot fly\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/turtleflyblog.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-AU\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/turtleflyblog.com\/#\/schema\/person\/130c2e1bdce4a8ba27bfe14c65988172\",\"name\":\"Joseph\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-AU\",\"@id\":\"https:\/\/turtleflyblog.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/539caa7bc4af75cd4ab82fc8d51935da?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/539caa7bc4af75cd4ab82fc8d51935da?s=96&d=mm&r=g\",\"caption\":\"Joseph\"},\"sameAs\":[\"https:\/\/turtleflyblog.com\/\/\"],\"url\":\"https:\/\/turtleflyblog.com\/author\/joseph\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL CASE - Who said turtle cannot fly","description":"When you are familiar with CASE, you can make your SQL statement more flexible and reduce redundant semantics.CASE can be categorized into two cases. One is simple case","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/","og_locale":"en_US","og_type":"article","og_title":"SQL CASE - Who said turtle cannot fly","og_description":"When you are familiar with CASE, you can make your SQL statement more flexible and reduce redundant semantics.CASE can be categorized into two cases. One is simple case","og_url":"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/","og_site_name":"Who said turtle cannot fly","article_published_time":"2020-05-18T09:33:11+00:00","article_modified_time":"2022-02-28T10:12:48+00:00","og_image":[{"url":"https:\/\/turtleflyblog.com\/wp-content\/uploads\/2020\/11\/image.png","type":"","width":"","height":""}],"author":"Joseph","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Joseph","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/","url":"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/","name":"SQL CASE - Who said turtle cannot fly","isPartOf":{"@id":"https:\/\/turtleflyblog.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/#primaryimage"},"image":{"@id":"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/#primaryimage"},"thumbnailUrl":"https:\/\/turtleflyblog.com\/wp-content\/uploads\/2020\/11\/image.png","datePublished":"2020-05-18T09:33:11+00:00","dateModified":"2022-02-28T10:12:48+00:00","author":{"@id":"https:\/\/turtleflyblog.com\/#\/schema\/person\/130c2e1bdce4a8ba27bfe14c65988172"},"description":"When you are familiar with CASE, you can make your SQL statement more flexible and reduce redundant semantics.CASE can be categorized into two cases. One is simple case","breadcrumb":{"@id":"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/#breadcrumb"},"inLanguage":"en-AU","potentialAction":[{"@type":"ReadAction","target":["https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/"]}]},{"@type":"ImageObject","inLanguage":"en-AU","@id":"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/#primaryimage","url":"https:\/\/i0.wp.com\/turtleflyblog.com\/wp-content\/uploads\/2020\/11\/image.png?fit=345%2C144&ssl=1","contentUrl":"https:\/\/i0.wp.com\/turtleflyblog.com\/wp-content\/uploads\/2020\/11\/image.png?fit=345%2C144&ssl=1","width":345,"height":144},{"@type":"BreadcrumbList","@id":"https:\/\/turtleflyblog.com\/en\/2020\/05\/18\/sql-case\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/turtleflyblog.com\/en\/"},{"@type":"ListItem","position":2,"name":"SQL CASE"}]},{"@type":"WebSite","@id":"https:\/\/turtleflyblog.com\/#website","url":"https:\/\/turtleflyblog.com\/","name":"Who said turtle cannot fly","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/turtleflyblog.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-AU"},{"@type":"Person","@id":"https:\/\/turtleflyblog.com\/#\/schema\/person\/130c2e1bdce4a8ba27bfe14c65988172","name":"Joseph","image":{"@type":"ImageObject","inLanguage":"en-AU","@id":"https:\/\/turtleflyblog.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/539caa7bc4af75cd4ab82fc8d51935da?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/539caa7bc4af75cd4ab82fc8d51935da?s=96&d=mm&r=g","caption":"Joseph"},"sameAs":["https:\/\/turtleflyblog.com\/\/"],"url":"https:\/\/turtleflyblog.com\/author\/joseph\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/posts\/84","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/comments?post=84"}],"version-history":[{"count":1,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/posts\/84\/revisions"}],"predecessor-version":[{"id":678,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/posts\/84\/revisions\/678"}],"wp:attachment":[{"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/media?parent=84"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/categories?post=84"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/turtleflyblog.com\/wp-json\/wp\/v2\/tags?post=84"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}