Database_SQL_Join.md

Database SQL - Join

• Overview
• Inner Join
• Outer Join
  • Left Join
    • Left Join Excluding Inner Join
  • Right Join
    • Right Join Excluding Inner Join
  • Full Join
    • Full Join Excluding Inner Join
• Cross Join
• Self Join

Overview

graph LR;
  A["Join"] --> B1["Inner Join"];
  A --> B2["Outer Join"];
  A --> B3["Cross Join"];
  A --> B4["Self Join"];
  B2 --> C1["Left Join (Left Outer Join)"];
  B2 --> C2["Right Join (Right Outer Join)"];
  B2 --> C3["Full Join (Full Outer Join)"];
  C1 --> D1["Left Join Excluding Inner Join"];
  C2 --> D2["Right Join Excluding Inner Join"];
  C3 --> D3["Full Join Excluding Inner Join"];

Loading

Inner Join

• Venn diagram

  

• SQL example

  SELECT * FROM TableA a
  INNER JOIN TableB b
  ON a.key = b.key;

• Result example

  

Outer Join

Left Join

• Venn diagram

  

• SQL example

  SELECT * FROM TableA a
  LEFT JOIN TableB b
  ON a.key = b.key;

• Result example

  

• Notes

  • All the records from the left table will be included into the result, even if there is no matching on the right table.

Left Join Excluding Inner Join

• Venn diagram

  

• SQL example

  SELECT * FROM TableA a
  LEFT JOIN TableB b
  ON a.key = b.key
  WHERE b.key IS NULL;

• Result example

  

Right Join

• Venn diagram

  

• SQL example

  SELECT * FROM TableA a
  RIGHT JOIN TableB b
  ON a.key = b.key;

• Result example

  

Right Join Excluding Inner Join

• Venn diagram

  

• SQL example

  SELECT * FROM TableA a
  RIGHT JOIN TableB b
  ON a.key = b.key
  WHERE a.key IS NULL;

• Result example

  

Full Join

• Venn diagram

  

• SQL example

  SELECT * FROM TableA a
  FULL OUTER JOIN TableB b
  ON a.key = b.key;

• Result example

  

Full Join Excluding Inner Join

• Venn diagram

  

• SQL example

  SELECT * FROM TableA a
  FULL OUTER JOIN TableB b
  ON a.key = b.key
  WHERE a.key IS NULL or b.key IS NULL;

• Result example

  

Cross Join

• SQL example

  SELECT * FROM TableA a
  CROSS JOIN TableB b;

• Result example

  

Self Join

• SQL example

  SELECT * FROM TableA a1
  FULL OUTER JOIN TableA a2
  ON a1.column1 = a2.column2;

• Result example
  • Original table

    ID	Name	ManagerID
    456	Robert
    383	Cook	222
    777	Daniel	222
    123	Smith	456
    222	William	456

  • Result

    ID	Name	ManagerID	Manager
    456	Robert
    383	Cook	222	William
    777	Daniel	222	William
    123	Smith	456	Robert
    222	William	456	Robert