This page explains how to use different types of SQL JOIN to combines records from two tables.
In practice joining two tables requires that the related columns, used to join these tables, are indexed properly.
The related columns are often the primary and foreign keys of the joined tables.

These tables and their data are used in the examples bellow:

• create table table1 (rownum1 int, c1 char, primary key (rownum1));
• create table table2 (rownum2 int, c2 char, primary key (rownum2));
• insert into table1(rownum1,c1) values(1,'a'); insert into table1(rownum1,c1) values(2,'a'); insert into table1(rownum1,c1) values(3,'b'); insert into table1(rownum1,c1) values(4,'c'); insert into table1(rownum1,c1) values(5,'c'); insert into table1(rownum1,c1) values(6,'d'); COMMIT;
• insert into table2(rownum2,c2) values(1,'a'); insert into table2(rownum2,c2) values(2,'b'); insert into table2(rownum2,c2) values(3,'b'); insert into table2(rownum2,c2) values(4,'e'); insert into table2(rownum2,c2) values(5,'e'); insert into table2(rownum2,c2) values(6,'f'); COMMIT;

• Table1 "a" "a" "b" "c" "c" "d"

  Table2 "a" "b" "b" "e" "e" "f"

  
  

[INNER] JOIN select * from table1 t1 inner join table2 t2 on t1.c1=t2.c2
LEFT [OUTER] JOIN select * from table1 t1 left outer join table2 t2 on t1.c1=t2.c2	LEFT [OUTER] JOIN (excluding INNER JOIN) select t1.rownum1,t1.c1 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2 where t2.rownum2 is null
RIGHT [OUTER] JOIN select * from table1 t1 right outer join table2 t2 on t1.c1=t2.c2	RIGHT [OUTER] JOIN (excluding INNER JOIN) select t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2 where t1.rownum1 is null
FULL [OUTER] JOIN select * from table1 t1 full outer join table2 t2 on t1.c1=t2.c2	FULL [OUTER] JOIN (excluding INNER JOIN) select * from table1 t1 full outer join table2 t2 on t1.c1=t2.c2 where t1.rownum1 is null or t2.rownum2 is null
CROSS JOIN select * from table1 t1,table2 t2
Semi join select * from table1 t1 where exists ( select 1 from table2 t2 where t2.c2=t1.c1 )	Anti join select * from table1 t1 where not exists ( select 1 from table2 t2 where t2.c2=t1.c1 )

• 
  
• Sample Query:
  select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 inner join table2 t2 on t1.c1=t2.c2 order by t1.rownum1,t2.rownum2
  
• Pseudo-code:
  for (rownum1,c1) in table1 for (rownum2,c2) in table2 if c1==c2 print rownum1,c1,rownum2,c2
  
• Query output:
  

  rownum1	c1	rownum2	c2
  1	"a"	1	"a"
  2	"a"	1	"a"
  3	"b"	2	"b"
  3	"b"	3	"b"

  
  

• 
  
• Sample Query:
  select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2 order by t1.rownum1,t2.rownum2
  
• Pseudo-code:
  for (rownum1,c1) in table1 flag=not-found for (rownum2,c2) in table2 if c1==c2 { print rownum1,c1,rownum2,c2 flag=found } if flag==not-found print rownum1,c1,(null),(null)
  
• Query output:
  

  rownum1	c1	rownum2	c2
  1	"a"	1	"a"
  2	"a"	1	"a"
  3	"b"	2	"b"
  3	"b"	3	"b"
  4	"c"	(null)	(null)
  5	"c"	(null)	(null)
  6	"d"	(null)	(null)

  
  

• 
  
• Sample Query:
  select t1.rownum1,t1.c1 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2 where t2.rownum2 is null order by t1.rownum1
  
• Pseudo-code:
  for (rownum1,c1) in table1 flag=not-found for c2 in table2 if c1==c2 { flag=found break } if flag==not-found print rownum1,c1
  
• Query output:
  

  rownum1	c1
  4	"c"
  5	"c"
  6	"d"

  
  

• 
  
• Sample Query:
  select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2 order by t1.rownum1,t2.rownum2
  
• Pseudo-code:
  for (rownum2,c2) in table2 flag=not-found for (rownum1,c1) in table1 if c1==c2 { print rownum1,c1,rownum2,c2 flag=found } if flag==not-found print (null),(null),rownum2,c2
  
• Query output:
  

  rownum1	c1	rownum2	c2
  1	"a"	1	"a"
  2	"a"	1	"a"
  3	"b"	2	"b"
  3	"b"	3	"b"
  (null)	(null)	4	"e"
  (null)	(null)	5	"e"
  (null)	(null)	6	"f"

  
  

• 
  
• Sample Query:
  select t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2 where t1.rownum1 is null order by t2.rownum2
  
• Pseudo-code:
  for (rownum2,c2) in table2 flag=not-found for c1 in table1 if c1==c2 { flag=found break } if flag==not-found print rownum2,c2
  
• Query output:
  

  rownum2	c2
  4	"e"
  5	"e"
  6	"f"

  
  

• 
  
• Sample Query (1):
  select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 full outer join table2 t2 on t1.c1=t2.c2 order by t1.rownum1,t2.rownum2
  
• Sample Query (2):
  select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2 union select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2
  
• Pseudo-code:
  for (rownum1,c1) in table1 flag=not-found for (rownum2,c2) in table2 if c1==c2 { print rownum1,c1,rownum2,c2 flag=found } if flag==not-found print rownum1,c1,(null),(null) for (rownum2,c2) in table2 flag=not-found for c1 in table1 if c1==c2 { flag=found break } if flag==not-found print (null),(null),rownum2,c2
  
