Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 4 Joins in Oracle
  5. 4.10 SQL Joins Deep Dive – Part2

4.10 SQL Joins Deep Dive – Part2

Join in the most salient feature of any SQL database. To become a master in SQL, you must understand joins and their actions. Validate your SQL join knowledge by experimenting following code examples.

CREATE TABLE T1
(
  a1 INTEGER,
  a2 VARCHAR2(10)
);

CREATE TABLE T2
(
  b1 INTEGER,
  b2 VARCHAR2(10)
);

CREATE TABLE T3
(
  c1 INTEGER,
  c2 VARCHAR2(10)
);

CREATE TABLE T4
(
  d1 INTEGER,
  d2 VARCHAR2(10)
);

Let’s insert data to above tables.

INSERT INTO T1 VALUES(10, 'T1-1');
INSERT INTO T1 VALUES(20, 'T1-2');
INSERT INTO T1 VALUES(30, 'T1-3');
INSERT INTO T1 VALUES(40, 'T1-4');

INSERT INTO T2 VALUES(10, 'T2-1');
INSERT INTO T2 VALUES(22, 'T2-2');
INSERT INTO T2 VALUES(30, 'T2-3');

INSERT INTO T3 VALUES(10, 'T3-1');
INSERT INTO T3 VALUES(20, 'T3-2');
INSERT INTO T3 VALUES(33, 'T3-3');

INSERT INTO T4 VALUES(10, 'T4-1');
INSERT INTO T4 VALUES(40, 'T4-2');
INSERT INTO T4 VALUES(44, 'T4-3');
INSERT INTO T4 VALUES(444, 'T4-4');

Try out following examples and validate your SQL join understanding.

Analyze Inner Joins in SQL

Example 1: Inner join – T1 join T2

SELECT t1.*, t2.*
  FROM t1, t2 
 WHERE a1 = b1;

Example 2: Inner join – T2 join T3

SELECT t2.*, t3.* 
  FROM t2, t3 
 WHERE a1 = c1;

Example 3: Inner join – T1 join T4

SELECT t1.*, t4.* 
  FROM t1, t4
 WHERE a1 = d1;

Example 4: Multi table equi join – T1 join T2 join T3

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3
 WHERE a1 = b1
   AND a1 = c1;

Example 5: Multi table equi join – T1 join T3 join T4

SELECT t1.*, t3.*, t4.* 
  FROM t1, t3, t4
 WHERE a1 = c1
   AND a1 = d1;

Example 6: Multi table equi join – T4 join T1 join T3

SELECT t1.*, t3.*, t4.* 
  FROM t4, t1, t3
 WHERE d1 = a1
   AND a1 = c1;

Results (Number of rows) does not change in equi-join when you join with the same columns even if the order of tables in FROM clause is not same. So, output from Example 5 and Example 6 should be same.

Example 7: Multi table equi join – T4 join T1 join T3

SELECT t1.*, t3.*, t4.* 
  FROM t4, t1, t3, t2
 WHERE d1 = a1
   AND a1 = c1
   AND c1 = b1;

You could try all other combination of inner joins and you must if you are not confident at this point. If you are positively confident, experiment with other examples in this chapter.

Now try with other types of joins available in Oracle SQL.

Example 7: T1 Left join T3

SELECT t1.*, t2.* 
  FROM t1, t2 
 WHERE a1 = b1(+);

Example 8: T4 Left join T1

SELECT t4.*, t1.* 
  FROM t4, t1 
 WHERE d1 = a1(+);

Example 9: Right join

SELECT t1.*, t2.* 
  FROM t1, t2 
 WHERE a1(+) = b1;

Example 10: Attempt to Full join using (+) operator, Oracle produces error “ORA-01416: two tables cannot be outer-joined to each other” ; Use FULL OUTER JOIN keyword instead.

SELECT t1.*, t2.* 
  FROM t1, t2 
 WHERE a1 (+)= b1
   AND a1 = b1(+);

Example 11: T1 Full join T2

