Hi, I am going to explain the basics of joins in SQL queries. This idea of writing a blog about SQL joins came when me and my colleagues discussing joining two tables. The basic idea of this blog is to provide clarity to beginners and intermediate programmers who don't have an idea of how joins works.

What are Joins

Joins are used to combine data from two or more tables. Most of the time, joins are done using primary key and foreign key relationships. Some of our databases may have the foreign key relationships defined, while others may not. The relation between two tables is based on business-related understanding. This means the relationship comes from the business logic, not from the technology. For example, the "orders" table and "order items" table are related by business understanding. An order contains a list of items. When we bring this concept into programming, we express that "order items" are under a specific order. To establish this relationship, we use primary key and foreign key relationships. The program/technology doesn't even know about the data and relationship; it just states that this table is linked to that table by certain columns. Additionally, it specifies that references are available and that we can't delete certain records, and so on.

What are the Various Join Types

There are multiple types of joins, which can be grouped as follows:

• FULL JOIN
• INNER JOIN
• OUTER JOIN

For more details, visit: Visual Representation of SQL Joins

Understanding the types of joins may be simple, but the challenge lies in how we perform the join. For example, if I have two tables, "customers" and "students," and there is no direct relationship between them, can I join these tables? If you say no, then the understanding may need to be clarified. Basically, how we write joins is as follows:

        
select 
    <columns> 
from 
    <table1>
    Inner/Left/right> join <table2> 
        on <conditions>
    

Here, the joining is done via the condition rather than the keys. I want to join "table1" to "table2" with a specific condition. The SQL query condition is a comparison expression that represents a boolean result, which is quite simple, right? For the above question, business logic says the join is invalid, not that we can't join. Technically, we can join the tables, but the result may not be meaningful.

How Does Joins Work

Basically, joins work in a simple way. Think of it like this: the rows of "table1" are compared with each row in "table2" based on the condition. This is done via an iteration of each row in both tables. During the joining process, the rows that satisfy the condition are added to the result, while the others are ignored and not added to the result.

In simple words, each row on table one iterated with foreach and compared with each row in the table 2 iterated with another foreach loop. So each one row from table one iterated with all rows in the table 2 via the nested foreach loop. like this,

        
foreach(var row in table1.rows)
{
    foreach(var row1 in table2.rows)
    {
        if(condition)
        {
            //Add to results
        }
    }
}
    

For example, consider the following tables:

I am going to join both tables. How will I write the query?

        
select 
    id, name, key, value 
from 
    table1 
    left join table2 
        on <condition>
    

In this situation, if we give the condition as 1 = 1 or true = true, which always returns true, then the result will join all rows in table 1 with all rows in table 2 and return. The result will look like:

Alternatively, if we give the condition as 1 = 2 or true = false, which always results in false, no rows will be returned. The result will look like:

For example, if we give the condition as id = 1, then only the rows that satisfy the condition, i.e., rows with id as 1, will be returned. The result will look like:

Using Multiple Join Conditions

We can use multiple join conditions to join one table to another. We can use as many conditions on the join as required. The conditions are finally used or combined with logical operators like AND, OR, etc. These conditions are similar to the conditions used in the WHERE clause. We can also use subqueries in the condition; ultimately, it should be a comparison expression results in Boolean result.

Conclusion

A join query doesn't necessarily require a relationship to join technically. A join needs a condition / a comparison expression that results in a Boolean value. However, in most cases, we join tables that have relationship between them. The situation of joining tables without a direct relationship occurs rarely, but understanding how joins work is important.

Thank you..!

Written On: on february 26, 2023