The Basics of the JOINS in SQL database

This blog intends to understand the basic concept of Joins. Once theoretical concepts are clear then it will be easy to write a query based on joins. Joins are easy to execute once you understand logic while executing it. In Joins you need to retrieve the data from multiple tables. There are three Retrieval Methods i.e. Projection, Selection, and Joins.

Usually, we store the data in different tables. what if we used only one single table for all data? If only one table is there while executing each query you need to access redundant data also which makes the performance of the query weak. And also it's better to make different tables for different information for retrieving purposes. Easy to read when multiple tables are there. Execution time is also reduced in relative manner. So, need to create separate tables then you can connect them using the primary key and foreign key. This is actually RDBMS (Relational Database Management System). Readability will be more easier if there is no redundant data in the database.

When the user want to retrieve information from more than one table, the user needs to connect that tables which needed. Here the Joins come. Joins is basically used to retrieve the data from multiple tables. Joins has two types ANSI format joins and Non-ANSI format joins. The following tree diagram can explain the types of joins.

1. Inner join :

An INNER JOIN is one of the JOIN operations. This is how it works, from Table1 each n every value try to match with every value from Table2. And if it matches then it means that’s a common value.

The output of matching records from one table to another table is called as Inner Join. Each matching value from both fields will be shown in the join. Inner Join only shows matching records or common records.

 Venn Diagram of Inner join:

As we all know this basic Venn diagram in which two sets are there A and B. Information or data which is common in both sets is retrieved.     

Example of Syntax :

Select * from Customer
Select * from Order

Select * from order
Inner join customer on order.customerID = customer.customerID

Here, in this example of Inner Join syntax in which the CustomerId is a column, and order and customer are tables. While executing this query of Inner Join will show common values from both tables.

Now, Let's see the joins concept with basic examples.

Example 1

Let's consider two tables T1 and T2 Which contains some numbers that could be customer Id, ProductId or some marks in exams, etc.

Here, It comes the output of Inner join above Two tables.

In the Executed result set, some values matches i.e. common values from both the Tables T1 and T2. As shown below four values matched that is highlighted by red line.

Example 2

Consider Table 1 and Table 2 , these two tables are provided in which some information is stored. For now, consider one field from each table. Here there is some information in each table that contains students interested in admission of some courses of film making. Table 1 has the course name with the course ID(CID).      Table 2 has Student names with student ID and admitted course ID.

       Table 1                                                                 Table 2                          

After applying Inner Join to both these tables the output will be shown like this.

So, in this scenario Field CID from Table 1 which is the primary key in Table 1 matches with Field CID from Table 2 which is the foreign key over here.

And the Records in which the values matches are shown in the table i.e. common rows from the both tables. In null condition, null value does not match with another null value.

2. Outer join :

The Basic concept of Outer Join in which output will show matching records from both tables as well as unmatched records.

Left join:

It is a Left outer join but usually called as Left join. In which execution will show the common records from both the tables along with uncommon records from the left table.

Venn Diagram of Left Join :

Example 1 

Suppose, here there are two tables one is Table1 and the other is Table2. Both tables contain some information like employeeID, customerID, some series no., some product ID, etc.

                                                   

The values which are in the yellow circle are uncommon values from Table1. The common values matched with each other shown by red lines.

Example 2

Suppose there are two tables order and customer. And there is some business requirement for which left join need to use.

Syntax :

Select * from order
Left join customer on order.customerID = customer.customerID

So the output will show common values from both the tables as well as uncommon values from the order table only. In result set, First some records are common values from both tables followed by uncommon values from the order table only. Because here left join is emphasized on the order table.

Right join 

It is actually Right Outer Join but is usually called as Right Join. In which execution will show the common records from both the tables as well as uncommon records from the right table.

Venn Diagram of Right Join :     

Example 1

Consider the same tables from the Left join session i.e. Table1 and Table2.

Suppose, this time we need to use the right join on these tables as in need of business requirement.

Execution of Right join will be as shown below :

This is how Right Join works. Each value from Table2 tries to match with the value from Table1. The common records are to be shown in the output and uncommon values from Table2 only. The values which are in the yellow circle shown below are uncommon values from Table2.

Example 2

Select * from Order
Right join Customer on order.customerID = customer.customerID

So the output will show common values from the both tables as well as uncommon values from the Customer table only. First, some records are common values from both tables followed by uncommon values from the Customer table only. Because here Right join is emphasized on the Customer table. It depends on which table you want to emphasize that decides which join you should use. If you want to emphasize on the left table i.e left join and if right join that means to emphasize on the right table. 

Full Outer Join 

Full outer Join is almost like a combination of the Left Join and Right Join. So, It will show the common records from both the tables and also uncommon records from both the tables.

Venn Diagram of Full outer join :   

Basic Syntax :

SELECT * FROM Table_1
FULL OUTER JOIN Table_2ON Table1.column name = Table_2.column name

Example

Here there are two Tables Table_1 and Table_2 in which there are some values. In this example, considering values of the tables are basic numerical values i.e. integers, so that it will be easy to understand the logic of full outer join.             

Tables: Output :

The below diagram explains how Full Outer Join works. Red lines show common values. And the values which are in the yellow boxes are uncommon values. In the output first four rows are common values from both the tables followed by three rows that are uncommon values from Table_1 followed by three rows that are uncommon values from Table_2.

As you can see, Null is not considered as a common value because Null never matches with another Null value. So Null is like an uncommon value concluded at this point.

3. Cross join

Every row of the first table will join with each and every row of the second table.

Assume the first table has rows ‘X’ and the Second table has rows ‘Y’ so in the result of a cross join between the First table and the second table will have rows ‘X*Y’ i.e. multiplication of no. of rows from both the tables.

Syntax :  

Select * from Table_1
Cross join Table_2

Example :  Let’s consider two tables as shown in below :                            

In these tables, Table_1 has 4 rows and Table_2 has also 4 rows. So, in the executed result no. of the total records will be 16 i.e. multiplication of the number of rows from Table_1 and Table_2. The number of rows will not be same always. So the final result table will be as shown below. As explained above, total 16 rows are there in the result set. 

Logic is shown in the below table. First values from Table_1 will join with each n every value from Table_2. The same logic applies to all values. Every value from Table_1 will join with all values from Table_2.     

Self Join

Its Non-ANSI Format join called as old style also. As the name suggests, self-join means table data joins with itself only. A SELF JOIN is basically any JOIN operation in which to relate a table with itself. This join can be executed on a single table only. Sometimes you need to use alias too.

Example

Consider, the following table named an Employee in which a list of employees and their managers is included. The table name is the employee table. It’s a list of codes of every employee that has a manager with the name of the employee’s manager:

 Syntax for this example

  SELECT
    employee.ID,
        employee.Employee_Name,
        employee.Manager_ID,
        manager.Employee_Name  as Manager_Name
FROM Employees employee
JOIN Employees manager
ON employee.Manager_ID = manager.ID

Here’s the Executed result table :

Conclusion

 As per Business Requirements, the user needs to find out which join should be applied. For that, you need to improve your analytical skills. By working on queries, analytical skills can be technologically advanced for that theoretical basic concepts should be clear.

Learning SQL is very easy but getting pro in it, is a bit tough but not impossible. It can be achieved by practicing progressively more. I didn’t use sql server here to explain the logic. The main focus was to understand the concept before you implement it. Just google it, there are several examples on the internet. You can get solved examples too, you will understand quickly after reading this blog. Do more and more practice so that you will be next to write the blog more effectively.

I hope this blog will be supportive to understand the basics of Join Concepts.

 

By,

Shital Mahajan