Joins to be clear


Just to be clear, there are 5 types of a SQL joins:



  1. Cross-join or Cartesian Product - Joins all rows from one relation with rows from the other relation

  2. Inner join - Joins only those rows that have same data in the common attributes of the two relations

  3. Outer join - Joins rows in two relations that have same data in the common attributes, and for all the remaining rows (that couldn't be joined) adds NULLs to the attribute values

  4. Left outer join - Joins all rows in the left relation either with rows in the right relation (when they have same values for common attributes) or with NULLs

  5. Right outer join - Joins all rows in the right relation either with rows in the left relation (when they have same values for common attributes) or with NULLs.

Just rolling it out, because some of us tend to confuse between a cross-join and an outer join and so forth.


Keep joining.


PS: Count the number of times I've called out the word "Join" or it's extensions in this blog : )


Ads-by-Microsoft



  

Comments (2)
  1. 3 – Outer join – Joins rows in two relations that have same data in the common attributes, and for all the remaining rows (that couldn’t be joined) adds NULLs to the attribute values

    Number 3 is a full join in T-SQL, T-SQL from Microsoft have the outer keyword in Right Join and Left Join, which is a little confusing for me.

    Please join but join carefully.

Comments are closed.

Skip to main content