SELECT t1.*, t2.* 
  FROM t1 
       FULL JOIN t2 ON a1 = b1;

Example 12: T4 Full join T2

SELECT t4.*, t2.* 
  FROM t4 
       FULL JOIN t2 ON d1 = b1;

Example 13: T2 Full join T4

SELECT t4.*, t2.* 
  FROM t2 
       FULL JOIN t4 ON b1 = d1;

Comparing Example 12 and Example 13: Query results for both the examples must be same.

  • Both examples have same projection (here “t4.*, t2.*”) in the SELECT clause (both project same selection criteria – number of columns and column order are same).
  • Join condition in both the examples are same.

Moving towards complex join conditions

Joining 2 tables is pretty straight forward. However, certainly confusion arises when you join multiple tables having all types of joins. Continue to explore some of such scenarios below.

Example 14: Multi table hybrid join – Left Join on T1

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3
 WHERE a1 = b1
   AND a1 = c1 (+);

Example 15: Multi table hybrid join – Left Join on T2

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3
 WHERE a1 = b1
   AND b1 = c1 (+);

Example 16: Multi table hybrid join – Left Join on T1 and T2

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3
 WHERE a1 = b1 (+)
   AND b1 = c1 (+);

Example 17: Multi table hybrid join – Left Join on T1 and T2; extra Outer Join condition added

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3
 WHERE a1 = b1 (+)
   AND b1 = c1 (+)
   AND a1 = c1 (+);

Example 18: Multi table hybrid join – Left Join on T1 and T2; extra condition added as Inner Join

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3
 WHERE a1 = b1 (+)
   AND b1 = c1 (+)
   AND a1 = c1;

Example 19: Multi table hybrid join – Right Join on T3 using T1

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3
 WHERE a1 = b1
   AND a1(+) = c1;

Example 20: Multi table hybrid join – Right Join on T3 using T2

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3
 WHERE a1 = b1
   AND b1(+) = c1;

Example 21: Multi table hybrid join – Right Join on T3 using T1 and T2

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3
 WHERE a1 = b1
   AND a1(+) = c1
   AND b1(+) = c1;

Example 22: All tables hybrid Join – T1 Inner Join T2, T2 Left Join T3, T3 Left Join T4

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3, t4
 WHERE a1 = b1
   AND b1 = c1(+)
   AND c1 = d1(+);

Example 23: All tables hybrid Join- T1 Inner Join T2, T2 Left Join T3, T3 Right Join T4

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3, t4
 WHERE a1 = b1
   AND b1 = c1(+)
   AND c1(+) = d1;

Example 24: All tables hybrid Join- T1 Left Join T2, T2 Left Join T3, T3 Right Join T4

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3, t4
 WHERE a1 = b1 (+)
   AND b1 = c1(+)
   AND c1(+) = d1;

Example 25: All tables hybrid Join- T1 Right Join T2, T2 Left Join T3, T3 Right Join T4

SELECT t1.*, t2.*, t3.* 
  FROM t1, t2, t3, t4
 WHERE a1(+) = b1
   AND b1 = c1(+)
   AND c1(+) = d1;

Simplifying writing Joins through ANSI Syntax in Oracle

Understanding large and complex join conditions that uses Oracle native outer join operator (+), creates a little confusion for developers or sometime becomes cumbersome if migrating code to other databases. Therefore prefer ANSI join over native join syntax.

Take an example in Oracle join that contains (+) operator for outer joins.

SELECT t1.*, t2.*, t3.*, t4.*
  FROM t1, t2, t3, t4
 WHERE b1 = c1(+)
   AND a1(+) = b1
   AND d1(+) = c1;

	A1 A2		      B1 B2		    C1 C2		  D1 D2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
	10 T1-1 	      10 T2-1		    10 T3-1		  10 T4-1
			      22 T2-2
	30 T1-3 	      30 T2-3

Following code is ANSI complaint join syntax for above code.

