{"id":1721,"date":"2026-04-16T11:39:55","date_gmt":"2026-04-16T09:39:55","guid":{"rendered":"https:\/\/www.innospira.fr\/?p=1721"},"modified":"2026-04-16T11:39:56","modified_gmt":"2026-04-16T09:39:56","slug":"optimiser-ses-requetes-sql-avec-linstruction-with","status":"publish","type":"post","link":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/","title":{"rendered":"Optimiser ses requ\u00eates SQL avec l&rsquo;instruction WITH"},"content":{"rendered":"\n<p>Optimiser ses requ\u00eates SQL, c&rsquo;est indispensable quand on travaille r\u00e9guli\u00e8rement sur un ensemble de tables ou base de donn\u00e9es. Tu as sans doute d\u00e9j\u00e0 \u00e9crit des requ\u00eates SQL qui commencent \u00e0 ressembler \u00e0 une usine \u00e0 gaz avec beaucoup (trop) de sous-requ\u00eates imbriqu\u00e9es. C&rsquo;est l\u00e0 qu&rsquo;intervient l&rsquo;instruction <strong>WITH<\/strong>, aussi appel\u00e9e <strong>CTE<\/strong> (Common Table Expression). Dans cet article, on va voir ce que c&rsquo;est, pourquoi c&rsquo;est utile, et surtout comment s&rsquo;en servir concr\u00e8tement \ud83d\ude09.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-large-font-size\">C&rsquo;est quoi une CTE ?<\/h2>\n\n\n\n<p>Une CTE (Common Table Expression) est une <strong>table temporaire nomm\u00e9e<\/strong>, d\u00e9finie au d\u00e9but d&rsquo;une requ\u00eate SQL gr\u00e2ce au mot-cl\u00e9 <code>WITH<\/code>. Elle n&rsquo;existe que le temps de l&rsquo;ex\u00e9cution de la requ\u00eate principale : elle n&rsquo;est pas stock\u00e9e en base, elle ne cr\u00e9e pas de vue, elle est simplement disponible comme une table virtuelle pour la requ\u00eate qui suit.<\/p>\n\n\n\n<p>La syntaxe de base ressemble \u00e0 \u00e7a :<\/p>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-d4f7421c alignfull uagb-is-root-container\"><div class=\"uagb-container-inner-blocks-wrap\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" 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;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 0 16px;font-size:0.8em;width:100%;text-align:left;background-color:#1E1E1E;font-style:italic;color:#D4D4D4\"><span style=\"border-bottom:1px solid rgba(234, 191, 191, 0.2)\">SQL<\/span><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;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 nom_cte AS (\n  SELECT ...\n  FROM ...\n  WHERE ...\n)\nSELECT *\nFROM nom_cte;<\/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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">WITH<\/span><span style=\"color: #D4D4D4\"> nom_cte <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> ...<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> ...<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> ...<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> nom_cte;<\/span><\/span><\/code><\/pre><\/div>\n<\/div><\/div>\n\n\n\n<p>Simple, lisible, et puissant. Voyons maintenant pourquoi tu devrais l&rsquo;utiliser.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-large-font-size\">Pourquoi utiliser une CTE pour optimiser ses requ\u00eates ?<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"> Lisibilit\u00e9 et maintenabilit\u00e9 du code<\/h3>\n\n\n\n<p>Le premier avantage des CTEs, c&rsquo;est la <strong>clart\u00e9<\/strong>. Plut\u00f4t que d&rsquo;imbriquer des sous-requ\u00eates dans des sous-requ\u00eates, tu d\u00e9composes ta logique en blocs nomm\u00e9s et lisibles. Ton coll\u00e8gue (ou toi-m\u00eame dans 6 mois) te remerciera.<\/p>\n\n\n\n<p>Compare ces deux approches pour r\u00e9cup\u00e9rer les clients ayant pass\u00e9 plus de 3 commandes :<\/p>\n\n\n\n<p><strong>Sans CTE (sous-requ\u00eate imbriqu\u00e9e) :<\/strong><\/p>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-b8a7ccd7 alignfull uagb-is-root-container\"><div class=\"uagb-container-inner-blocks-wrap\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" 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;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 0 16px;font-size:0.8em;width:100%;text-align:left;background-color:#1E1E1E;font-style:italic;color:#D4D4D4\"><span style=\"border-bottom:1px solid rgba(234, 191, 191, 0.2)\">SQL<\/span><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;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>SELECT *\nFROM clients\nWHERE id IN (\n  SELECT client_id\n  FROM commandes\n  GROUP BY client_id\n  HAVING COUNT(*) > 3\n);<\/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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> clients<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> id <\/span><span style=\"color: #569CD6\">IN<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> client_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> commandes<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">GROUP BY<\/span><span style=\"color: #D4D4D4\"> client_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">HAVING<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(*) &gt; <\/span><span style=\"color: #B5CEA8\">3<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">);<\/span><\/span><\/code><\/pre><\/div>\n<\/div><\/div>\n\n\n\n<p><strong>Avec l&rsquo;utilisation de CTE :<\/strong><\/p>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-980f90cb alignfull uagb-is-root-container\"><div class=\"uagb-container-inner-blocks-wrap\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" 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;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 0 16px;font-size:0.8em;width:100%;text-align:left;background-color:#1E1E1E;font-style:italic;color:#D4D4D4\"><span style=\"border-bottom:1px solid rgba(234, 191, 191, 0.2)\">SQL<\/span><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;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 clients_actifs AS (\n  SELECT client_id\n  FROM commandes\n  GROUP BY client_id\n  HAVING COUNT(*) > 3\n)\nSELECT *\nFROM clients\nWHERE id IN (SELECT client_id FROM clients_actifs);<\/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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">WITH<\/span><span style=\"color: #D4D4D4\"> clients_actifs <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> client_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> commandes<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">GROUP BY<\/span><span style=\"color: #D4D4D4\"> client_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">HAVING<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(*) &gt; <\/span><span style=\"color: #B5CEA8\">3<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> clients<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> id <\/span><span style=\"color: #569CD6\">IN<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> client_id <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> clients_actifs);<\/span><\/span><\/code><\/pre><\/div>\n<\/div><\/div>\n\n\n\n<p>La logique est s\u00e9par\u00e9e, le bloc s&rsquo;appelle <code>clients_actifs<\/code> : on sait imm\u00e9diatement ce qu&rsquo;il repr\u00e9sente.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"> R\u00e9utilisabilit\u00e9 dans la m\u00eame requ\u00eate<\/h3>\n\n\n\n<p>Une CTE peut \u00eatre <strong>r\u00e9f\u00e9renc\u00e9e plusieurs fois<\/strong> dans la requ\u00eate principale, ce qui \u00e9vite de r\u00e9p\u00e9ter le m\u00eame bloc de code. C&rsquo;est particuli\u00e8rement utile quand tu as une logique de filtrage ou d&rsquo;agr\u00e9gation complexe que tu dois appliquer \u00e0 plusieurs endroits.<\/p>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-d0611aee alignfull uagb-is-root-container\"><div class=\"uagb-container-inner-blocks-wrap\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" 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;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 0 16px;font-size:0.8em;width:100%;text-align:left;background-color:#1E1E1E;font-style:italic;color:#D4D4D4\"><span style=\"border-bottom:1px solid rgba(234, 191, 191, 0.2)\">SQL<\/span><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;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 ventes_2026 AS (\n  SELECT vendeur_id, SUM(montant) AS total\n  FROM ventes\n  WHERE YEAR(date_vente) = 2026\n  GROUP BY vendeur_id\n)\nSELECT v.vendeur_id, v.total, e.nom\nFROM ventes_2026 v\nJOIN employes e ON e.id = v.vendeur_id\nWHERE v.total > (SELECT AVG(total) FROM ventes_2026);<\/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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">WITH<\/span><span style=\"color: #D4D4D4\"> ventes_2026 <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> vendeur_id, <\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(montant) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> ventes<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">YEAR<\/span><span style=\"color: #D4D4D4\">(date_vente) = <\/span><span style=\"color: #B5CEA8\">2026<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">GROUP BY<\/span><span style=\"color: #D4D4D4\"> vendeur_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> v.vendeur_id, v.total, e.nom<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> ventes_2026 v<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">JOIN<\/span><span style=\"color: #D4D4D4\"> employes e <\/span><span style=\"color: #569CD6\">ON<\/span><span style=\"color: #D4D4D4\"> e.id = v.vendeur_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> v.total &gt; (<\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">AVG<\/span><span style=\"color: #D4D4D4\">(total) <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> ventes_2026);<\/span><\/span><\/code><\/pre><\/div>\n<\/div><\/div>\n\n\n\n<p>Ici, <code>ventes_2026<\/code> est utilis\u00e9e deux fois : dans le <code>JOIN<\/code> et dans la sous-requ\u00eate du <code>WHERE<\/code>. Sans CTE, il faudrait dupliquer toute la logique.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"> CTEs multiples : cha\u00eener les \u00e9tapes<\/h3>\n\n\n\n<p>Tu peux d\u00e9finir <strong>plusieurs CTEs<\/strong> dans la m\u00eame requ\u00eate, et m\u00eame les faire r\u00e9f\u00e9rencer les unes les autres. C&rsquo;est id\u00e9al pour construire une logique \u00e9tape par \u00e9tape.<\/p>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-7a4cbe34 alignfull uagb-is-root-container\"><div class=\"uagb-container-inner-blocks-wrap\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" 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;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 0 16px;font-size:0.8em;width:100%;text-align:left;background-color:#1E1E1E;font-style:italic;color:#D4D4D4\"><span style=\"border-bottom:1px solid rgba(234, 191, 191, 0.2)\">SQL<\/span><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;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\ncommandes_recentes AS (\n  SELECT *\n  FROM commandes\n  WHERE date_commande >= DATE_SUB(NOW(), INTERVAL 30 DAY)\n),\ntotal_par_client AS (\n  SELECT client_id, SUM(montant) AS total\n  FROM commandes_recentes\n  GROUP BY client_id\n),\ntop_clients AS (\n  SELECT client_id, total\n  FROM total_par_client\n  WHERE total > 500\n)\nSELECT c.nom, c.email, t.total\nFROM clients c\nJOIN top_clients t ON t.client_id = c.id\nORDER BY t.total 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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">WITH<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">commandes_recentes <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> commandes<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> date_commande &gt;= DATE_SUB(<\/span><span style=\"color: #569CD6\">NOW<\/span><span style=\"color: #D4D4D4\">(), INTERVAL <\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">DAY<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">total_par_client <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> client_id, <\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(montant) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> commandes_recentes<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">GROUP BY<\/span><span style=\"color: #D4D4D4\"> client_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">top_clients <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> client_id, total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> total_par_client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> total &gt; <\/span><span style=\"color: #B5CEA8\">500<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> c.nom, c.email, t.total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> clients c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">JOIN<\/span><span style=\"color: #D4D4D4\"> top_clients t <\/span><span style=\"color: #569CD6\">ON<\/span><span style=\"color: #D4D4D4\"> t.client_id = c.id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> t.total <\/span><span style=\"color: #569CD6\">DESC<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n<\/div><\/div>\n\n\n\n<p>On d\u00e9compose la logique en 3 \u00e9tapes claires : filtrer les commandes r\u00e9centes, calculer les totaux, garder uniquement les tops clients. Chaque CTE a une responsabilit\u00e9 unique.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-large-font-size\">Les CTEs r\u00e9cursives : un cas d&rsquo;usage puissant<\/h2>\n\n\n\n<p>Les CTEs supportent la <strong>r\u00e9cursivit\u00e9<\/strong>, ce qui les rend indispensables pour travailler avec des donn\u00e9es hi\u00e9rarchiques comme des arborescences (cat\u00e9gories, organigrammes, menus\u2026).<\/p>\n\n\n\n<p>Voici un exemple concret pour afficher toute une hi\u00e9rarchie de cat\u00e9gories :<\/p>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-5d6c7de1 alignfull uagb-is-root-container\"><div class=\"uagb-container-inner-blocks-wrap\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" 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;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 0 16px;font-size:0.8em;width:100%;text-align:left;background-color:#1E1E1E;font-style:italic;color:#D4D4D4\"><span style=\"border-bottom:1px solid rgba(234, 191, 191, 0.2)\">SQL<\/span><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;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 arbre_categories AS (\n  -- Ancre : point de d\u00e9part (cat\u00e9gories racines)\n  SELECT id, nom, parent_id, 0 AS niveau\n  FROM categories\n  WHERE parent_id IS NULL\n  \n  UNION ALL\n  \n  -- Partie r\u00e9cursive : enfants de chaque noeud\n  SELECT c.id, c.nom, c.parent_id, a.niveau + 1\n  FROM categories c\n  JOIN arbre_categories a ON a.id = c.parent_id\n)\nSELECT *\nFROM arbre_categories\nORDER BY niveau, nom;<\/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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">WITH<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">RECURSIVE<\/span><span style=\"color: #D4D4D4\"> arbre_categories <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #6A9955\">-- Ancre : point de d\u00e9part (cat\u00e9gories racines)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> id, nom, parent_id, <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> niveau<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> categories<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> parent_id <\/span><span style=\"color: #569CD6\">IS<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">NULL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">UNION ALL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #6A9955\">-- Partie r\u00e9cursive : enfants de chaque noeud<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> c.id, c.nom, c.parent_id, a.niveau + <\/span><span style=\"color: #B5CEA8\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> categories c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">JOIN<\/span><span style=\"color: #D4D4D4\"> arbre_categories a <\/span><span style=\"color: #569CD6\">ON<\/span><span style=\"color: #D4D4D4\"> a.id = c.parent_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> arbre_categories<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> niveau, nom;<\/span><\/span><\/code><\/pre><\/div>\n<\/div><\/div>\n\n\n\n<p>La CTE s&rsquo;appelle elle-m\u00eame jusqu&rsquo;\u00e0 ce qu&rsquo;il n&rsquo;y ait plus d&rsquo;enfants \u00e0 trouver. Le moteur SQL prends les r\u00e9sultats de la premi\u00e8re it\u00e9ration (stock\u00e9e dans <code>arbre_categories<\/code>) puis cherche tous les enfants dont le <code>parent_id<\/code> correspond. C&rsquo;est quelque chose qu&rsquo;il serait tr\u00e8s difficile de faire proprement sans cette fonctionnalit\u00e9.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-large-font-size\">CTEs et performances : ce qu&rsquo;il faut vraiment savoir<\/h2>\n\n\n\n<p>Un point souvent mal compris : <strong>les CTEs ne sont pas automatiquement plus rapides qu&rsquo;une sous-requ\u00eate<\/strong>. Leur avantage principal est la lisibilit\u00e9 (c&rsquo;est aussi une fa\u00e7on d&rsquo;optimiser ses requ\u00eates \ud83d\ude09). Cependant, quelques \u00e9l\u00e9ments m\u00e9ritent attention :<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Mat\u00e9rialisation vs. inline<\/h3>\n\n\n\n<p>Selon le moteur SQL utilis\u00e9, une CTE peut \u00eatre :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Inline<\/strong> : le moteur la \u00ab\u00a0d\u00e9plie\u00a0\u00bb comme une sous-requ\u00eate et l&rsquo;optimise globalement avec la requ\u00eate principale. C&rsquo;est ce que fait PostgreSQL par d\u00e9faut depuis la version 12.<\/li>\n\n\n\n<li><strong>Mat\u00e9rialis\u00e9e<\/strong> : le moteur ex\u00e9cute la CTE une fois, stocke le r\u00e9sultat en m\u00e9moire, puis l&rsquo;utilise. MySQL\/MariaDB mat\u00e9rialise les CTEs, ce qui peut introduire un co\u00fbt si le r\u00e9sultat est volumineux.<\/li>\n<\/ul>\n\n\n\n<p>Sur <a href=\"https:\/\/www.postgresql.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL<\/a>, tu peux forcer la mat\u00e9rialisation avec <code>WITH nom AS MATERIALIZED (...)<\/code> ou l&#8217;emp\u00eacher avec <code>NOT MATERIALIZED<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u00c9viter de charger inutilement<\/h3>\n\n\n\n<p>Si ta CTE charge des millions de lignes mais que ta requ\u00eate finale n&rsquo;en utilise que quelques dizaines, pense \u00e0 filtrer d\u00e8s la CTE plut\u00f4t qu&rsquo;apr\u00e8s. Les <strong>index ne traversent pas toujours la barri\u00e8re<\/strong> d&rsquo;une CTE mat\u00e9rialis\u00e9e : mets tes conditions de filtrage au bon endroit. Il faut toujours bien garder en t\u00eate les index pour optimiser les requ\u00eates SQL.<\/p>\n\n\n\n<p>En parlant d&rsquo;optimisation des performances, si tu veux aller plus loin sur ce sujet, j&rsquo;ai \u00e9crit un article complet sur <a href=\"https:\/\/www.innospira.fr\/index.php\/2025\/07\/18\/comment-ameliorer-les-performances-de-ton-application-web\/\" target=\"_blank\" rel=\"noopener\">comment am\u00e9liorer les performances de ton application web<\/a>. Les bonnes pratiques c\u00f4t\u00e9 base de donn\u00e9es s&rsquo;inscrivent dans une d\u00e9marche globale d&rsquo;optimisation.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">CTEs vs vues vs tables temporaires<\/h3>\n\n\n\n<p>Pour t&rsquo;aider \u00e0 choisir la bonne approche :<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Caract\u00e9ristique<\/th><th>CTE<\/th><th>Vue<\/th><th>Table temporaire<\/th><\/tr><\/thead><tbody><tr><td>Dur\u00e9e de vie<\/td><td>Requ\u00eate unique<\/td><td>Persistante<\/td><td>Session<\/td><\/tr><tr><td>Stockage<\/td><td>Aucun<\/td><td>Aucun (ou mat\u00e9rialis\u00e9e)<\/td><td>Disque\/m\u00e9moire<\/td><\/tr><tr><td>R\u00e9utilisable entre requ\u00eates<\/td><td>Non<\/td><td>Oui<\/td><td>Oui<\/td><\/tr><tr><td>R\u00e9cursivit\u00e9<\/td><td>Oui<\/td><td>Non<\/td><td>Non<\/td><\/tr><tr><td>Lisibilit\u00e9<\/td><td>Excellente<\/td><td>Bonne<\/td><td>Moyenne<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading has-large-font-size\">Cas d&rsquo;usage o\u00f9 les CTEs sont indispensables<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Rapports analytiques complexes<\/strong> : d\u00e9composer les calculs en \u00e9tapes (agr\u00e9gation \u2192 filtrage \u2192 ranking).<\/li>\n\n\n\n<li><strong>Hierarchies et arbres<\/strong> : cat\u00e9gories, menus, commentaires imbriqu\u00e9s, organigrammes.<\/li>\n\n\n\n<li><strong>D\u00e9duplication<\/strong> : isoler les doublons dans une CTE avant de les traiter.<\/li>\n\n\n\n<li><strong>Pagination avanc\u00e9e<\/strong> : calculer les offsets ou les totaux dans une CTE, les utiliser dans la requ\u00eate principale.<\/li>\n\n\n\n<li><strong>Calculs inter-lignes<\/strong> : cumuls, moyennes glissantes : souvent combin\u00e9s avec les fonctions de fen\u00eatrage (<code>OVER<\/code>, <code>PARTITION BY<\/code>)<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading has-large-font-size\">Exemple complet : classement des vendeurs avec cumul<\/h2>\n\n\n\n<p>Pour finir, voici un exemple un peu plus complet qui combine une CTE avec des fonctions de fen\u00eatrage :<\/p>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-2d7bd756 alignfull uagb-is-root-container\"><div class=\"uagb-container-inner-blocks-wrap\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" 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;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 0 16px;font-size:0.8em;width:100%;text-align:left;background-color:#1E1E1E;font-style:italic;color:#D4D4D4\"><span style=\"border-bottom:1px solid rgba(234, 191, 191, 0.2)\">SQL<\/span><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;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 ventes_mensuelles AS (\n  SELECT\n    vendeur_id,\n    DATE_FORMAT(date_vente, '%Y-%m') AS mois,\n    SUM(montant) AS total_mois\n  FROM ventes\n  WHERE YEAR(date_vente) = 2026\n  GROUP BY vendeur_id, DATE_FORMAT(date_vente, '%Y-%m')\n),\nclassement AS (\n  SELECT\n      vendeur_id,\n      mois,\n      total_mois,\n      RANK() OVER (PARTITION BY mois ORDER BY total_mois DESC) AS rang,\n      SUM(total_mois) OVER (PARTITION BY vendeur_id ORDER BY mois) AS cumul\n    FROM ventes_mensuelles\n)\nSELECT e.nom, c.mois, c.total_mois, c.rang, c.cumul\nFROM classement c\nJOIN employes e ON e.id = c.vendeur_id\nWHERE c.rang &lt;= 3\nORDER BY c.mois, c.rang;<\/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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">WITH<\/span><span style=\"color: #D4D4D4\"> ventes_mensuelles <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    vendeur_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">DATE_FORMAT<\/span><span style=\"color: #D4D4D4\">(date_vente, <\/span><span style=\"color: #CE9178\">&#39;%Y-%m&#39;<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> mois,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(montant) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> total_mois<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> ventes<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">YEAR<\/span><span style=\"color: #D4D4D4\">(date_vente) = <\/span><span style=\"color: #B5CEA8\">2026<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">GROUP BY<\/span><span style=\"color: #D4D4D4\"> vendeur_id, <\/span><span style=\"color: #569CD6\">DATE_FORMAT<\/span><span style=\"color: #D4D4D4\">(date_vente, <\/span><span style=\"color: #CE9178\">&#39;%Y-%m&#39;<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">classement <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      vendeur_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      mois,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      total_mois,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #DCDCAA\">RANK<\/span><span style=\"color: #D4D4D4\">() <\/span><span style=\"color: #569CD6\">OVER<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">PARTITION<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">BY<\/span><span style=\"color: #D4D4D4\"> mois <\/span><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> total_mois <\/span><span style=\"color: #569CD6\">DESC<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> rang,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(total_mois) <\/span><span style=\"color: #569CD6\">OVER<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">PARTITION<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">BY<\/span><span style=\"color: #D4D4D4\"> vendeur_id <\/span><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> mois) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> cumul<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> ventes_mensuelles<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> e.nom, c.mois, c.total_mois, c.rang, c.cumul<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> classement c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">JOIN<\/span><span style=\"color: #D4D4D4\"> employes e <\/span><span style=\"color: #569CD6\">ON<\/span><span style=\"color: #D4D4D4\"> e.id = c.vendeur_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> c.rang &lt;= <\/span><span style=\"color: #B5CEA8\">3<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> c.mois, c.rang;<\/span><\/span><\/code><\/pre><\/div>\n<\/div><\/div>\n\n\n\n<p>Cette requ\u00eate retourne le top 3 des vendeurs chaque mois avec leur cumul annuel. Imagines faire \u00e7a avec des sous-requ\u00eates imbriqu\u00e9es : bonne chance pour la relire 3 semaines plus tard \ud83d\ude05.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-large-font-size\">Optimiser ses requ\u00eates SQL avec les CTEs<\/h2>\n\n\n\n<p>Les CTEs avec <code>WITH<\/code> sont l&rsquo;un de ces outils SQL qui, une fois adopt\u00e9s, deviennent indispensables. Elles rendent le code plus lisible, plus maintenable, et permettent d&rsquo;exprimer des logiques complexes de mani\u00e8re structur\u00e9e. C\u00f4t\u00e9 performances, elles ne sont pas magiques, il faut comprendre comment ton moteur les traite, mais elles s&rsquo;int\u00e8grent parfaitement dans une strat\u00e9gie d&rsquo;optimisation globale.<\/p>\n\n\n\n<p>Si tu veux aller plus loin et comprendre comment ces optimisations SQL s&rsquo;inscrivent dans une vision plus large de la performance applicative, je t&rsquo;invite \u00e0 lire mon article sur <a href=\"https:\/\/www.innospira.fr\/index.php\/2025\/07\/18\/comment-ameliorer-les-performances-de-ton-application-web\/\" target=\"_blank\" rel=\"noopener\">l&rsquo;am\u00e9lioration des performances de ton application web<\/a>.<\/p>\n\n\n\n<p>Aussi, tu peux consulter le guide suivant pour aller plus loin sur les CTE :<a href=\"https:\/\/sgbd.developpez.com\/actu\/101633\/Apprendre-a-utiliser-manipuler-les-CTE-Common-Table-Expression-un-tutoriel-de-Lyche\/\">Apprendre \u00e0 utiliser et manipuler les CTE (d\u00e9veloppez.com)<\/a>. <\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Optimiser ses requ\u00eates SQL, c&rsquo;est indispensable quand on travaille r\u00e9guli\u00e8rement sur un ensemble de tables ou base de donn\u00e9es. Tu [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1730,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"linkedin_posted":false,"linkedin_posted_date":"","footnotes":""},"categories":[25],"tags":[32],"class_list":["post-1721","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ia-developpement","tag-developpement"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.6 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Optimiser ses requ\u00eates SQL avec l&#039;instruction WITH<\/title>\n<meta name=\"description\" content=\"Comprendre et apprendre les CTE avec l&#039;instruction WITH pour optimiser ses requ\u00eates, faciliter la lecture. Avec des exemples concrets.\" \/>\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.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Optimiser ses requ\u00eates SQL avec l&#039;instruction WITH\" \/>\n<meta property=\"og:description\" content=\"Comprendre et apprendre les CTE avec l&#039;instruction WITH pour optimiser ses requ\u00eates, faciliter la lecture. Avec des exemples concrets.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/\" \/>\n<meta property=\"og:site_name\" content=\"InnoSpira\" \/>\n<meta property=\"article:published_time\" content=\"2026-04-16T09:39:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-04-16T09:39:56+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql.webp\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"720\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/webp\" \/>\n<meta name=\"author\" content=\"J\u00e9r\u00e9mie\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"J\u00e9r\u00e9mie\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"NewsArticle\",\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/\"},\"author\":{\"name\":\"J\u00e9r\u00e9mie\",\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/#\\\/schema\\\/person\\\/9df0acbd7573e147b4ffc81c2fa32662\"},\"headline\":\"Optimiser ses requ\u00eates SQL avec l&rsquo;instruction WITH\",\"datePublished\":\"2026-04-16T09:39:55+00:00\",\"dateModified\":\"2026-04-16T09:39:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/\"},\"wordCount\":1143,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.innospira.fr\\\/wp-content\\\/uploads\\\/2026\\\/04\\\/optimiser_ses_requetes_sql.webp\",\"keywords\":[\"d\u00e9veloppement\"],\"articleSection\":[\"IA et D\u00e9veloppement\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/\",\"url\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/\",\"name\":\"Optimiser ses requ\u00eates SQL avec l'instruction WITH\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.innospira.fr\\\/wp-content\\\/uploads\\\/2026\\\/04\\\/optimiser_ses_requetes_sql.webp\",\"datePublished\":\"2026-04-16T09:39:55+00:00\",\"dateModified\":\"2026-04-16T09:39:56+00:00\",\"description\":\"Comprendre et apprendre les CTE avec l'instruction WITH pour optimiser ses requ\u00eates, faciliter la lecture. Avec des exemples concrets.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.innospira.fr\\\/wp-content\\\/uploads\\\/2026\\\/04\\\/optimiser_ses_requetes_sql.webp\",\"contentUrl\":\"https:\\\/\\\/www.innospira.fr\\\/wp-content\\\/uploads\\\/2026\\\/04\\\/optimiser_ses_requetes_sql.webp\",\"width\":1280,\"height\":720,\"caption\":\"Optimiser ses requ\u00eates SQL avec la clause WITH (CTE)\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/2026\\\/04\\\/16\\\/optimiser-ses-requetes-sql-avec-linstruction-with\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.innospira.fr\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Optimiser ses requ\u00eates SQL avec l&rsquo;instruction WITH\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/#website\",\"url\":\"https:\\\/\\\/www.innospira.fr\\\/\",\"name\":\"InnoSpira\",\"description\":\"D\u00e9veloppez votre activit\u00e9, innovez !\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.innospira.fr\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/#organization\",\"name\":\"InnoSpira\",\"url\":\"https:\\\/\\\/www.innospira.fr\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.innospira.fr\\\/wp-content\\\/uploads\\\/2024\\\/10\\\/cropped-innospira_mini.png\",\"contentUrl\":\"https:\\\/\\\/www.innospira.fr\\\/wp-content\\\/uploads\\\/2024\\\/10\\\/cropped-innospira_mini.png\",\"width\":634,\"height\":211,\"caption\":\"InnoSpira\"},\"image\":{\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/youtube.com\\\/@innospira\",\"https:\\\/\\\/www.tiktok.com\\\/@innospira\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.innospira.fr\\\/#\\\/schema\\\/person\\\/9df0acbd7573e147b4ffc81c2fa32662\",\"name\":\"J\u00e9r\u00e9mie\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/79bb7d75560df4f10cfac00fb18946028e219cf4d7f9cd8e684a91135bf7c8cb?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/79bb7d75560df4f10cfac00fb18946028e219cf4d7f9cd8e684a91135bf7c8cb?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/79bb7d75560df4f10cfac00fb18946028e219cf4d7f9cd8e684a91135bf7c8cb?s=96&d=mm&r=g\",\"caption\":\"J\u00e9r\u00e9mie\"},\"sameAs\":[\"http:\\\/\\\/www.innospira.fr\"],\"url\":\"https:\\\/\\\/www.innospira.fr\\\/index.php\\\/author\\\/nazario\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Optimiser ses requ\u00eates SQL avec l'instruction WITH","description":"Comprendre et apprendre les CTE avec l'instruction WITH pour optimiser ses requ\u00eates, faciliter la lecture. Avec des exemples concrets.","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.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/","og_locale":"fr_FR","og_type":"article","og_title":"Optimiser ses requ\u00eates SQL avec l'instruction WITH","og_description":"Comprendre et apprendre les CTE avec l'instruction WITH pour optimiser ses requ\u00eates, faciliter la lecture. Avec des exemples concrets.","og_url":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/","og_site_name":"InnoSpira","article_published_time":"2026-04-16T09:39:55+00:00","article_modified_time":"2026-04-16T09:39:56+00:00","og_image":[{"width":1280,"height":720,"url":"https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql.webp","type":"image\/webp"}],"author":"J\u00e9r\u00e9mie","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"J\u00e9r\u00e9mie","Dur\u00e9e de lecture estim\u00e9e":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"NewsArticle","@id":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/#article","isPartOf":{"@id":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/"},"author":{"name":"J\u00e9r\u00e9mie","@id":"https:\/\/www.innospira.fr\/#\/schema\/person\/9df0acbd7573e147b4ffc81c2fa32662"},"headline":"Optimiser ses requ\u00eates SQL avec l&rsquo;instruction WITH","datePublished":"2026-04-16T09:39:55+00:00","dateModified":"2026-04-16T09:39:56+00:00","mainEntityOfPage":{"@id":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/"},"wordCount":1143,"commentCount":0,"publisher":{"@id":"https:\/\/www.innospira.fr\/#organization"},"image":{"@id":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/#primaryimage"},"thumbnailUrl":"https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql.webp","keywords":["d\u00e9veloppement"],"articleSection":["IA et D\u00e9veloppement"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/","url":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/","name":"Optimiser ses requ\u00eates SQL avec l'instruction WITH","isPartOf":{"@id":"https:\/\/www.innospira.fr\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/#primaryimage"},"image":{"@id":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/#primaryimage"},"thumbnailUrl":"https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql.webp","datePublished":"2026-04-16T09:39:55+00:00","dateModified":"2026-04-16T09:39:56+00:00","description":"Comprendre et apprendre les CTE avec l'instruction WITH pour optimiser ses requ\u00eates, faciliter la lecture. Avec des exemples concrets.","breadcrumb":{"@id":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/"]}]},{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/#primaryimage","url":"https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql.webp","contentUrl":"https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql.webp","width":1280,"height":720,"caption":"Optimiser ses requ\u00eates SQL avec la clause WITH (CTE)"},{"@type":"BreadcrumbList","@id":"https:\/\/www.innospira.fr\/index.php\/2026\/04\/16\/optimiser-ses-requetes-sql-avec-linstruction-with\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.innospira.fr\/"},{"@type":"ListItem","position":2,"name":"Optimiser ses requ\u00eates SQL avec l&rsquo;instruction WITH"}]},{"@type":"WebSite","@id":"https:\/\/www.innospira.fr\/#website","url":"https:\/\/www.innospira.fr\/","name":"InnoSpira","description":"D\u00e9veloppez votre activit\u00e9, innovez !","publisher":{"@id":"https:\/\/www.innospira.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.innospira.fr\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/www.innospira.fr\/#organization","name":"InnoSpira","url":"https:\/\/www.innospira.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/www.innospira.fr\/#\/schema\/logo\/image\/","url":"https:\/\/www.innospira.fr\/wp-content\/uploads\/2024\/10\/cropped-innospira_mini.png","contentUrl":"https:\/\/www.innospira.fr\/wp-content\/uploads\/2024\/10\/cropped-innospira_mini.png","width":634,"height":211,"caption":"InnoSpira"},"image":{"@id":"https:\/\/www.innospira.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/youtube.com\/@innospira","https:\/\/www.tiktok.com\/@innospira"]},{"@type":"Person","@id":"https:\/\/www.innospira.fr\/#\/schema\/person\/9df0acbd7573e147b4ffc81c2fa32662","name":"J\u00e9r\u00e9mie","image":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/secure.gravatar.com\/avatar\/79bb7d75560df4f10cfac00fb18946028e219cf4d7f9cd8e684a91135bf7c8cb?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/79bb7d75560df4f10cfac00fb18946028e219cf4d7f9cd8e684a91135bf7c8cb?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/79bb7d75560df4f10cfac00fb18946028e219cf4d7f9cd8e684a91135bf7c8cb?s=96&d=mm&r=g","caption":"J\u00e9r\u00e9mie"},"sameAs":["http:\/\/www.innospira.fr"],"url":"https:\/\/www.innospira.fr\/index.php\/author\/nazario\/"}]}},"uagb_featured_image_src":{"full":["https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql.webp",1280,720,false],"thumbnail":["https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql-150x150.webp",150,150,true],"medium":["https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql-300x169.webp",300,169,true],"medium_large":["https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql-768x432.webp",768,432,true],"large":["https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql-1024x576.webp",1024,576,true],"1536x1536":["https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql.webp",1280,720,false],"2048x2048":["https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql.webp",1280,720,false],"mailpoet_newsletter_max":["https:\/\/www.innospira.fr\/wp-content\/uploads\/2026\/04\/optimiser_ses_requetes_sql.webp",1280,720,false]},"uagb_author_info":{"display_name":"J\u00e9r\u00e9mie","author_link":"https:\/\/www.innospira.fr\/index.php\/author\/nazario\/"},"uagb_comment_info":0,"uagb_excerpt":"Optimiser ses requ\u00eates SQL, c&rsquo;est indispensable quand on travaille r\u00e9guli\u00e8rement sur un ensemble de tables ou base de donn\u00e9es. Tu [&hellip;]","_links":{"self":[{"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/posts\/1721","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/comments?post=1721"}],"version-history":[{"count":6,"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/posts\/1721\/revisions"}],"predecessor-version":[{"id":1731,"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/posts\/1721\/revisions\/1731"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/media\/1730"}],"wp:attachment":[{"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/media?parent=1721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/categories?post=1721"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.innospira.fr\/index.php\/wp-json\/wp\/v2\/tags?post=1721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}