{"id":6269,"date":"2015-12-07T20:43:35","date_gmt":"2015-12-07T19:43:35","guid":{"rendered":"http:\/\/www.salvis.com\/blog\/?p=6269"},"modified":"2023-11-07T22:31:51","modified_gmt":"2023-11-07T21:31:51","slug":"outer-join-operator-restrictions-in-12-1-0-2","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/","title":{"rendered":"Outer Join Operator (+) Restrictions in 12.1.0.2?"},"content":{"rendered":"\n<p>I&#8217;m currently reviewing a draft of Roger Troller&#8217;s updated\u00a0PL\/SQL and SQL Coding Guidelines version 3.0. One guideline recommends using ANSI join syntax. The mentioned reasons are<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>ANSI join syntax does not have as many restrictions as the ORACLE join syntax has. Furthermore ANSI join syntax supports the full outer join. A third advantage of the ANSI join syntax is the separation of the join condition from the query filters.<\/p>\n<\/blockquote>\n\n\n\n<p>While I read this I wondered which restrictions still exist for ORACLE join syntax nowadays and&nbsp;searched for &#8220;(+)&#8221; in the current Error Messages documentation (E49325-06) and found&nbsp;the following error messages:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>ORA-01417: a table may be outer joined to at most one other table<\/li>\n\n\n\n<li>ORA-01719: outer join operator (+) not allowed in operand of OR or IN<\/li>\n\n\n\n<li>ORA-01799: a column may not be outer-joined to a subquery<\/li>\n\n\n\n<li>ORA-25156: old style outer join (+) cannot be used with ANSI joins<\/li>\n\n\n\n<li>ORA-30563: outer join operator (+) is not allowed here<\/li>\n<\/ul>\n\n\n\n<p>In the 9.2 documentation (A96525-01) I&nbsp;found the following additional messages:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>ORA-01416: two tables cannot be outer-joined to each other<\/li>\n\n\n\n<li>ORA-01468: a predicate may reference only one outer-joined table<\/li>\n<\/ul>\n\n\n\n<p>I&#8217;ve&nbsp;written SQL statements to produce the&nbsp;error message listed above on a 9.2.0.8 Oracle database and ran them on a 12.1.0.2 database as well to&nbsp;see which restrictions still exist for the outer join operator (+) as basis for my feedback to Roger. While writing the queries I thought this might be an interesting topic to blog about.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Examples<\/h3>\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:#D4D4D4;--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:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Example 1: ORA-01416: two tables cannot be outer-joined to each other<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT s.*, p.*\n  FROM sh.sales s, sh.products p\n WHERE p.prod_id = s.prod_id(+)\n       AND p.supplier_id(+) = s.channel_id;\" style=\"color:#D4D4D4;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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> s.*, p.*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> sh.sales s, sh.products p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> p.prod_id = s.prod_id(+)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> p.supplier_id(+) = s.channel_id;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>An\u00a0ORA-01416 is thrown in 9.2.0.8\u00a0and\u00a012.1.0.2. You cannot formulate such a query using ANSI join. Doing something like that does not make sense. It is not a relevant restriction. But it is interesting to see that an ORA-01416 is thrown in Oracle 12.1.0.2, even if this error message is not documented any more.<\/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:#D4D4D4;--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:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Example 2: ORA-14017: a table may be outer joined to at most one other table<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT s.*, c.*, p.*\n  FROM sh.sales s, sh.customers c, sh.products p\n WHERE p.prod_id = s.prod_id(+)\n       AND c.cust_id = s.cust_id(+);\" style=\"color:#D4D4D4;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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> s.*, c.*, p.*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> sh.sales s, sh.customers c, sh.products p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> p.prod_id = s.prod_id(+)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> c.cust_id = s.cust_id(+);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>An&nbsp;ORA-01417 is thrown in 9.2.0.8 but not&nbsp;in 12.1.0.2.<\/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:#D4D4D4;--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:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Example 3: ORA-01468: a predicate may reference only one outer-joined table<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT s.*, p.*\n  FROM sh.sales s, sh.products p\n WHERE p.prod_id(+) = s.prod_id(+);\" style=\"color:#D4D4D4;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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> s.*, p.*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> sh.sales s, sh.products p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> p.prod_id(+) = s.prod_id(+);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>An\u00a0ORA-01468 is thrown in 9.2.0.8\u00a0and\u00a0in 12.1.0.2. You cannot formulate such a query using ANSI join. It could have been\u00a0a way to formulate a full outer join, but something like that is not supported with\u00a0Oracle join syntax. ORA-01468 is not documented in Oracle 12.1.0.2, but this error is thrown. I do not consider this a relevant restriction for Oracle join syntax.<\/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:#D4D4D4;--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:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Example 4: ORA-01719: outer join operator (+) not allowed in operand of OR or IN<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT s.*, p.*\n  FROM sh.sales s, sh.products p\n WHERE p.prod_id(+) = s.prod_id\n   AND p.prod_category(+) IN ('Boys', 'Girls');\" style=\"color:#D4D4D4;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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> s.*, p.*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> sh.sales s, sh.products p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> p.prod_id(+) = s.prod_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> p.prod_category(+) <\/span><span style=\"color: #569CD6\">IN<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #CE9178\">&#39;Boys&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;Girls&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>An&nbsp;ORA-01719 is thrown in 9.2.0.8 but not&nbsp;in 12.1.0.2.<\/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:#D4D4D4;--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:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Example 5a: ORA-01799: a column may not be outer-joined to a subquery<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT s.*\n  FROM sh.sales s\n WHERE s.time_id(+) = (SELECT MAX(t.time_id)\n                         FROM sh.times t);\" style=\"color:#D4D4D4;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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> s.*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> sh.sales s<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> s.time_id(+) = (<\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">MAX<\/span><span style=\"color: #D4D4D4\">(t.time_id)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                         <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> sh.times t);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>An\u00a0ORA-01799 is thrown in 9.2.0.8 and in 12.1.0.2. You cannot formulate such a query using ANSI join. Of course, you may rewrite this to a valid Oracle join or ANSI join query. Here&#8217;s an 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:#D4D4D4;--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:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Example 5b: Fix for ORA-01799: a column may not be outer-joined to a subquery<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT s.*, t.max_time_id\n  FROM sh.sales s,\n       (SELECT MAX(t.time_id) AS max_time_id\n          FROM sh.times t) t\n WHERE s.time_id(+) = t.max_time_id;\" style=\"color:#D4D4D4;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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> s.*, t.max_time_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> sh.sales s,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       (<\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">MAX<\/span><span style=\"color: #D4D4D4\">(t.time_id) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> max_time_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> sh.times t) t<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> s.time_id(+) = t.max_time_id;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Because the restriction applies to ANSI join as well, I do not consider this a relevant restriction for Oracle join syntax.<\/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:#D4D4D4;--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:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Example 6: ORA-25156: old style outer join (+) cannot be used with ANSI joins<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT s.*, c.*, p.*\n  FROM sh.sales s, sh.customers c\n  JOIN sh.products p\n    ON (p.prod_id = s.prod_id)\n WHERE c.cust_id = s.cust_id(+);\" style=\"color:#D4D4D4;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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> s.*, c.*, p.*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> sh.sales s, sh.customers c<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">JOIN<\/span><span style=\"color: #D4D4D4\"> sh.products p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">ON<\/span><span style=\"color: #D4D4D4\"> (p.prod_id = s.prod_id)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> c.cust_id = s.cust_id(+);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>An\u00a0ORA-25156\u00a0is thrown in 9.2.0.8 and 12.1.0.2. This is not a restriction for Oracle join syntax. The grammar simply does not support mixing join syntax variants.<\/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:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);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 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Example 7: ORA-30563: outer join operator (+) is not allowed here<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT lpad(' ', (LEVEL - 1) * 3) || to_char(e.empno) || ' ' || \n       e.ename(+) || \n       ' ' || d.dname AS emp_name\n  FROM scott.emp e, scott.dept d\n WHERE e.deptno(+) = d.deptno\nCONNECT BY PRIOR e.empno(+) = e.mgr\n START WITH e.ename(+) = 'KING'\n ORDER BY rownum, e.empno(+);\" style=\"color:#D4D4D4;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 dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> lpad(<\/span><span style=\"color: #CE9178\">&#39; &#39;<\/span><span style=\"color: #D4D4D4\">, (<\/span><span style=\"color: #569CD6\">LEVEL<\/span><span style=\"color: #D4D4D4\"> - <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">) * <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">) || to_char(e.empno) || <\/span><span style=\"color: #CE9178\">&#39; &#39;<\/span><span style=\"color: #D4D4D4\"> || <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">       e.ename(+) || <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #CE9178\">&#39; &#39;<\/span><span style=\"color: #D4D4D4\"> || d.dname <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> emp_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> scott.emp e, scott.dept d<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> e.deptno(+) = d.deptno<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">CONNECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">BY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">PRIOR<\/span><span style=\"color: #D4D4D4\"> e.empno(+) = e.mgr<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">START<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WITH<\/span><span style=\"color: #D4D4D4\"> e.ename(+) = <\/span><span style=\"color: #CE9178\">&#39;KING&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> rownum, e.empno(+);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>An&nbsp;ORA-30563 is thrown in 9.2.0.8 and&nbsp;12.1.0.2. Interesting is that if you remove the (+) on the highlighted line 2 the query works on 9.2.0.8 but not on 12.1.0.2. Using the (+) in a&nbsp;CONNECT BY clause, START WITH clause, or ORDER BY clause does not make sense. It is not possible using ANSI-join as well. The important part is the join itself on line 5 and this is working in conjunction with a CONNECT BY. Therefore&nbsp;I do consider this an irrelevant restriction for the Oracle join syntax.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Summary<\/h3>\n\n\n\n<p>The results of the example relevant statements\u00a0are summarized in the following table.<br \/>\n<table id=\"tablepress-6\" class=\"tablepress tablepress-id-6\" aria-labelledby=\"tablepress-6-name\">\n<thead>\n<tr class=\"row-1\">\n\t<th class=\"column-1\">Error message by test SQL<\/th><th class=\"column-2\">Relevant outer join restriction?<\/th><th class=\"column-3\">Result in 9.2.0.8<\/th><th class=\"column-4\">Result in 12.1.0.2<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"row-striping row-hover\">\n<tr class=\"row-2\">\n\t<td class=\"column-1\">ORA-01416 two tables cannot be outer-joined to each other<\/td><td class=\"column-2\">No<\/td><td class=\"column-3\">Error<\/td><td class=\"column-4\">Error<\/td>\n<\/tr>\n<tr class=\"row-3\">\n\t<td class=\"column-1\">ORA-01417: a table may be outer joined to at most one other table<\/td><td class=\"column-2\">Yes<\/td><td class=\"column-3\">Error<\/td><td class=\"column-4\">OK<\/td>\n<\/tr>\n<tr class=\"row-4\">\n\t<td class=\"column-1\">ORA-01468 a predicate may reference only one outer-joined table<\/td><td class=\"column-2\">No<\/td><td class=\"column-3\">Error<\/td><td class=\"column-4\">Error<\/td>\n<\/tr>\n<tr class=\"row-5\">\n\t<td class=\"column-1\">ORA-01719: outer join operator (+) not allowed in operand of OR or IN<\/td><td class=\"column-2\">Yes<\/td><td class=\"column-3\">Error<\/td><td class=\"column-4\">OK<\/td>\n<\/tr>\n<tr class=\"row-6\">\n\t<td class=\"column-1\">ORA-01799: a column may not be outer-joined to a subquery<\/td><td class=\"column-2\">No<\/td><td class=\"column-3\">Error<\/td><td class=\"column-4\">Error<\/td>\n<\/tr>\n<tr class=\"row-7\">\n\t<td class=\"column-1\">ORA-25156: old style outer join (+) cannot be used with ANSI joins<\/td><td class=\"column-2\">No<\/td><td class=\"column-3\">Error<\/td><td class=\"column-4\">Error<\/td>\n<\/tr>\n<tr class=\"row-8\">\n\t<td class=\"column-1\">ORA-30563: outer join operator (+) is not allowed here<\/td><td class=\"column-2\">No<\/td><td class=\"column-3\">Error<\/td><td class=\"column-4\">Error<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2 id=\"tablepress-6-name\" class=\"tablepress-table-name tablepress-table-name-id-6\">Table 1: Outer join operator (+) restrictions in 9.2.0.8 and 12.1.0.2<\/h2>\n<br \/>In the most current Oracle version, no relevant limitations exist regarding the Oracle join syntax. Hence not choosing ANSI join syntax just because in the past some limitations existed is doing the right for the wrong reasons&#8230; I favour the ANSI join syntax because filter and join conditions are separated. For full outer joins, there is simply no better performance option than to use ANSI join syntax. See also also Chris Antognini&#8217;s post about <a href=\"https:\/\/antognini.ch\/2010\/05\/native-full-outer-join-officially-available-in-10-2-0-5\/\">native full outer join<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m currently reviewing a draft of Roger Troller&#8217;s updated\u00a0PL\/SQL and SQL Coding Guidelines version 3.0. One guideline recommends using ANSI join syntax. The mentioned reasons are ANSI join syntax does not have as many restrictions as the ORACLE join syntax has. Furthermore ANSI join syntax supports the full outer join. A third<span class=\"excerpt-hellip\"> [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":9532,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[85],"class_list":["post-6269","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Outer Join Operator (+) Restrictions in 12.1.0.2? - Philipp Salvisberg&#039;s Blog<\/title>\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.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Outer Join Operator (+) Restrictions in 12.1.0.2? - Philipp Salvisberg&#039;s Blog\" \/>\n<meta property=\"og:description\" content=\"I&#8217;m currently reviewing a draft of Roger Troller&#8217;s updated\u00a0PL\/SQL and SQL Coding Guidelines version 3.0. One guideline recommends using ANSI join syntax. The mentioned reasons are ANSI join syntax does not have as many restrictions as the ORACLE join syntax has. Furthermore ANSI join syntax supports the full outer join. A third [\u2026]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg&#039;s Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-07T19:43:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-07T21:31:51+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2015\/12\/outer_join.png\" \/>\n\t<meta property=\"og:image:width\" content=\"683\" \/>\n\t<meta property=\"og:image:height\" content=\"454\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"Outer Join Operator (+) Restrictions in 12.1.0.2?\",\"datePublished\":\"2015-12-07T19:43:35+00:00\",\"dateModified\":\"2023-11-07T21:31:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/\"},\"wordCount\":746,\"commentCount\":6,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/12\\\/outer_join.png\",\"keywords\":[\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/\",\"name\":\"Outer Join Operator (+) Restrictions in 12.1.0.2? - Philipp Salvisberg&#039;s Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/12\\\/outer_join.png\",\"datePublished\":\"2015-12-07T19:43:35+00:00\",\"dateModified\":\"2023-11-07T21:31:51+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/12\\\/outer_join.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/12\\\/outer_join.png\",\"width\":683,\"height\":454},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2015\\\/12\\\/07\\\/outer-join-operator-restrictions-in-12-1-0-2\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Outer Join Operator (+) Restrictions in 12.1.0.2?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg&#039;s Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Outer Join Operator (+) Restrictions in 12.1.0.2? - Philipp Salvisberg&#039;s Blog","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.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/","og_locale":"en_US","og_type":"article","og_title":"Outer Join Operator (+) Restrictions in 12.1.0.2? - Philipp Salvisberg&#039;s Blog","og_description":"I&#8217;m currently reviewing a draft of Roger Troller&#8217;s updated\u00a0PL\/SQL and SQL Coding Guidelines version 3.0. One guideline recommends using ANSI join syntax. The mentioned reasons are ANSI join syntax does not have as many restrictions as the ORACLE join syntax has. Furthermore ANSI join syntax supports the full outer join. A third [\u2026]","og_url":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/","og_site_name":"Philipp Salvisberg&#039;s Blog","article_published_time":"2015-12-07T19:43:35+00:00","article_modified_time":"2023-11-07T21:31:51+00:00","og_image":[{"width":683,"height":454,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2015\/12\/outer_join.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"Outer Join Operator (+) Restrictions in 12.1.0.2?","datePublished":"2015-12-07T19:43:35+00:00","dateModified":"2023-11-07T21:31:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/"},"wordCount":746,"commentCount":6,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2015\/12\/outer_join.png","keywords":["SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/","url":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/","name":"Outer Join Operator (+) Restrictions in 12.1.0.2? - Philipp Salvisberg&#039;s Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2015\/12\/outer_join.png","datePublished":"2015-12-07T19:43:35+00:00","dateModified":"2023-11-07T21:31:51+00:00","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2015\/12\/outer_join.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2015\/12\/outer_join.png","width":683,"height":454},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2015\/12\/07\/outer-join-operator-restrictions-in-12-1-0-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Outer Join Operator (+) Restrictions in 12.1.0.2?"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg&#039;s Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/6269","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=6269"}],"version-history":[{"count":40,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/6269\/revisions"}],"predecessor-version":[{"id":12590,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/6269\/revisions\/12590"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/9532"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=6269"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=6269"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=6269"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}