{"id":5850,"date":"2017-06-16T02:56:42","date_gmt":"2017-06-16T09:56:42","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5850"},"modified":"2024-01-04T07:37:34","modified_gmt":"2024-01-04T14:37:34","slug":"mysql-adjacency-list-tree","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/","title":{"rendered":"MySQL Adjacency List Model"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to use the adjacency list model for managing hierarchical data in MySQL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to adjacency list model<\/h2>\n\n\n\n<p>Hierarchical data is everywhere, appearing in various forms such as blog categories, product hierarchies, or organizational structures.<\/p>\n\n\n\n<p>Managing hierarchical data in MySQL can be approached in several ways, and the adjacency list model is often considered the simplest solution. Due to its simplicity, the adjacency list model is a highly favored choice among developers and database administrators.<\/p>\n\n\n\n<p>In the adjacency list model, each node has a pointer to its parent, with the top node having no parent. Here are some examples of electronic product categories:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"830\" height=\"300\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/mysql-adjacency-list.png\" alt=\"mysql adjacency list\" class=\"wp-image-5866\" title=\"mysql adjacency list\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/mysql-adjacency-list.png 830w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/mysql-adjacency-list-300x108.png 300w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/mysql-adjacency-list-768x278.png 768w\" sizes=\"auto, (max-width: 830px) 100vw, 830px\" \/><\/figure>\n\n\n\n<p>Before delving into the adjacency list model, it&#8217;s essential to acquaint yourself with the following key terms:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>Electronics<\/code> is a top node or root node.<\/li>\n\n\n\n<li><code>Laptops, Cameras &amp; photo, Phones &amp; Accessories<\/code> nodes are the children of the <code>Electronics<\/code> node. And vice versa Electronics node is the parent of <code>Laptops, Cameras &amp; photo, Phones &amp; Accessories<\/code> nodes.<\/li>\n\n\n\n<li>The leaf nodes are the nodes that have no children e.g., <code>Laptops<\/code>, <code>PC<\/code>, <code>Android<\/code>, <code>iOS<\/code>, etc., while the non-leaf nodes&nbsp;are the ones that have at least one child.<\/li>\n\n\n\n<li>The children and grandchildren of a node are called descendants. And the parents, grandparents, etc., of a node are also known as ancestors.<\/li>\n<\/ul>\n\n\n\n<p>To model this category tree, create a table called <code>category<\/code> with three columns: <code>id<\/code>, <code>title<\/code>, and <code>parent_id<\/code> as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> <span class=\"hljs-keyword\">category<\/span> (\n  <span class=\"hljs-keyword\">id<\/span> <span class=\"hljs-built_in\">int<\/span>(<span class=\"hljs-number\">10<\/span>) <span class=\"hljs-keyword\">unsigned<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span> AUTO_INCREMENT, \n  title <span class=\"hljs-built_in\">varchar<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>, \n  parent_id <span class=\"hljs-built_in\">int<\/span>(<span class=\"hljs-number\">10<\/span>) <span class=\"hljs-keyword\">unsigned<\/span> <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-literal\">NULL<\/span>, \n  PRIMARY <span class=\"hljs-keyword\">KEY<\/span> (<span class=\"hljs-keyword\">id<\/span>), \n  <span class=\"hljs-keyword\">FOREIGN<\/span> <span class=\"hljs-keyword\">KEY<\/span> (parent_id) <span class=\"hljs-keyword\">REFERENCES<\/span> <span class=\"hljs-keyword\">category<\/span> (<span class=\"hljs-keyword\">id<\/span>) <span class=\"hljs-keyword\">ON<\/span> <span class=\"hljs-keyword\">DELETE<\/span> <span class=\"hljs-keyword\">CASCADE<\/span> <span class=\"hljs-keyword\">ON<\/span> <span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-keyword\">CASCADE<\/span>\n);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Each row in a table represents a node in the tree identified by the <code>id<\/code> column. The <code>parent_id<\/code> column serves as a <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-foreign-key\/\">foreign key<\/a> to the <code>id<\/code> column in the same <code>category<\/code> table, acting as a pointer.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Inserting data<\/h3>\n\n\n\n<p>The root node of the tree has no parent, therefore, the <code>parent_id<\/code> is set to <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-null\/\"><code>NULL<\/code><\/a>. The other nodes must have one and only one parent.<\/p>\n\n\n\n<p>To <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-insert\/\">insert<\/a> a root node, you set the <code>parent_id<\/code> to <code>NULL<\/code> as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Electronics'<\/span>, <span class=\"hljs-literal\">NULL<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To insert a non-root node, you just need to set&nbsp;its&nbsp;<code>parent_id<\/code> to&nbsp;the id of its parent node. <\/p>\n\n\n\n<p>For example, the <code>parent_id<\/code> of <code>Laptop &amp; PC<\/code>, <code>Cameras &amp; Photos<\/code> and <code>Phone &amp; Accessories<\/code> nodes are set to 1:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Laptops &amp; PC'<\/span>, <span class=\"hljs-number\">1<\/span>);\n\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Laptops'<\/span>, <span class=\"hljs-number\">2<\/span>);\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'PC'<\/span>, <span class=\"hljs-number\">2<\/span>);\n\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Cameras &amp; photo'<\/span>, <span class=\"hljs-number\">1<\/span>);\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Camera'<\/span>, <span class=\"hljs-number\">5<\/span>);\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Phones &amp; Accessories'<\/span>, <span class=\"hljs-number\">1<\/span>);\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Smartphones'<\/span>, <span class=\"hljs-number\">7<\/span>);\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Android'<\/span>, <span class=\"hljs-number\">8<\/span>);\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'iOS'<\/span>, <span class=\"hljs-number\">8<\/span>);\n\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Other Smartphones'<\/span>, <span class=\"hljs-number\">8<\/span>);\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Batteries'<\/span>, <span class=\"hljs-number\">7<\/span>);\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Headsets'<\/span>, <span class=\"hljs-number\">7<\/span>);\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">category<\/span>(title, parent_id) \n<span class=\"hljs-keyword\">VALUES<\/span> \n  (<span class=\"hljs-string\">'Screen Protectors'<\/span>, <span class=\"hljs-number\">7<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Finding the root node<\/h3>\n\n\n\n<p>The root node is the node that has no parent. In other words, its <code>parent_id<\/code> is <code>NULL<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span>\n    <span class=\"hljs-keyword\">id<\/span>, title\n<span class=\"hljs-keyword\">FROM<\/span>\n    <span class=\"hljs-keyword\">category<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span>\n    parent_id <span class=\"hljs-keyword\">IS<\/span> <span class=\"hljs-literal\">NULL<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"295\" height=\"36\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-root-node.png\" alt=\"\" class=\"wp-image-5854\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Finding the immediate children of a node<\/h3>\n\n\n\n<p>The following <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-select-from\/\">query<\/a> gets the immediate children of the root node:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span>\n    <span class=\"hljs-keyword\">id<\/span>, title\n<span class=\"hljs-keyword\">FROM<\/span>\n    <span class=\"hljs-keyword\">category<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span>\n    parent_id = <span class=\"hljs-number\">1<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"212\" height=\"65\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-immediate-children.png\" alt=\"\" class=\"wp-image-5855\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Finding the leaf nodes<\/h3>\n\n\n\n<p>The leaf nodes are the nodes that have no children.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span>\n    c1.id, c1.title\n<span class=\"hljs-keyword\">FROM<\/span>\n    <span class=\"hljs-keyword\">category<\/span> c1\n        <span class=\"hljs-keyword\">LEFT<\/span> <span class=\"hljs-keyword\">JOIN<\/span>\n    <span class=\"hljs-keyword\">category<\/span> c2 <span class=\"hljs-keyword\">ON<\/span> c2.parent_id = c1.id\n<span class=\"hljs-keyword\">WHERE<\/span>\n    c2.id <span class=\"hljs-keyword\">IS<\/span> <span class=\"hljs-literal\">NULL<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"190\" height=\"154\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-leaf-nodes.png\" alt=\"Find leaf node\" class=\"wp-image-5856\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Querying the whole tree<\/h3>\n\n\n\n<p>The following <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-recursive-cte\/\">recursive common table expression (CTE)<\/a> retrieves the whole category tree. Notice that the <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-cte\/\">CTE<\/a> feature has been available since MySQL 8.0<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">WITH<\/span> <span class=\"hljs-keyword\">RECURSIVE<\/span> category_path (<span class=\"hljs-keyword\">id<\/span>, title, <span class=\"hljs-keyword\">path<\/span>) <span class=\"hljs-keyword\">AS<\/span>\n(\n  <span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">id<\/span>, title, title <span class=\"hljs-keyword\">as<\/span> <span class=\"hljs-keyword\">path<\/span>\n    <span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">category<\/span>\n    <span class=\"hljs-keyword\">WHERE<\/span> parent_id <span class=\"hljs-keyword\">IS<\/span> <span class=\"hljs-literal\">NULL<\/span>\n  <span class=\"hljs-keyword\">UNION<\/span> <span class=\"hljs-keyword\">ALL<\/span>\n  <span class=\"hljs-keyword\">SELECT<\/span> c.id, c.title, <span class=\"hljs-keyword\">CONCAT<\/span>(cp.path, <span class=\"hljs-string\">' &gt; '<\/span>, c.title)\n    <span class=\"hljs-keyword\">FROM<\/span> category_path <span class=\"hljs-keyword\">AS<\/span> cp <span class=\"hljs-keyword\">JOIN<\/span> <span class=\"hljs-keyword\">category<\/span> <span class=\"hljs-keyword\">AS<\/span> c\n      <span class=\"hljs-keyword\">ON<\/span> cp.id = c.parent_id\n)\n<span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> category_path\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">path<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"549\" height=\"230\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-whole-tree.png\" alt=\"adjacency list whole tree\" class=\"wp-image-5858\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-whole-tree.png 549w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-whole-tree-300x126.png 300w\" sizes=\"auto, (max-width: 549px) 100vw, 549px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Querying a sub-tree<\/h3>\n\n\n\n<p>The following query gets the sub-tree of <code>Phone &amp; Accessories<\/code> whose <code>id<\/code> is 7.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">WITH<\/span> <span class=\"hljs-keyword\">RECURSIVE<\/span> category_path (<span class=\"hljs-keyword\">id<\/span>, title, <span class=\"hljs-keyword\">path<\/span>) <span class=\"hljs-keyword\">AS<\/span>\n(\n  <span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">id<\/span>, title, title <span class=\"hljs-keyword\">as<\/span> <span class=\"hljs-keyword\">path<\/span>\n    <span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">category<\/span>\n    <span class=\"hljs-keyword\">WHERE<\/span> parent_id = <span class=\"hljs-number\">7<\/span>\n  <span class=\"hljs-keyword\">UNION<\/span> <span class=\"hljs-keyword\">ALL<\/span>\n  <span class=\"hljs-keyword\">SELECT<\/span> c.id, c.title, <span class=\"hljs-keyword\">CONCAT<\/span>(cp.path, <span class=\"hljs-string\">' &gt; '<\/span>, c.title)\n    <span class=\"hljs-keyword\">FROM<\/span> category_path <span class=\"hljs-keyword\">AS<\/span> cp <span class=\"hljs-keyword\">JOIN<\/span> <span class=\"hljs-keyword\">category<\/span> <span class=\"hljs-keyword\">AS<\/span> c\n      <span class=\"hljs-keyword\">ON<\/span> cp.id = c.parent_id\n)\n<span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> category_path\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">path<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"370\" height=\"158\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-subtree.png\" alt=\"adjacency list subtree\" class=\"wp-image-5859\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-subtree.png 370w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-subtree-300x128.png 300w\" sizes=\"auto, (max-width: 370px) 100vw, 370px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Querying a single path<\/h3>\n\n\n\n<p>To query a single path from bottom to top e.g., from <code>iOS<\/code> to <code>Electronics<\/code>, you use the following statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">WITH<\/span> <span class=\"hljs-keyword\">RECURSIVE<\/span> category_path (<span class=\"hljs-keyword\">id<\/span>, title, parent_id) <span class=\"hljs-keyword\">AS<\/span>\n(\n  <span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">id<\/span>, title, parent_id\n    <span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">category<\/span>\n    <span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">10<\/span> <span class=\"hljs-comment\">-- child node<\/span>\n  <span class=\"hljs-keyword\">UNION<\/span> <span class=\"hljs-keyword\">ALL<\/span>\n  <span class=\"hljs-keyword\">SELECT<\/span> c.id, c.title, c.parent_id\n    <span class=\"hljs-keyword\">FROM<\/span> category_path <span class=\"hljs-keyword\">AS<\/span> cp <span class=\"hljs-keyword\">JOIN<\/span> <span class=\"hljs-keyword\">category<\/span> <span class=\"hljs-keyword\">AS<\/span> c\n      <span class=\"hljs-keyword\">ON<\/span> cp.parent_id = c.id\n)\n<span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> category_path;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"241\" height=\"80\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-single-path.png\" alt=\"\" class=\"wp-image-5860\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Calculating the level of each node<\/h3>\n\n\n\n<p>Suppose the level of the root node is 0, each node underneath has a level that equals its parent node&#8217;s level plus 1.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">WITH<\/span> <span class=\"hljs-keyword\">RECURSIVE<\/span> category_path (<span class=\"hljs-keyword\">id<\/span>, title, lvl) <span class=\"hljs-keyword\">AS<\/span>\n(\n  <span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">id<\/span>, title, <span class=\"hljs-number\">0<\/span> lvl\n    <span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">category<\/span>\n    <span class=\"hljs-keyword\">WHERE<\/span> parent_id <span class=\"hljs-keyword\">IS<\/span> <span class=\"hljs-literal\">NULL<\/span>\n  <span class=\"hljs-keyword\">UNION<\/span> <span class=\"hljs-keyword\">ALL<\/span>\n  <span class=\"hljs-keyword\">SELECT<\/span> c.id, c.title,cp.lvl + <span class=\"hljs-number\">1<\/span>\n    <span class=\"hljs-keyword\">FROM<\/span> category_path <span class=\"hljs-keyword\">AS<\/span> cp <span class=\"hljs-keyword\">JOIN<\/span> <span class=\"hljs-keyword\">category<\/span> <span class=\"hljs-keyword\">AS<\/span> c\n      <span class=\"hljs-keyword\">ON<\/span> cp.id = c.parent_id\n)\n<span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> category_path\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> lvl;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"222\" height=\"301\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-level.png\" alt=\"\" class=\"wp-image-5861\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-level.png 222w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/adjacency-list-level-221x300.png 221w\" sizes=\"auto, (max-width: 222px) 100vw, 222px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Deleting a node and its descendants<\/h3>\n\n\n\n<p>To <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-delete\/\">delete <\/a>a node and its descendants, just remove the node itself, all the descendants will be deleted automatically by the <code>DELETE CASCADE<\/code> of the foreign key constraint.<\/p>\n\n\n\n<p>For example, to delete the <code>Laptops &amp; PC<\/code> node and its children ( <code>Laptops<\/code>, <code>PC<\/code>), you use the following statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">DELETE<\/span> <span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">category<\/span> \n<span class=\"hljs-keyword\">WHERE<\/span>\n    <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">2<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Deleting a node and promoting its descendants<\/h3>\n\n\n\n<p>To delete a non-leaf node and promote its descendants:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>First, update the <code>parent_id<\/code> of the immediate children of the node to the <code>id<\/code> of the new parent node.<\/li>\n\n\n\n<li>Then, delete the node.<\/li>\n<\/ol>\n\n\n\n<p>For example, to delete the <code>Smartphones<\/code> node and promote its children such as <code>Android<\/code>, <code>iOS<\/code>, <code>Other Smartphones<\/code> nodes:<\/p>\n\n\n\n<p>First, update the <code>parent_id<\/code> for all immediate children of <code>Smartphones<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-keyword\">category<\/span> \n<span class=\"hljs-keyword\">SET<\/span> \n    parent_id = <span class=\"hljs-number\">7<\/span> <span class=\"hljs-comment\">-- Phones &amp; Accessories<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span>\n    parent_id = <span class=\"hljs-number\">8<\/span>; <span class=\"hljs-comment\">-- Smartphones<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, delete the <code>Smartphones<\/code> node:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">DELETE<\/span> <span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">category<\/span> \n<span class=\"hljs-keyword\">WHERE<\/span>\n    <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">8<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Both statements should be wrapped in a single transaction:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">BEGIN<\/span>;\n\n<span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-keyword\">category<\/span> \n<span class=\"hljs-keyword\">SET<\/span> \n    parent_id = <span class=\"hljs-number\">7<\/span> \n<span class=\"hljs-keyword\">WHERE<\/span> \n    parent_id = <span class=\"hljs-number\">5<\/span>;\n\n<span class=\"hljs-keyword\">DELETE<\/span> <span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">category<\/span> \n<span class=\"hljs-keyword\">WHERE<\/span> \n    <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">8<\/span>;\n\n<span class=\"hljs-keyword\">COMMIT<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Moving a subtree<\/h3>\n\n\n\n<p>To move a subtree, just <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-update\/\">update <\/a>the <code>parent_id<\/code> of the top node of the subtree. For example, to move the <code>Cameras &amp; photo<\/code> as the children of <code>Phone and Accessories<\/code>, you use the following statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-keyword\">category<\/span> \n<span class=\"hljs-keyword\">SET<\/span> \n    parent_id = <span class=\"hljs-number\">7<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span>\n    <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">5<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this tutorial, you have learned how to use the adjacency list model to manage hierarchical data in MySQL.<\/p>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful? <\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"5850\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/\"\n\t\t\t\tdata-post-title=\"MySQL Adjacency List Model\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"5850\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/\"\n\t\t\t\tdata-post-title=\"MySQL Adjacency List Model\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, you will learn how to use adjacency list model for managing hierarchical data in MySQL.<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":174,"menu_order":103,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-5850","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MySQL Adjacency List Model<\/title>\n<meta name=\"description\" content=\"In this tutorial, you will learn how to use the MySQL adjacency list model for managing hierarchical data in MySQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL Adjacency List Model\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you will learn how to use the MySQL adjacency list model for managing hierarchical data in MySQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-04T14:37:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/mysql-adjacency-list.png\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-adjacency-list-tree\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-adjacency-list-tree\\\/\",\"name\":\"MySQL Adjacency List Model\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-adjacency-list-tree\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-adjacency-list-tree\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/mysql-adjacency-list.png\",\"datePublished\":\"2017-06-16T09:56:42+00:00\",\"dateModified\":\"2024-01-04T14:37:34+00:00\",\"description\":\"In this tutorial, you will learn how to use the MySQL adjacency list model for managing hierarchical data in MySQL.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-adjacency-list-tree\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-adjacency-list-tree\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-adjacency-list-tree\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/mysql-adjacency-list.png\",\"contentUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/mysql-adjacency-list.png\",\"width\":830,\"height\":300},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-adjacency-list-tree\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Basics\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"MySQL Adjacency List Model\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL Adjacency List Model","description":"In this tutorial, you will learn how to use the MySQL adjacency list model for managing hierarchical data in MySQL.","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:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/","og_locale":"en_US","og_type":"article","og_title":"MySQL Adjacency List Model","og_description":"In this tutorial, you will learn how to use the MySQL adjacency list model for managing hierarchical data in MySQL.","og_url":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/","og_site_name":"MySQL Tutorial","article_modified_time":"2024-01-04T14:37:34+00:00","og_image":[{"url":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/mysql-adjacency-list.png","type":"","width":"","height":""}],"twitter_misc":{"Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/","url":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/","name":"MySQL Adjacency List Model","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/#primaryimage"},"image":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/#primaryimage"},"thumbnailUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/mysql-adjacency-list.png","datePublished":"2017-06-16T09:56:42+00:00","dateModified":"2024-01-04T14:37:34+00:00","description":"In this tutorial, you will learn how to use the MySQL adjacency list model for managing hierarchical data in MySQL.","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/#primaryimage","url":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/mysql-adjacency-list.png","contentUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/06\/mysql-adjacency-list.png","width":830,"height":300},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-adjacency-list-tree\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Basics","item":"https:\/\/www.mysqltutorial.org\/mysql-basics\/"},{"@type":"ListItem","position":3,"name":"MySQL Adjacency List Model"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/5850","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=5850"}],"version-history":[{"count":4,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/5850\/revisions"}],"predecessor-version":[{"id":14199,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/5850\/revisions\/14199"}],"up":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/174"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=5850"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}