{"id":1445,"date":"2017-03-10T19:10:02","date_gmt":"2017-03-10T18:10:02","guid":{"rendered":"https:\/\/vielhuber.de\/?p=1445"},"modified":"2020-07-03T02:07:40","modified_gmt":"2020-07-03T00:07:40","slug":"rekursion-in-sql","status":"publish","type":"post","link":"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/","title":{"rendered":"Rekursion in SQL"},"content":{"rendered":"<p>Relationale\u00a0Datenbankmanagementsysteme auf Basis von SQL eignen sich nur bedingt f\u00fcr hierarchische oder rekursive Abfragen. Hier sind andere Systeme wie <a href=\"https:\/\/www.arangodb.com\/\">ArangoDB<\/a> besser (einen tollen Talk dazu gab es auf der\u00a0<a href=\"https:\/\/www.youtube.com\/watch?v=EUwN-wvCtkQ\" target=\"_blank\" rel=\"noopener noreferrer\">GOTO 2016<\/a>). Man kann aber auch in SQL mit Hilfe von <a href=\"https:\/\/docs.microsoft.com\/de-de\/sql\/t-sql\/queries\/with-common-table-expression-transact-sql\" target=\"_blank\" rel=\"noopener noreferrer\">Common Table Expressions<\/a> rekursive Abfragen erzeugen und so z.B. klassische Parent-\/Child-Tabellen auf alle Vorfahren und Nachfahren abfragen.<\/p>\n<p><!--more--><\/p>\n<p>In PostgreSQL sieht das dann beispielsweise so aus:<\/p>\n<p class=\"githubgist\" data-gist-file=\"create.sql\">0672c4761d202a35a9059fa137ace7c3<\/p>\n<p>Will man nun alle Kinder einer bestimmten item_id bekommen, nutzt man die Common Table Expressions:<\/p>\n<p class=\"githubgist\" data-gist-file=\"children.sql\">0672c4761d202a35a9059fa137ace7c3<\/p>\n<p>Auch die Abfrage der Eltern ist so kein Problem:<\/p>\n<p class=\"githubgist\" data-gist-file=\"parents.sql\">0672c4761d202a35a9059fa137ace7c3<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Relationale\u00a0Datenbankmanagementsysteme auf Basis von SQL eignen sich nur bedingt f\u00fcr hierarchische oder rekursive Abfragen. Hier sind andere Systeme wie ArangoDB besser (einen tollen Talk dazu gab es auf der\u00a0GOTO 2016). Man kann aber auch in SQL mit Hilfe von Common Table Expressions rekursive Abfragen erzeugen und so z.B. klassische Parent-\/Child-Tabellen auf alle Vorfahren und Nachfahren [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"gtbabel_prevent_lngs":"","gtbabel_alt_lng":"","footnotes":""},"categories":[1],"tags":[],"class_list":{"0":"post-1445","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-blog"},"acf":[],"yoast_head":"<title>Rekursion in SQL &#060; Vielhuber David<\/title>\n<meta name=\"description\" content=\"Relationale\u00a0Datenbankmanagementsysteme auf Basis von SQL eignen sich nur bedingt f\u00fcr hierarchische oder rekursive Abfragen. Hier sind andere Systeme...\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/\" \/>\n<meta property=\"og:locale\" content=\"de_DE\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Rekursion in SQL &#060; Vielhuber David\" \/>\n<meta property=\"og:description\" content=\"Relationale\u00a0Datenbankmanagementsysteme auf Basis von SQL eignen sich nur bedingt f\u00fcr hierarchische oder rekursive Abfragen. Hier sind andere Systeme wie\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Vielhuber David\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-10T18:10:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-07-03T00:07:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vielhuber.de\/wp-content\/uploads\/about.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"700\" \/>\n\t<meta property=\"og:image:height\" content=\"552\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"David\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@vielhuber\" \/>\n<meta name=\"twitter:site\" content=\"@vielhuber\" \/>\n<meta name=\"twitter:label1\" content=\"Verfasst von\" \/>\n\t<meta name=\"twitter:data1\" content=\"David\" \/>\n\t<meta name=\"twitter:label2\" content=\"Gesch\u00e4tzte Lesezeit\" \/>\n\t<meta name=\"twitter:data2\" content=\"1\u00a0Minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/vielhuber.de\\\/blog\\\/rekursion-in-sql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vielhuber.de\\\/blog\\\/rekursion-in-sql\\\/\"},\"author\":{\"name\":\"David\",\"@id\":\"https:\\\/\\\/vielhuber.de\\\/#\\\/schema\\\/person\\\/64d4ff14713d413ea4d9b210d0c2c6ef\"},\"headline\":\"Rekursion in SQL\",\"datePublished\":\"2017-03-10T18:10:02+00:00\",\"dateModified\":\"2020-07-03T00:07:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/vielhuber.de\\\/blog\\\/rekursion-in-sql\\\/\"},\"wordCount\":117,\"publisher\":{\"@id\":\"https:\\\/\\\/vielhuber.de\\\/#\\\/schema\\\/person\\\/64d4ff14713d413ea4d9b210d0c2c6ef\"},\"articleSection\":[\"Blog\"],\"inLanguage\":\"de\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/vielhuber.de\\\/blog\\\/rekursion-in-sql\\\/\",\"url\":\"https:\\\/\\\/vielhuber.de\\\/blog\\\/rekursion-in-sql\\\/\",\"name\":\"Rekursion in SQL &#060; Vielhuber David\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vielhuber.de\\\/#website\"},\"datePublished\":\"2017-03-10T18:10:02+00:00\",\"dateModified\":\"2020-07-03T00:07:40+00:00\",\"description\":\"Relationale\u00a0Datenbankmanagementsysteme auf Basis von SQL eignen sich nur bedingt f\u00fcr hierarchische oder rekursive Abfragen. Hier sind andere Systeme wie\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/vielhuber.de\\\/blog\\\/rekursion-in-sql\\\/#breadcrumb\"},\"inLanguage\":\"de\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/vielhuber.de\\\/blog\\\/rekursion-in-sql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/vielhuber.de\\\/blog\\\/rekursion-in-sql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/vielhuber.de\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Rekursion in SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/vielhuber.de\\\/#website\",\"url\":\"https:\\\/\\\/vielhuber.de\\\/\",\"name\":\"Vielhuber David\",\"description\":\"Full-Stack Developer\",\"publisher\":{\"@id\":\"https:\\\/\\\/vielhuber.de\\\/#\\\/schema\\\/person\\\/64d4ff14713d413ea4d9b210d0c2c6ef\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/vielhuber.de\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"de\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/vielhuber.de\\\/#\\\/schema\\\/person\\\/64d4ff14713d413ea4d9b210d0c2c6ef\",\"name\":\"David\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"de\",\"@id\":\"https:\\\/\\\/vielhuber.de\\\/wp-content\\\/uploads\\\/about.jpg\",\"url\":\"https:\\\/\\\/vielhuber.de\\\/wp-content\\\/uploads\\\/about.jpg\",\"contentUrl\":\"https:\\\/\\\/vielhuber.de\\\/wp-content\\\/uploads\\\/about.jpg\",\"width\":700,\"height\":552,\"caption\":\"David\"},\"logo\":{\"@id\":\"https:\\\/\\\/vielhuber.de\\\/wp-content\\\/uploads\\\/about.jpg\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/vielhuber\"]}]}<\/script>","yoast_head_json":{"title":"Rekursion in SQL &#060; Vielhuber David","description":"Relationale\u00a0Datenbankmanagementsysteme auf Basis von SQL eignen sich nur bedingt f\u00fcr hierarchische oder rekursive Abfragen. Hier sind andere Systeme...","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:\/\/vielhuber.de\/blog\/rekursion-in-sql\/","og_locale":"de_DE","og_type":"article","og_title":"Rekursion in SQL &#060; Vielhuber David","og_description":"Relationale\u00a0Datenbankmanagementsysteme auf Basis von SQL eignen sich nur bedingt f\u00fcr hierarchische oder rekursive Abfragen. Hier sind andere Systeme wie","og_url":"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/","og_site_name":"Vielhuber David","article_published_time":"2017-03-10T18:10:02+00:00","article_modified_time":"2020-07-03T00:07:40+00:00","og_image":[{"width":700,"height":552,"url":"https:\/\/vielhuber.de\/wp-content\/uploads\/about.jpg","type":"image\/jpeg"}],"author":"David","twitter_card":"summary_large_image","twitter_creator":"@vielhuber","twitter_site":"@vielhuber","twitter_misc":{"Verfasst von":"David","Gesch\u00e4tzte Lesezeit":"1\u00a0Minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/#article","isPartOf":{"@id":"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/"},"author":{"name":"David","@id":"https:\/\/vielhuber.de\/#\/schema\/person\/64d4ff14713d413ea4d9b210d0c2c6ef"},"headline":"Rekursion in SQL","datePublished":"2017-03-10T18:10:02+00:00","dateModified":"2020-07-03T00:07:40+00:00","mainEntityOfPage":{"@id":"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/"},"wordCount":117,"publisher":{"@id":"https:\/\/vielhuber.de\/#\/schema\/person\/64d4ff14713d413ea4d9b210d0c2c6ef"},"articleSection":["Blog"],"inLanguage":"de"},{"@type":"WebPage","@id":"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/","url":"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/","name":"Rekursion in SQL &#060; Vielhuber David","isPartOf":{"@id":"https:\/\/vielhuber.de\/#website"},"datePublished":"2017-03-10T18:10:02+00:00","dateModified":"2020-07-03T00:07:40+00:00","description":"Relationale\u00a0Datenbankmanagementsysteme auf Basis von SQL eignen sich nur bedingt f\u00fcr hierarchische oder rekursive Abfragen. Hier sind andere Systeme wie","breadcrumb":{"@id":"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/#breadcrumb"},"inLanguage":"de","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vielhuber.de\/blog\/rekursion-in-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vielhuber.de\/"},{"@type":"ListItem","position":2,"name":"Rekursion in SQL"}]},{"@type":"WebSite","@id":"https:\/\/vielhuber.de\/#website","url":"https:\/\/vielhuber.de\/","name":"Vielhuber David","description":"Full-Stack Developer","publisher":{"@id":"https:\/\/vielhuber.de\/#\/schema\/person\/64d4ff14713d413ea4d9b210d0c2c6ef"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vielhuber.de\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"de"},{"@type":["Person","Organization"],"@id":"https:\/\/vielhuber.de\/#\/schema\/person\/64d4ff14713d413ea4d9b210d0c2c6ef","name":"David","image":{"@type":"ImageObject","inLanguage":"de","@id":"https:\/\/vielhuber.de\/wp-content\/uploads\/about.jpg","url":"https:\/\/vielhuber.de\/wp-content\/uploads\/about.jpg","contentUrl":"https:\/\/vielhuber.de\/wp-content\/uploads\/about.jpg","width":700,"height":552,"caption":"David"},"logo":{"@id":"https:\/\/vielhuber.de\/wp-content\/uploads\/about.jpg"},"sameAs":["https:\/\/x.com\/vielhuber"]}]}},"_links":{"self":[{"href":"https:\/\/vielhuber.de\/jv\/wp-json\/wp\/v2\/posts\/1445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vielhuber.de\/jv\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vielhuber.de\/jv\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vielhuber.de\/jv\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vielhuber.de\/jv\/wp-json\/wp\/v2\/comments?post=1445"}],"version-history":[{"count":11,"href":"https:\/\/vielhuber.de\/jv\/wp-json\/wp\/v2\/posts\/1445\/revisions"}],"predecessor-version":[{"id":2719,"href":"https:\/\/vielhuber.de\/jv\/wp-json\/wp\/v2\/posts\/1445\/revisions\/2719"}],"wp:attachment":[{"href":"https:\/\/vielhuber.de\/jv\/wp-json\/wp\/v2\/media?parent=1445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vielhuber.de\/jv\/wp-json\/wp\/v2\/categories?post=1445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vielhuber.de\/jv\/wp-json\/wp\/v2\/tags?post=1445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}