{"id":4056,"date":"2024-09-19T07:15:33","date_gmt":"2024-09-18T23:15:33","guid":{"rendered":"https:\/\/five.co\/?p=4056"},"modified":"2026-05-04T14:59:17","modified_gmt":"2026-05-04T06:59:17","slug":"multiple-with-statements-in-sql","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/multiple-with-statements-in-sql\/","title":{"rendered":"Multiple WITH Statements in SQL: Comprehensive Guide"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">SQL&#8217;s WITH clause, also known as Common Table Expressions (CTEs), is a great for complex queries. But did you know you can use multiple WITH statements in a single query? <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s dive into multiple WITH statements and see how they can change your SQL queries.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">The Basics: What Are WITH Statements?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before we jump into multiple WITH statements, let&#8217;s quickly recap what a single WITH statement does:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WITH sales_summary AS (\n    SELECT \n        product_id,\n        SUM(quantity) as total_quantity,\n        SUM(price * quantity) as total_revenue\n    FROM sales\n    GROUP BY product_id\n)\nSELECT * FROM sales_summary WHERE total_revenue > 10000;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> sales_summary <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        product_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(quantity) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> total_quantity,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(price * quantity) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> total_revenue<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> product_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales_summary <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> total_revenue <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">10000<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query creates a temporary named result set, <code>sales_summary<\/code>, which we can then use in the main query. It&#8217;s like creating a virtual table that exists only for the duration of the query.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Stepping It Up: Using Multiple WITH Statements<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Now, let&#8217;s see how we can use multiple WITH statements to break down complex queries into manageable pieces:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WITH \nmonthly_sales AS (\n    SELECT \n        DATE_TRUNC('month', sale_date) as month,\n        product_id,\n        SUM(quantity) as total_quantity,\n        SUM(price * quantity) as total_revenue\n    FROM sales\n    GROUP BY DATE_TRUNC('month', sale_date), product_id\n),\ntop_products AS (\n    SELECT \n        month,\n        product_id,\n        total_revenue,\n        RANK() OVER (PARTITION BY month ORDER BY total_revenue DESC) as rank\n    FROM monthly_sales\n)\nSELECT tp.month, tp.product_id, p.product_name, tp.total_revenue\nFROM top_products tp\nJOIN products p ON tp.product_id = p.id\nWHERE tp.rank &lt;= 3\nORDER BY tp.month, tp.total_revenue DESC;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">monthly_sales <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, sale_date) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        product_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(quantity) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> total_quantity,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(price * quantity) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> total_revenue<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, sale_date), product_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">top_products <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        product_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        total_revenue,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">RANK<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> total_revenue <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> rank<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> monthly_sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">tp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">month<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">tp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">product_id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">product_name<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">tp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_revenue<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> top_products tp<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> products p <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">tp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">product_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">tp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">rank<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">3<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">tp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">month<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">tp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_revenue<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:21px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">In this example, we&#8217;ve used two WITH clauses:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><code>monthly_sales<\/code>: Calculates monthly sales for each product.<\/li>\n\n\n\n<li><code>top_products<\/code>: Ranks products within each month based on revenue.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">The main query then uses these CTEs to find the top 3 products for each month.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Scenario: Analyzing Customer Behavior<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s look at a real-world scenario where multiple WITH statements shine. Imagine you&#8217;re an e-commerce analyst trying to identify your most valuable customers based on their purchasing habits and demographics.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WITH \ncustomer_purchases AS (\n    SELECT \n        customer_id,\n        COUNT(DISTINCT order_id) as total_orders,\n        SUM(order_total) as total_spent,\n        AVG(order_total) as avg_order_value\n    FROM orders\n    WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)\n    GROUP BY customer_id\n),\ncustomer_demographics AS (\n    SELECT \n        c.id as customer_id,\n        c.age,\n        c.gender,\n        a.city,\n        a.country\n    FROM customers c\n    JOIN addresses a ON c.address_id = a.id\n),\ncustomer_segments AS (\n    SELECT \n        cp.*,\n        cd.age,\n        cd.gender,\n        cd.city,\n        cd.country,\n        CASE \n            WHEN cp.total_spent > 1000 AND cp.total_orders > 10 THEN 'High Value'\n            WHEN cp.total_spent > 500 OR cp.total_orders > 5 THEN 'Medium Value'\n            ELSE 'Low Value'\n        END as customer_segment\n    FROM customer_purchases cp\n    JOIN customer_demographics cd ON cp.customer_id = cd.customer_id\n)\nSELECT \n    customer_segment,\n    COUNT(*) as segment_count,\n    AVG(age) as avg_age,\n    AVG(total_spent) as avg_total_spent,\n    AVG(avg_order_value) as avg_order_value\nFROM customer_segments\nGROUP BY customer_segment\nORDER BY avg_total_spent DESC;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">customer_purchases <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        customer_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">DISTINCT<\/span><span style=\"color: #ABB2BF\"> order_id) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> total_orders,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(order_total) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> total_spent,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(order_total) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> avg_order_value<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> order_date <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> DATE_SUB(CURRENT_DATE, INTERVAL <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">YEAR<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> customer_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">customer_demographics <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> customer_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">age<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">gender<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">a<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">city<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">a<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">country<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> addresses a <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">address_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">a<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">customer_segments <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        cp.*,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">cd<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">age<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">cd<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">gender<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">cd<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">city<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">cd<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">country<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">cp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_spent<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1000<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">cp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_orders<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;High Value&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">cp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_spent<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">500<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">OR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">cp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_orders<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">5<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Medium Value&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Low Value&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> customer_segment<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customer_purchases cp<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> customer_demographics cd <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">cp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">cd<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    customer_segment,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> segment_count,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(age) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> avg_age,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(total_spent) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> avg_total_spent,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(avg_order_value) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> avg_order_value<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customer_segments<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> customer_segment<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> avg_total_spent <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query uses three WITH statements to:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Calculate purchase metrics for each customer<\/li>\n\n\n\n<li>Gather demographic information<\/li>\n\n\n\n<li>Segment customers based on their value<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">The main query then provides a summary of each customer segment.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Optimizing Performance with Multiple WITH Statements<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While multiple WITH statements can make your queries more readable, they can also improve performance in certain scenarios:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1. Avoiding Redundant Calculations<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">By breaking down complex queries into smaller CTEs, you can avoid repeating the same calculations multiple times. This is especially useful when you need to reference the same subquery in multiple places.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">2. Improved Query Planning<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Database engines can often optimize queries with multiple WITH statements more effectively. By separating complex logic into distinct CTEs, you give the query planner more opportunities to optimize each part independently.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">3. Simplifying UNION Operations<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">When you need to combine results from multiple similar queries, using WITH statements can make UNION operations much cleaner:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WITH \nactive_users AS (\n    SELECT user_id, 'active' as status\n    FROM users\n    WHERE last_login > DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)\n),\ninactive_users AS (\n    SELECT user_id, 'inactive' as status\n    FROM users\n    WHERE last_login &lt;= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)\n)\nSELECT * FROM active_users\nUNION ALL\nSELECT * FROM inactive_users;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">active_users <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> user_id, <\/span><span style=\"color: #98C379\">&#39;active&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">status<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> users<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> last_login <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> DATE_SUB(CURRENT_DATE, INTERVAL <\/span><span style=\"color: #D19A66\">30<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DAY<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">inactive_users <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> user_id, <\/span><span style=\"color: #98C379\">&#39;inactive&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">status<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> users<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> last_login <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> DATE_SUB(CURRENT_DATE, INTERVAL <\/span><span style=\"color: #D19A66\">30<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DAY<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> active_users<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> inactive_users;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This approach is much more readable than nesting subqueries within a UNION statement.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong style=\"background-color: rgb(69, 136, 216); color: rgb(241, 235, 218); font-size: calc(12px + 0.390625vw); font-weight: bold;\">Multiple WITH Statements with a Rapid Database Builder<\/strong><\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When working with SQL, executing efficient queries is straightforward, but sometimes complex operations, like using multiple WITH statements, require extra attention.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is where rapid database builders like Five come into play. Five provides a MySQL database for your application and generates an automatic UI, making it easier to interact with and manage your data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">With Five, you can handle tasks like chaining multiple CTEs in SQL intuitively through its generated forms and tables. This allows you to focus on writing accurate queries without worrying about the complexities of structuring multiple <code>WITH<\/code> statements manually.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five also allows you to create interactive forms, tables, dynamic charts, comprehensive PDF reports, and dashboards that are automatically generated based on your database schema. This makes it easier to visualise and analyze data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In addition, Five lets you write custom JavaScript and TypeScript functions, giving you the flexibility to implement custom business logic.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once your application is ready, Five makes deployment easy with just a few clicks, allowing you to deploy your MySQL- based application to a secure, scalable cloud infrastructure.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This lets you focus on developing, while Five handles the complexities of cloud deployment and infrastructure management. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re serious about using SQL efficiently, especially when using multiple WITH statements, give Five a try.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Build Your Database In 3 Steps<\/strong><br \/><span style=\"font-size: 14pt;\">Start Developing Today<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\" data-wplink-edit=\"true\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br \/><\/a><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Advanced Techniques: Recursive WITH Statements<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">One feature of WITH statements is the ability to create recursive queries. This is particularly useful for handling hierarchical or graph-like data structures.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s look at an example where we have an employee table with a self-referencing manager_id column:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WITH RECURSIVE employee_hierarchy AS (\n    -- Base case: top-level employees (no manager)\n    SELECT id, name, manager_id, 0 as level\n    FROM employees\n    WHERE manager_id IS NULL\n\n    UNION ALL\n\n    -- Recursive case: employees with managers\n    SELECT e.id, e.name, e.manager_id, eh.level + 1\n    FROM employees e\n    JOIN employee_hierarchy eh ON e.manager_id = eh.id\n)\nSELECT \n    id,\n    name,\n    level,\n    REPEAT('  ', level) || name as hierarchical_name\nFROM employee_hierarchy\nORDER BY level, name;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">RECURSIVE<\/span><span style=\"color: #ABB2BF\"> employee_hierarchy <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Base case: top-level employees (no manager)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> id, <\/span><span style=\"color: #C678DD\">name<\/span><span style=\"color: #ABB2BF\">, manager_id, <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">level<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> manager_id <\/span><span style=\"color: #C678DD\">IS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Recursive case: employees with managers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">name<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">manager_id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">eh<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">level<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees e<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> employee_hierarchy eh <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">manager_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">eh<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">name<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">level<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">REPEAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;  &#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">level<\/span><span style=\"color: #ABB2BF\">) || <\/span><span style=\"color: #C678DD\">name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> hierarchical_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employee_hierarchy<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">level<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">name<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query uses a recursive WITH statement to build a hierarchical view of employees, showing their level in the organization and indenting their names accordingly.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Combining Multiple WITH Statements and Recursive Queries<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">You can even combine multiple WITH statements, including recursive ones, to tackle extremely complex problems. Here&#8217;s an example that finds the shortest path between two nodes in a graph:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WITH RECURSIVE\ngraph_edges AS (\n    -- Define the graph edges\n    SELECT 'A' as source, 'B' as target, 1 as distance UNION ALL\n    SELECT 'B', 'C', 2 UNION ALL\n    SELECT 'A', 'C', 3 UNION ALL\n    SELECT 'C', 'D', 1 UNION ALL\n    SELECT 'B', 'D', 4\n),\npaths(source, target, path, total_distance) AS (\n    -- Base case: direct connections\n    SELECT source, target, ARRAY&#091;source, target&#093;, distance\n    FROM graph_edges\n\n    UNION ALL\n\n    -- Recursive case: extend paths\n    SELECT p.source, ge.target, p.path || ge.target, p.total_distance + ge.distance\n    FROM paths p\n    JOIN graph_edges ge ON p.target = ge.source\n    WHERE NOT ge.target = ANY(p.path)  -- Avoid cycles\n)\nSELECT path, total_distance\nFROM paths\nWHERE source = 'A' AND target = 'D'\nORDER BY total_distance\nLIMIT 1;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">RECURSIVE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">graph_edges <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Define the graph edges<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;A&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> source, <\/span><span style=\"color: #98C379\">&#39;B&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">target<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> distance <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;B&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;A&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;D&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;B&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;D&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">4<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">paths(source, <\/span><span style=\"color: #C678DD\">target<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">path<\/span><span style=\"color: #ABB2BF\">, total_distance) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Base case: direct connections<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> source, <\/span><span style=\"color: #C678DD\">target<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">ARRAY<\/span><span style=\"color: #E06C75\">&#091;source, target&#093;<\/span><span style=\"color: #ABB2BF\">, distance<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> graph_edges<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Recursive case: extend paths<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">source<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">ge<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">target<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">path<\/span><span style=\"color: #ABB2BF\"> || <\/span><span style=\"color: #D19A66\">ge<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">target<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_distance<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #D19A66\">ge<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">distance<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> paths p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> graph_edges ge <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">target<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ge<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">source<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NOT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ge<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">target<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> ANY(<\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">path<\/span><span style=\"color: #ABB2BF\">)  <\/span><span style=\"color: #7F848E; font-style: italic\">-- Avoid cycles<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">path<\/span><span style=\"color: #ABB2BF\">, total_distance<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> paths<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> source <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;A&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">target<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;D&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> total_distance<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">LIMIT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query first defines the graph edges, then uses a recursive CTE to find all possible paths, and finally selects the shortest path from &#8216;A&#8217; to &#8216;D&#8217;.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">FAQ<\/h2>\n\n\n<div id=\"rank-math-faq\" class=\"rank-math-block\">\n<ol class=\"rank-math-list \">\n<li id=\"faq-question-1771985858796\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong>What is a WITH statement in SQL?<\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>A <code>WITH<\/code> statement (also called a Common Table Expression or CTE) defines a temporary named result set that can be referenced in a query. It helps make complex SQL easier to read and maintain.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985909936\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong><strong>Can you use multiple WITH statements in a single SQL query?<\/strong><\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Yes, you can define <strong>multiple CTEs<\/strong> in one <code>WITH<\/code> clause by separating them with commas. This lets you build layered results and reuse intermediate datasets before your final <code>SELECT<\/code>.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985985770\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong>Do CTEs execute in order?<\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Each CTE is defined before the final query, but they are executed logically as needed by the query optimizer. You can reference earlier CTEs inside later ones to create step\u2011by\u2011step transformations.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985994514\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong><strong>How do multiple WITH statements improve query readability?<\/strong><\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>By breaking complex logic into named building blocks, CTEs make long queries easier to understand and debug. You can isolate filtering, aggregation, or transformation steps in separate CTEs.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771986003404\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong><strong><strong>What are common use cases for multiple WITH statements?<\/strong><\/strong><\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Typical use cases include preparing intermediate result sets, joining multiple preprocessed tables, performing recursive queries, or aggregating data before the final output.<\/p>\n\n<\/div>\n<\/li>\n<\/ol>\n<\/div><\/div><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Wrapping Up: Multiple WITH Statements<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">We&#8217;ve explored the versatility and efficiency of using multiple WITH statements in SQL. From simplifying complex queries to enabling advanced recursive operations, this technique is a valuable addition to any SQL developer&#8217;s toolkit.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Key takeaways:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Multiple WITH statements can break down complex queries into manageable, reusable parts.<\/li>\n\n\n\n<li>They can improve query readability and maintainability.<\/li>\n\n\n\n<li>In many cases, they can enhance query performance by avoiding redundant calculations.<\/li>\n\n\n\n<li>Recursive WITH statements open up new possibilities for handling hierarchical and graph data.<\/li>\n\n\n\n<li>Combining multiple WITH statements, including recursive ones, allows for solving extremely complex problems elegantly.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">As you continue to work with SQL, experiment with multiple WITH statements in your own queries. You&#8217;ll likely find that they not only make your code cleaner but also open up new ways of thinking about and structuring your database operations.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL&#8217;s WITH clause, also known as Common Table Expressions (CTEs), is a great for complex queries. But did you know you can use multiple WITH statements in a single query? Let&#8217;s dive into multiple WITH statements and see how they can change your SQL queries. The Basics: What Are WITH Statements? Before we jump into [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4059,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4056","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Multiple-WITH-Statements-in-SQL.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Multiple-WITH-Statements-in-SQL-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Multiple-WITH-Statements-in-SQL-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Multiple-WITH-Statements-in-SQL-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Multiple-WITH-Statements-in-SQL-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Multiple-WITH-Statements-in-SQL.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Multiple-WITH-Statements-in-SQL.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"SQL&#8217;s WITH clause, also known as Common Table Expressions (CTEs), is a great for complex queries. But did you know you can use multiple WITH statements in a single query? Let&#8217;s dive into multiple WITH statements and see how they can change your SQL queries. The Basics: What Are WITH Statements? Before we jump into&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4056","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4056"}],"version-history":[{"count":1,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4056\/revisions"}],"predecessor-version":[{"id":17803,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4056\/revisions\/17803"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4059"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4056"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4056"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4056"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}