Different types of joins in SQL

What are the different types of joins? Please explain how they differ and why certain types are better in certain situations.

Solution:
JOIN is used to combine the results of two tables. To perform a join, each of the tables must have at least one field which will be used to find matching records from the other table. The join type defines which records will go into the result set.
Let’s take for example two tables: one table lists “regular” beverages, and another lists the calorie-free beverages. Each table has two fields: the beverage name and its product code. The “code” field will be used to perform the record matching.

Regular Beverages:
Name Code
Budweiser BUDWEISER
Coca-Cola COCACOLA
Pepsi PEPSI
Calorie-Free Beverage:
Code Name
COCACOLA Diet Coca-Cola
FRESCA Fresca
PEPSI Diet Pepsi
PEPSI Pepsi Light
PEPSI Purified Water

Let’s join this table by the code field. Whereas the order of the joined tables makes sense in some cases, we will consider the following statement:

[Regular Beverage] JOIN [Calorie-Free Beverage]

i.e. [Regular Beverage] is from the left of the join operator, and [Calorie-Free Beverage] is from the right.

  1. INNER JOIN: Result set will contain only those data where the criteria match. In our example we will get 3 records: 1 with COCACOLA and 2 with PEPSI codes.
    Regular_Beverages.Name Code Calorie-Free_Beverages.Name
    Coca-Cola COCACOLA Diet Coca-Cola
    Pepsi PEPSI Diet Pepsi
    Pepsi PEPSI Pepsi Light
  2. OUTER JOIN: OUTER JOIN will always contain the results of INNER JOIN, however it can contain some records that have no matching record in other table. OUTER JOINs are divided to following subtypes:
    1. LEFT OUTER JOIN, or simply LEFT JOIN: The result will contain all records from the left table. If no matching records were found in the right table, then its fields will contain the NULL values. In our example, we would get 4 records. In addition to INNER JOIN results, BUDWEISER will be listed, because it was in the left table.
      Regular_Beverages.Name Code Calorie-Free_Beverages.Name
      Budweiser BUDWEISER NULL
      Coca-Cola COCACOLA Diet Coca-Cola
      Pepsi PEPSI Diet Pepsi
      Pepsi PEPSI Pepsi Light
    2. RIGHT OUTER JOIN, or simply RIGHT JOIN: This type of join is the opposite of LEFT JOIN; it will contain all records from the right table, and missing fields from the left table will contain NULL. If we have two tables A and B, then we can say that statement A LEFT JOIN B is equivalent to statement B RIGHT JOIN A. In our example, we will get 5 records. In addition to INNER JOIN results, FRESCA and WATER records will be listed.
      Regular_Beverages.Name Code Calorie-Free_Beverages.Name
      Coca-Cola COCACOLA Diet Coca-Cola
      NULL FRESCA Fresca
      Pepsi PEPSI Diet Pepsi
      Pepsi PEPSI Pepsi Light
      NULL Water Purified Water
    3. FULL OUTER JOIN. This type of join combines the results of LEFT and RIGHT joins. All records from both tables will be part of the result set, whether the matching record exists in the other table or not. If no matching record was found then the corresponding result fields will have a NULL value. In our example, we will get 6 records.
      Regular_Beverages.Name Code Calorie-Free_Beverages.Name
      Budweiser BUDWEISER NULL
      Coca-Cola COCACOLA Diet Coca-Cola
      NULL FRESCA Fresca
      Pepsi PEPSI Diet Pepsi
      Pepsi PEPSI Pepsi Light
      NULL Water Purified Water
    Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: