sql JOIN(Inner, Outer, Cross, Self)

This post was migrated from Tistory. You can find the original here.

Inner Join

• Extracts only the intersecting rows. (No NULLs.)

Outer Join

• Left Outer Join
  • Extracts based on the left table. All rows from the left table appear regardless of the JOIN condition.\
  • Any part that doesn’t match the JOIN condition (on) is filled with NULL.
    \
• Right Outer Join
  • Extracts based on the right table. All rows from the right table appear regardless of the JOIN condition.\
  • Any part that doesn’t match the JOIN condition (on) is filled with NULL.
    \
• Full Outer Join
  • Extracts all rows from both tables. All rows from both sides appear regardless of the JOIN condition.\
  • This does not mean extracting every combination of data. (There is still a JOIN condition.)\
  • Any part that doesn’t match the JOIN condition (on) is filled with NULL.

Cross Join

• Extracts every possible combination of data. (There is no JOIN condition.)

Self Join

• Assigns an alias to the table itself and joins it against itself.