SELECT t1.*, t2.*, t3.*, t4.*
  FROM t1 
       RIGHT JOIN t2 ON a1 = b1
       LEFT JOIN t3  ON b1 = c1
       LEFT JOIN t4 ON c1 = d1;

	A1 A2		      B1 B2		    C1 C2		  D1 D2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
	10 T1-1 	      10 T2-1		    10 T3-1		  10 T4-1
			      22 T2-2
	30 T1-3 	      30 T2-3

Oracle Outer Join operator hazy behavior

Outer joins in Oracle can be implemented using (+) operator or ANSI style of writing. As ANSI syntax is universally accepted in almost all RDBMS, we recommend you to use ANSI syntax to write joins. There is no performance gain of one style over the other. Joins implemented in ANSI syntax offers following benefits.

ANSI code is portable as is or with minimal changes to other RDBMS.

ANSI syntax is key word based and hence is easy to follow in complex and bigger join queries with multiple tables.

The order of join execution is the order you specify in your ANSI syntax. Whereas Oracle native joins are determined by Oracle optimizer depending on several factors.

When multiple tables are joined using Oracle (+) operator, sometimes the result set might be indeterministic. You end up in lot of confusion with the abnormal behavior of (+) operator when you have too many LEFT OUTER and RIGHT OUTER joins in the same query.

Consider below scenario that uses ANSI operator to perform joins.

SELECT t1.*, t2.*, t3.*, t4.*
  FROM t1 
       RIGHT JOIN t2 ON a1 = b1
       LEFT JOIN t3  ON b1 = c1
       RIGHT JOIN t4 ON c1 = d1;

	A1 A2		      B1 B2		    C1 C2		  D1 D2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
	10 T1-1 	      10 T2-1		    10 T3-1		  10 T4-1
									  44 T4-3
									  40 T4-2
									 444 T4-4

If asked to convert the code in Oracle native join format using (+) operator, then someone might do something similar to below.

SELECT t1.*, t2.*, t3.*, t4.*
  FROM t1, t2, t3, t4
 WHERE a1(+) = b1
   AND b1 = c1(+)
   AND d1 = c1(+);

	A1 A2		      B1 B2		    C1 C2		  D1 D2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
	10 T1-1 	      10 T2-1		    10 T3-1		  10 T4-1
	10 T1-1 	      10 T2-1					 444 T4-4
	10 T1-1 	      10 T2-1					  44 T4-3
	30 T1-3 	      30 T2-3					 444 T4-4
			      22 T2-2					  10 T4-1
	30 T1-3 	      30 T2-3					  10 T4-1
			      22 T2-2					 444 T4-4
			      22 T2-2					  44 T4-3
	10 T1-1 	      10 T2-1					  40 T4-2
	30 T1-3 	      30 T2-3					  40 T4-2
	30 T1-3 	      30 T2-3					  44 T4-3
			      22 T2-2					  40 T4-2

Output looks strange though, this is the behavior of Oracle (+) operator. Using (+) operator for multi-table LEFT JOIN and RIGHT JOIN, may sometime bring you hidden surprises and might lead to undesired and unpredictable output. You must be sure and confident enough on what kind of join your condition is evaluating, if using Oracle (+) operator to perform outer joins for muti-table join condition. If not, use ANSI join syntax instead.

Hence, we recommend using ANSI join syntax over (+) operator outer joins.

Key Take Aways from Oracle Joins

  • To join two tables, primary key or unique key is not a mandate. The tables must have common data to join with.
  • Results (Number of rows) does not change in equi-join when you join with the same columns even if the order of tables in FROM clause is not same.
  • You can use unique key column from either table to join with subsequent tables. The final result remains unchanged by changing previous tables unique key in the join condition.
  • Joins in Oracle are executed from top to bottom in order. The first two tables are joined first, then the result set is used to join with latter tables.
  • Oracle (+) operator can not be used to perform Full Outer Join, use Full Outer Join instead.
  • When multiple tables are joining, ANSI join syntax is easy to understand compared to Oracle native outer join syntax that uses (+) operator. There is no performance gain for one syntax over the other.

Was this article helpful to you? Yes No

How can we help?