独家优惠奖金 100% 高达 1 BTC + 180 免费旋转

Journey into the world of SQL Joins

The most important component of SQL are joins it can as straight forward as possible and also pretty complex as I am going to explain below.

In SQL there are four kinds of joins which are:

Inner Join is the kind of join where we join only we join two or more tables if and only if the join condition is satisfied.This is also the default type of join.

Syntax:

Left Join is the kind of join where we join two or more tables where all the records from the left table are displayed even when the join condition is satisfied or not.

Syntax:

Right Join is the kind of join where we join two or more tables where all the records from the right table are displayed even when the join condition is satisfied or not.

Syntax:

Scenario 1:

Starting with a simple scenario, where there are unique records in both the tables.

Considering the following tables for this Scenario

Table 1:

Table 2:

Inner Join:

Query:

Output:

Above we can see that only the records that satisfy the condition on which the join is performed are returned by the Query.

Left Join:

Query:

Output:

Above we can see that all the records from the table which is on the left side of the join condition is returned even though the condition is not satisfied returning values from the right table as NULLs

Right Join:

Query:

Output:

Above we can see that all the records from the table which is on the right side of the join condition is returned even though the condition is not satisfied returning values from the left table as NULLs

Full OuterJoin:

Query:

Output:

Above we can see that all the records from both the tables are returned if or not if the condition is satisfied and which ever record is not present in the other table, it ends up returning NULLs for those key values.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Scenario 2:

Diving into the waters a bit where we add a few duplicates into each of the tables.

Considering the following tables for this Scenario

Table 1:

Table 2:

Inner Join:

Query:

Output:

Interesting did you notice that the ID = 1 has been repeated over 6 times and this is because for each match occurrence is considered unique and it would perform the join 3(from TBL1)*2(from TBL2) as each of them are unique to each other.

Left Join:

Query:

Output:

This join can be considered as an extension of explanation given for the inner join and here the query return all the records from the left table (TBL1) and NULLs from the right table(TBL2) for those unmatched records.

Right Join:

Query:

Output:

This join can be considered as an extension of explanation given for the inner join and here the query return all the records from the right table (TBL2) and NULLs from the left table(TBL1) for those unmatched records.

Full Outer Join:

Query:

Output:

As we know that a FULL OUTER join is just a combination of both LEFT and RIGHT joins you can consider it as a UNION of both the outputs of LEFT and RIGHT joins.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Scenario 3:

This Scenario describes what would be the output if we are having NULLs in both the tables

Considering the following tables for this Scenario

Table 1:

Table 2:

Inner Join:

Query:

Output:

Above we can see that only the records that satisfy the condition on which the join is performed are returned by the Query.

Left Join:

Query:

Output:

Here we notice that even though there are multiple NULLs in each table unlike in the previous scenario where when multiple ids matched we got m*n number of records out of the query but for NULLs as that is not the case as NULL is not equal to NULL

Right Join:

Query:

Output:

Here we notice that even though there are multiple NULLs in each table unlike in the previous scenario where when multiple ids matched we got m*n number of records out of the query but for NULLs as that is not the case as NULL is not equal to NULL

Full Outer Join:

Query:

Output:

As we know that a FULL OUTER join is just a combination of both LEFT and RIGHT joins you can consider it as a UNION of both the outputs of LEFT and RIGHT joins.

So these are the three scenarios with the four sub scenarios which makes it a total of twelve cases which you can go through before to ace your interview.

I would also be writing a post on the more complex joins mostly used in while performing operations on Big Data.

Add a comment

What is your view?

Send

Related posts:

Why Does My Dog Poop In The Car?

These are the main causes of a dog pooping inside the automobile, even though an eager canine in the car and a lengthy car trip with a puppy may sound enjoyable to you. You’ve come to the correct…

EMEA 2022 cybersecurity predictions

Cyberattacks have increased in the EMEA region until 2021, with ransomware attacks becoming more prevalent. According to the Unit 42 Threat Report, 1H 2021 Update, the average ransom demand jumped by…

Erotic Fitness

The use of erotic fitness has become increasingly popular in recent years. The variety of ways it can be used is not just limited to entertainment, but also health & lifestyle improvements. It…