{"id":4160,"date":"2024-09-26T09:07:36","date_gmt":"2024-09-26T01:07:36","guid":{"rendered":"https:\/\/five.co\/?p=4160"},"modified":"2024-09-26T09:09:00","modified_gmt":"2024-09-26T01:09:00","slug":"sql-conditional-joins","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-conditional-joins\/","title":{"rendered":"SQL Conditional Joins: Practical Guide"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Breakdown of SQL Conditional Joins<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SQL conditional joins are a great tool for handling complex data relationships. They allow you to combine data from multiple tables based on specific conditions, going beyond simple key-based joins. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s dive into the world of conditional joins and explore how they can enhance your SQL queries.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-2wt3n wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-2wt3n \"><div class=\"eb-toc-container eb-toc-2wt3n  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Breakdown of SQL Conditional Joins&quot;,&quot;text&quot;:&quot;Breakdown of SQL Conditional Joins&quot;,&quot;link&quot;:&quot;breakdown-of-sql-conditional-joins&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Understanding Conditional Joins&quot;,&quot;text&quot;:&quot;Understanding Conditional Joins&quot;,&quot;link&quot;:&quot;understanding-conditional-joins&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Types of Conditional Joins&quot;,&quot;text&quot;:&quot;Types of Conditional Joins&quot;,&quot;link&quot;:&quot;types-of-conditional-joins&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Range Joins&quot;,&quot;text&quot;:&quot;Range Joins&quot;,&quot;link&quot;:&quot;range-joins&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Multi-Column Joins&quot;,&quot;text&quot;:&quot;Multi-Column Joins&quot;,&quot;link&quot;:&quot;multi-column-joins&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Subquery in Join Condition&quot;,&quot;text&quot;:&quot;Subquery in Join Condition&quot;,&quot;link&quot;:&quot;subquery-in-join-condition&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-World Use Case: Sales Performance Analysis&quot;,&quot;text&quot;:&quot;Real-World Use Case: Sales Performance Analysis&quot;,&quot;link&quot;:&quot;real-world-use-case-sales-performance-analysis&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Optimizing Conditional Joins&quot;,&quot;text&quot;:&quot;Optimizing Conditional Joins&quot;,&quot;link&quot;:&quot;optimizing-conditional-joins&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Use Indexes Wisely&quot;,&quot;text&quot;:&quot;Use Indexes Wisely&quot;,&quot;link&quot;:&quot;use-indexes-wisely&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Avoid Functions in Join Conditions&quot;,&quot;text&quot;:&quot;Avoid Functions in Join Conditions&quot;,&quot;link&quot;:&quot;avoid-functions-in-join-conditions&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Consider Denormalization for Complex Conditions&quot;,&quot;text&quot;:&quot;Consider Denormalization for Complex Conditions&quot;,&quot;link&quot;:&quot;consider-denormalization-for-complex-conditions&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Handling NULL Values in Conditional Joins&quot;,&quot;text&quot;:&quot;Handling NULL Values in Conditional Joins&quot;,&quot;link&quot;:&quot;handling-null-values-in-conditional-joins&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Combining Conditional Joins with Window Functions&quot;,&quot;text&quot;:&quot;Combining Conditional Joins with Window Functions&quot;,&quot;link&quot;:&quot;combining-conditional-joins-with-window-functions&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Conclusion&quot;,&quot;text&quot;:&quot;Conclusion&quot;,&quot;link&quot;:&quot;conclusion&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Breakdown of SQL Conditional Joins&quot;,&quot;value&quot;:&quot;breakdown-of-sql-conditional-joins&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Understanding Conditional Joins&quot;,&quot;value&quot;:&quot;understanding-conditional-joins&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Types of Conditional Joins&quot;,&quot;value&quot;:&quot;types-of-conditional-joins&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Range Joins&quot;,&quot;value&quot;:&quot;range-joins&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Multi-Column Joins&quot;,&quot;value&quot;:&quot;multi-column-joins&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Subquery in Join Condition&quot;,&quot;value&quot;:&quot;subquery-in-join-condition&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Real-World Use Case: Sales Performance Analysis&quot;,&quot;value&quot;:&quot;real-world-use-case-sales-performance-analysis&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Optimizing Conditional Joins&quot;,&quot;value&quot;:&quot;optimizing-conditional-joins&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Use Indexes Wisely&quot;,&quot;value&quot;:&quot;use-indexes-wisely&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Avoid Functions in Join Conditions&quot;,&quot;value&quot;:&quot;avoid-functions-in-join-conditions&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Consider Denormalization for Complex Conditions&quot;,&quot;value&quot;:&quot;consider-denormalization-for-complex-conditions&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Handling NULL Values in Conditional Joins&quot;,&quot;value&quot;:&quot;handling-null-values-in-conditional-joins&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Combining Conditional Joins with Window Functions&quot;,&quot;value&quot;:&quot;combining-conditional-joins-with-window-functions&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Conclusion&quot;,&quot;value&quot;:&quot;conclusion&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#understanding-conditional-joins\">Understanding Conditional Joins<\/a><li><a href=\"#types-of-conditional-joins\">Types of Conditional Joins<\/a><ul class=\"eb-toc__list\"><li><a href=\"#range-joins\">Range Joins<\/a><li><a href=\"#multi-column-joins\">Multi-Column Joins<\/a><li><a href=\"#subquery-in-join-condition\">Subquery in Join Condition<\/a><\/li><\/ul><li><a href=\"#real-world-use-case-sales-performance-analysis\">Real-World Use Case: Sales Performance Analysis<\/a><li><a href=\"#optimizing-conditional-joins\">Optimizing Conditional Joins<\/a><li><a href=\"#handling-null-values-in-conditional-joins\">Handling NULL Values in Conditional Joins<\/a><li><a href=\"#combining-conditional-joins-with-window-functions\">Combining Conditional Joins with Window Functions<\/a><li><a href=\"#conclusion\">Conclusion<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Understanding Conditional Joins<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">At its core, a conditional join is a join operation where the join condition includes logic beyond simple equality between columns. This can include comparisons, date ranges, or even subqueries. Here&#8217;s a basic example:<\/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\" data-code=\"SELECT o.order_id, o.order_date, c.customer_name\nFROM orders o\nJOIN customers c ON o.customer_id = c.customer_id\n  AND o.order_date &gt; c.last_purchase_date;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><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\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> orders o<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> customers c <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/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\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">last_purchase_date<\/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\">In this query, we&#8217;re joining the <code>orders<\/code> and <code>customers<\/code> tables not just on matching <code>customer_id<\/code>, but also with the condition that the order date is later than the customer&#8217;s last purchase date.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Types of Conditional Joins<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Range Joins<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Range joins are useful when dealing with time-based or numerical range data. For example, finding employees who were active during a specific project:<\/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\" data-code=\"SELECT e.employee_name, p.project_name\nFROM employees e\nJOIN projects p ON e.hire_date <= p.end_date\n  AND (e.termination_date IS NULL OR e.termination_date &gt;= p.start_date);\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><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\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">employee_name<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">project_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees e<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> projects p <\/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\">hire_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">end_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">termination_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">IS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">OR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">termination_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">start_date<\/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 matches employees to projects where their employment period overlaps with the project duration.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Multi-Column Joins<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes, you need to join tables based on multiple columns with different conditions:<\/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\" data-code=\"SELECT p.product_name, i.inventory_count, s.store_name\nFROM products p\nJOIN inventory i ON p.product_id = i.product_id\nJOIN stores s ON i.store_id = s.store_id\n  AND (\n    (p.product_type = 'Perishable' AND s.has_refrigeration = 1)\n    OR p.product_type != 'Perishable'\n  );\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><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\">SELECT<\/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\">i<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">inventory_count<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">store_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> products p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> inventory i <\/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\">product_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">i<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">product_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> stores s <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">i<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">store_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">store_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    (<\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">product_type<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Perishable&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">has_refrigeration<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">OR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">product_type<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">!=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Perishable&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  );<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query joins product and inventory data, but only includes stores that have refrigeration for perishable products.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Subquery in Join Condition<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You can use subqueries in your join conditions for more complex logic:<\/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\" data-code=\"SELECT c.customer_name, o.order_id\nFROM customers c\nLEFT JOIN orders o ON o.customer_id = c.customer_id\n  AND o.order_date = (\n    SELECT MAX(order_date)\n    FROM orders\n    WHERE customer_id = c.customer_id\n  );\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><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\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_name<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> customers c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">LEFT JOIN<\/span><span style=\"color: #ABB2BF\"> orders o <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/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\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/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 style=\"color: #56B6C2\">MAX<\/span><span style=\"color: #ABB2BF\">(order_date)<\/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\"> customer_id <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">customer_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  );<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query retrieves each customer&#8217;s most recent order (if any) by using a correlated subquery in the join condition.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Use Case: Sales Performance Analysis<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s look at a more complex scenario where conditional joins can be particularly useful. Imagine you&#8217;re analyzing sales performance and need to compare each sale against the average for its product category during the same month.<\/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\" data-code=\"WITH monthly_averages AS (\n  SELECT \n    p.category_id,\n    DATE_TRUNC('month', s.sale_date) AS sale_month,\n    AVG(s.sale_amount) AS avg_sale_amount\n  FROM sales s\n  JOIN products p ON s.product_id = p.product_id\n  GROUP BY p.category_id, DATE_TRUNC('month', s.sale_date)\n)\nSELECT \n  s.sale_id,\n  s.sale_date,\n  s.sale_amount,\n  p.product_name,\n  p.category_id,\n  ma.avg_sale_amount,\n  (s.sale_amount - ma.avg_sale_amount) AS difference_from_average\nFROM sales s\nJOIN products p ON s.product_id = p.product_id\nJOIN monthly_averages ma \n  ON p.category_id = ma.category_id\n  AND DATE_TRUNC('month', s.sale_date) = ma.sale_month\nWHERE s.sale_amount &gt; ma.avg_sale_amount * 1.5;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><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\"> monthly_averages <\/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\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">category_id<\/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\">, <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_date<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> sale_month,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_amount<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> avg_sale_amount<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales s<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><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\">s<\/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\">product_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">category_id<\/span><span style=\"color: #ABB2BF\">, DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_date<\/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>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_id<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_date<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_amount<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><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>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">category_id<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #D19A66\">ma<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">avg_sale_amount<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_amount<\/span><span style=\"color: #ABB2BF\"> - <\/span><span style=\"color: #D19A66\">ma<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">avg_sale_amount<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> difference_from_average<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales s<\/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\">s<\/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\">product_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> monthly_averages ma <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/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\">category_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ma<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">category_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_date<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ma<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_month<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">ma<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">avg_sale_amount<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">5<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query does several things:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>It calculates the monthly average sale amount for each product category.<\/li>\n\n\n\n<li>It joins this information with individual sales.<\/li>\n\n\n\n<li>The conditional join ensures that we&#8217;re comparing each sale to the average for its specific category and month.<\/li>\n\n\n\n<li>Finally, it filters to show only sales that are more than 50% above the average.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Optimizing Conditional Joins<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While conditional joins are powerful, they can also be computationally expensive. Here are some tips to optimize their performance:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Use Indexes Wisely<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Ensure that columns used in join conditions are properly indexed. This is especially important for range conditions:<\/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\" data-code=\"CREATE INDEX idx_employee_dates ON employees (hire_date, termination_date);\nCREATE INDEX idx_project_dates ON projects (start_date, end_date);\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><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\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INDEX<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">idx_employee_dates<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> employees (hire_date, termination_date);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INDEX<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">idx_project_dates<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> projects (<\/span><span style=\"color: #C678DD\">start_date<\/span><span style=\"color: #ABB2BF\">, end_date);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Avoid Functions in Join Conditions<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">When possible, avoid using functions in join conditions as they can prevent the use of indexes:<\/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\" data-code=\"-- Less efficient\nJOIN orders o ON YEAR(c.signup_date) = YEAR(o.order_date)\n\n-- More efficient\nJOIN orders o ON c.signup_date &gt;= DATE_TRUNC('year', o.order_date)\n  AND c.signup_date < DATE_TRUNC('year', o.order_date) + INTERVAL '1 year'\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><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: #7F848E; font-style: italic\">-- Less efficient<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> orders o <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">YEAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">signup_date<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">YEAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- More efficient<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> orders o <\/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\">signup_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;year&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">c<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">signup_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;year&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/span><span style=\"color: #ABB2BF\">) + INTERVAL <\/span><span style=\"color: #98C379\">&#39;1 year&#39;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Consider Denormalization for Complex Conditions<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">If you find yourself frequently using complex conditional joins, consider denormalizing your data to simplify queries:<\/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\" data-code=\"CREATE TABLE employee_projects AS\nSELECT e.employee_id, p.project_id\nFROM employees e\nCROSS JOIN projects p\nWHERE e.hire_date <= p.end_date\n  AND (e.termination_date IS NULL OR e.termination_date &gt;= p.start_date);\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><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\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">employee_projects<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><\/span>\n<span class=\"line\"><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\">employee_id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">project_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> employees e<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CROSS JOIN<\/span><span style=\"color: #ABB2BF\"> projects p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">hire_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">end_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">termination_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">IS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">OR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">termination_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">start_date<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This table can then be used for simpler, faster joins in subsequent queries.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Handling NULL Values in Conditional Joins<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">NULL values require special attention in conditional joins. Consider this scenario where we want to match orders with their shipping details, including orders that haven&#8217;t been shipped:<\/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\" data-code=\"SELECT o.order_id, o.order_date, s.ship_date, s.tracking_number\nFROM orders o\nLEFT JOIN shipments s ON o.order_id = s.order_id\n  AND (s.ship_date IS NULL OR s.ship_date &gt;= o.order_date)\nORDER BY o.order_date DESC;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><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\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_id<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ship_date<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">tracking_number<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> orders o<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">LEFT JOIN<\/span><span style=\"color: #ABB2BF\"> shipments s <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ship_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">IS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">OR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ship_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">o<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">order_date<\/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:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query ensures that we include all orders, even those without shipments, while also validating that the ship date (if it exists) is not before the order date.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Combining Conditional Joins with Window Functions<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Conditional joins can be particularly powerful when combined with window functions. Here&#8217;s an example that ranks sales representatives&#8217; performance within their region, but only for products in their specialty:<\/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\" data-code=\"SELECT \n  sr.rep_name,\n  sr.region,\n  p.product_name,\n  s.sale_amount,\n  RANK() OVER (\n    PARTITION BY sr.region\n    ORDER BY s.sale_amount DESC\n  ) as region_rank\nFROM sales_reps sr\nJOIN sales s ON sr.rep_id = s.rep_id\nJOIN products p ON s.product_id = p.product_id\n  AND p.category = sr.specialty\nWHERE s.sale_date &gt;= CURRENT_DATE - INTERVAL '1 month'\nORDER BY sr.region, region_rank;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><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\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #D19A66\">sr<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">rep_name<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #D19A66\">sr<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">region<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><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>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_amount<\/span><span style=\"color: #ABB2BF\">,<\/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>\n<span class=\"line\"><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: #D19A66\">sr<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">region<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_amount<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DESC<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  ) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> region_rank<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales_reps sr<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> sales s <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">sr<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">rep_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">rep_id<\/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\">s<\/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\">product_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">category<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">sr<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">specialty<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">sale_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE - INTERVAL <\/span><span style=\"color: #98C379\">&#39;1 month&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">sr<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">region<\/span><span style=\"color: #ABB2BF\">, region_rank;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:19px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query uses a conditional join to match sales reps with their specialty products, then applies a ranking within each region.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Conditional joins in SQL opens up possibilities for complex data analysis. They allow you to express business logic directly in your database queries, often reducing the need for multiple queries or application-level processing.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Key takeaways:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use conditional joins to express complex relationships between tables.<\/li>\n\n\n\n<li>Combine multiple conditions for more sophisticated data matching.<\/li>\n\n\n\n<li>Be mindful of performance implications and optimize with proper indexing.<\/li>\n\n\n\n<li>Consider the impact of NULL values in your join conditions.<\/li>\n\n\n\n<li>Combine conditional joins with other SQL features like window functions for analysis.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Breakdown of SQL Conditional Joins SQL conditional joins are a great tool for handling complex data relationships. They allow you to combine data from multiple tables based on specific conditions, going beyond simple key-based joins. Let&#8217;s dive into the world of conditional joins and explore how they can enhance your SQL queries. Understanding Conditional Joins [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4161,"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-4160","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-SQL-Conditional-Joins-Practical-Guide.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Conditional-Joins-Practical-Guide-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Conditional-Joins-Practical-Guide-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Conditional-Joins-Practical-Guide-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Conditional-Joins-Practical-Guide-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Conditional-Joins-Practical-Guide.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Conditional-Joins-Practical-Guide.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":"Breakdown of SQL Conditional Joins SQL conditional joins are a great tool for handling complex data relationships. They allow you to combine data from multiple tables based on specific conditions, going beyond simple key-based joins. Let&#8217;s dive into the world of conditional joins and explore how they can enhance your SQL queries. Understanding Conditional Joins&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4160","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=4160"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4160\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4161"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}