Join

A join is the process of combining data from two or more tables using matching columns.

Examples

SELECT * FROM emp, dept
WHERE  emp.deptno = dept.deptno;

SELECT * FROM dept
  INNER JOIN emp ON (emp.deptno = dept.deptno);

SELECT * FROM emp NATURAL JOIN dept;

Usual types of join

This section will shows most often use types of join you have from an example with 2 tables, A and B, each one containing 2 rows, one with common "id" and the other one with a specific "id". The pictures give you an image of the result set. The test case is the following one:

DROP TABLE a;
DROP TABLE b;
CREATE TABLE a (idA int PRIMARY KEY, valA varchar2(10));
CREATE TABLE b (idB int PRIMARY KEY, valB varchar2(10));
INSERT INTO a VALUES (0, 'Only in A');
INSERT INTO a VALUES (1, 'in A and B');
INSERT INTO b VALUES (1, 'in A and B');
INSERT INTO b VALUES (2, 'Only in B');
COMMIT;

SQL> SELECT * FROM a;

       IDA VALA
---------- ----------
         0 Only in A
         1 in A and B

SQL> SELECT * FROM b;

       IDB VALB
---------- ----------
         1 in A and B
         2 Only in B

Note: the join conditions given in this section are on the primary keys, this is not necessary, join conditions may be on any column (see the examples given in the "Also see" section links).

Inner join

Inner Join

An inner join retrieves the rows that have a common key in the 2 tables:

SQL> SELECT * 
  2  FROM a INNNER JOIN b ON idA = idB;

       IDA VALA              IDB VALB
---------- ---------- ---------- ----------
         1 in A and B          1 in A and B

Left outer join

Left Outer Join

A left outer join retrieves all the rows of the left table plus the information of the right table with a common key:

SQL> SELECT * 
  2  FROM a LEFT OUTER JOIN b ON idA = idB;

       IDA VALA              IDB VALB
---------- ---------- ---------- ----------
         1 in A and B          1 in A and B
         0 Only in A

Right outer join

Right Outer Join

A right outer join retrieves all the rows of the right table plus the information of the left table with a common key:

SQL> SELECT * 
  2  FROM a RIGHT OUTER JOIN b ON idA = idB;

       IDA VALA              IDB VALB
---------- ---------- ---------- ----------
         1 in A and B          1 in A and B
                               2 Only in B

Left outer join, left only rows

Left Outer Join, left only rows

This left outer join with an additional condition allows to retrieve the rows whose key is only in the left table:

SQL> SELECT * FROM a LEFT OUTER JOIN b ON idA = idB 
  2  WHERE idB IS NULL;

       IDA VALA              IDB VALB
---------- ---------- ---------- ----------
         0 Only in A

Right outer join, right only rows

Right Outer Join, right only rows

This right outer join with an additional condition allows to retrieve the rows whose key is only in the right table:

SQL> SELECT * 
  2  FROM a RIGHT OUTER JOIN b ON idA = idB 
  3  WHERE idA IS NULL;

       IDA VALA              IDB VALB
---------- ---------- ---------- ----------
                               2 Only in B

Full outer join

Full Outer Join

A full outer join retrieves the rows that in the two tables associating the rows that have the same key in both tables:

SQL> SELECT * 
  2  FROM a FULL OUTER JOIN b ON idA = idB;

       IDA VALA              IDB VALB
---------- ---------- ---------- ----------
         1 in A and B          1 in A and B
         0 Only in A
                               2 Only in B

Full outer join, only not common rows

Full Outer Join, only not common rows

This full outer join with an additional condition allows to retrieve the rows that are in the 2 tables except those with a common key:

SQL> SELECT * 
  2  FROM a FULL OUTER JOIN b ON idA = idB 
  3  WHERE idA IS NULL OR idB IS NULL;

       IDA VALA              IDB VALB
---------- ---------- ---------- ----------
         0 Only in A
                               2 Only in B

Also see

Join methods:

• Cartesian join
• Equi join
• Inner join
• Natural join
• Nonequi join
• Outer join
• Self join

Join optimizations:

• Cluster join
• Hash join
• Nested loops join
• Sort merge join

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#