• Query output:
  

  rownum1	c1	rownum2	c2
  1	"a"	1	"a"
  2	"a"	1	"a"
  3	"b"	2	"b"
  3	"b"	3	"b"
  4	"c"	(null)	(null)
  5	"c"	(null)	(null)
  6	"d"	(null)	(null)
  (null)	(null)	4	"e"
  (null)	(null)	5	"e"
  (null)	(null)	6	"f"

  
  

• 
  
• Sample Query (1):
  select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 full outer join table2 t2 on t1.c1=t2.c2 where t1.rownum1 is null or t2.rownum2 is null order by t1.rownum1,t2.rownum2
  
• Sample Query (2):
  select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2 where t2.rownum2 is null union select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2 where t1.rownum1 is null
  
• Pseudo-code:
  for (rownum1,c1) in table1 flag=not-found for c2 in table2 if c1==c2 { flag=found break } if flag==not-found print rownum1,c1,(null),(null) for (rownum2,c2) in table2 flag=not-found for c1 in table1 if c1==c2 { flag=found break } if flag==not-found print (null),(null),rownum2,c2
  
• Query output:
  

  rownum1	c1	rownum2	c2
  4	"c"	(null)	(null)
  5	"c"	(null)	(null)
  6	"d"	(null)	(null)
  (null)	(null)	4	"e"
  (null)	(null)	5	"e"
  (null)	(null)	6	"f"

  
  

• 
  
• Sample Query:
  select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1,table2 t2 order by t1.rownum1,t2.rownum2
  
• Pseudo-code:
  for (rownum1,c1) in table1 for (rownum2,c2) in table2 print rownum1,c1,rownum2,c2
  
• Query output:
  

  rownum1	c1	rownum2	c2
  1	"a"	1	"a"
  1	"a"	2	"b"
  1	"a"	3	"b"
  1	"a"	4	"e"
  1	"a"	5	"e"
  1	"a"	6	"f"
  2	"a"	1	"a"
  2	"a"	2	"b"
  2	"a"	3	"b"
  2	"a"	4	"e"
  2	"a"	5	"e"
  2	"a"	6	"f"
  3	"b"	1	"a"
  3	"b"	2	"b"
  3	"b"	3	"b"
  3	"b"	4	"e"
  3	"b"	5	"e"
  3	"b"	6	"f"
  4	"c"	1	"a"
  4	"c"	2	"b"
  4	"c"	3	"b"
  4	"c"	4	"e"
  4	"c"	5	"e"
  4	"c"	6	"f"
  5	"c"	1	"a"
  5	"c"	2	"b"
  5	"c"	3	"b"
  5	"c"	4	"e"
  5	"c"	5	"e"
  5	"c"	6	"f"
  6	"d"	1	"a"
  6	"d"	2	"b"
  6	"d"	3	"b"
  6	"d"	4	"e"
  6	"d"	5	"e"
  6	"d"	6	"f"

  
  

• 
  
• Sample Query (1):
  select t1.rownum1,t1.c1 from table1 t1 where t1.c1 in ( select t2.c2 from table2 t2 ) order by t1.rownum1
  
• Sample Query (2):
  select t1.rownum1,t1.c1 from table1 t1 where exists ( select 1 from table2 t2 where t2.c2=t1.c1 ) order by t1.rownum1
  
• Pseudo-code:
  for (rownum1,c1) in table1 for c2 in table2 if c1==c2 { print rownum1,c1 break }
  
• Query output:
  

  rownum1	c1
  1	"a"
  2	"a"
  3	"b"

  
  

• 
  
• Sample Query (1):
  select t1.rownum1,t1.c1 from table1 t1 where t1.c1 not in ( select t2.c2 from table2 t2 ) order by t1.rownum1
  
• Sample Query (2):
  select t1.rownum1,t1.c1 from table1 t1 where not exists ( select 1 from table2 t2 where t2.c2=t1.c1 ) order by t1.rownum1
  
• Pseudo-code:
  for (rownum1,c1) in table1 flag=not-found for c2 in table2 if c1==c2 { flag=found break } if flag==not-found print rownum1,c1
  
• Query output:
  

  rownum1	c1
  4	"c"
  5	"c"
  6	"d"

  
  

• 
  
• Sample Query:
  select t1.rownum1 as "rownum",t1.c1 as "c" from table1 t1 union select t2.rownum2 as "rownum",t2.c2 as "c" from table2 t2
  
• Pseudo-code:
  for (rownum1,c1) in table1 for (rownum2,c2) in table2 if rownum1==rownum2 && c1==c2 { put (rownum1,c1) in set-without-duplicates break } for (rownum1,c1) in set-without-duplicates print rownum1,c1 for (rownum1,c1) in table1 if (rownum1,c1) not in set-without-duplicates print rownum1,c1 for (rownum2,c2) in table2 if (rownum2,c2) not in set-without-duplicates print rownum2,c2
  
• Query output:
  

  rownum	c
  1	"a"
  2	"a"
  2	"b"
  3	"b"
  4	"c"
  4	"e"
  5	"c"
  5	"e"
  6	"d"
  6	"f"

  
  

• 
  
• Sample Query:
  select t1.rownum1 as "rownum",t1.c1 as "c" from table1 t1 intersect select t2.rownum2 as "rownum",t2.c2 as "c" from table2 t2
  
• Pseudo-code:
  for (rownum1,c1) in table1 for (rownum2,c2) in table2 if rownum1==rownum2 && c1==c2 { put (rownum1,c1) in set-without-duplicates break } for (rownum1,c1) in set-without-duplicates print rownum1,c1
  
• Query output:
  

  rownum	c
  1	"a"
  3	"b"