What is the difference between various types of joins? For instance, there’s inner join, left join, right join, full join, cross join, natural join, equi-join, and semi-join?

Different types of joins are required depending on the type of data wrangling one needs to do. Let’s work with an example here. In this toy example, we have 2 tables-

  1. Married- This contains the name and social security number ( SSN) of the people who are married.
  2. Home Owners- This contains the name and social security number ( SSN) of the people who own a home

SSN is unique for each person and will be used as a primary key to merge two tables.

Let’s now look at the types of joins using the above 2 tables and which group they would provide the information on from both or either tables.

  1. Inner Join- If we want to find out people who are both married and home owners- Mia, Liz, Benjamin, Ava.
  2. Left Join- All married folks, whether or not home owners- John, James, Harper, Amy, Mia, Liz, Benjamin, Ava.
  3. Right Join– All home owners, whether or not married- Mia, Liz, Benjamin, Ava, Sophia, William, Jacob, Michael
  4. Full Join- Everyone from both tables- Mia, Liz, Benjamin, Ava, Sophia, William, Jacob, Michael, John, James, Harper, Amy.

left

Use keys from left frame only

right

Use keys from right frame only

outer

Use union of keys from both frames

inner

Use intersection of keys from both frames

About sqlschool

SQL School (Regd: SequelGate Innovative Technologies Pvt. Ltd.) is one of fastest growing organization delivering Realtime Training and Realtime Projects exclusively on Microsoft SQL Server. Our Training services include SQL DBA Online Training, MSBI Online Training, SQL DBA Classroom Training, MSBI Classroom Training and Realtime Projects. SQL School, established in February, 2008 is now, one of the best institute offering Trainings on SQL Server, SQL Database Administration (DBA) and Business Intelligence (BI) technologies. We have been working with numerous consultancies in India, US, UK and Australia. We undertake Online Trainings and Corporate Trainings on SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012 (DENALI) versions. We make sure that all our sessions are very much interactive and well structured. We encourage every participant to come up with his / her own queries during & after the training sessions. We prefer practical approach rather than theoretical information to master the technical depth of the technology. We provide excellent Lab Handouts for practice, Realtime Case Studies and Projects on SQL Server Administration (SQL DBA) and Microsoft Business Intelligence (MS BI) Training courses. Free LIVE DEMOs would be provided prior to training registration. Specific DAY to DAY Course Plan will also be shared prior to training registration to ensure transparency of our Training services.