«

»

Understanding SQL Joins

Hi,

Today we will be trying to understand the already much spoken about topic in SQL (T-SQL), "Joins". SQL joins can either make or break any Sql query. In T-SQL we basically have the following types of joins:

  1. Inner Join / Natural Join
  2. Left Join / Left Outer Join
  3. Right Join / Right Outer Join
  4. Full Outer Join
  5. Cross Join / Cartesian Product

Lets us take an example of two different tables and then try to show the output using different joins. Let there be two different tables, Table T1 and Table T2. The structure of the two tables along with the content inside them is given below:

Table T1 Table T2
ColA ColB
1 AA
1 BB
2 CC
3 DD
3 EE
4 FF
5 GG
ColC ColD
1 A
1 B
2 C
3 D
4 E
4 F
6 G

Now we will try to run all the join queries on these two tables one by one and then analyze the results.

Lets talk about Inner join. Inner Join is the most simplest joins available through SQL. An SQL Inner Join works on the principle, "Return all the rows where the joining condition is met".The query that we will be using for Inner join is:

Select
	T1.ColA,
	T1.ColB,
	T2.ColC,
	T2.ColD
	FROM T1(NOLOCK)
	INNER JOIN T2(NOLOCK)
	ON T1.ColA=T2.ColC

Remember the principle on which Inner join works? The principle was "Return all the rows where the joining condition is met". Let us apply this principle here. We are supposed to return all those rows for which the value of ColA from T1 table matches the value of ColC from T2 table. So we can see that the first row in T1 table has 2 different matches in T2 table. Similarly the 2nd row again has 2 matches, the third row has just one match, the fourth has again 1 match, the fifth and sixth row again have one match in T2 table and the 7th row has no matches in Table T1. So counting the total number of matches we have around 9 matches. So there will be total 9 rows that will be returned in the output/result of the above inner join query. Lets us have a look at the result.

ColA ColB ColC ColD
1 AA 1 A
1 AA 1 B
1 BB 1 A
1 BB 1 B
2 CC 2 C
3 DD 3 D
3 EE 3 D
4 FF 4 E
4 FF 4 F

Ok, so we have now seen the inner join query result and we know how it was derived. Now we can move ahead and try a similar approach for Left join or Left Outer join. Left join also works on the same principle that inner join works on but with an additional clause. Left join will return all the rows that have a matching value in Table T2 but along with it, it will also return those values which are present only in Table T1 and do not have a matching value in Table T2. So let us try and apply this principle on the Table T1 and Table T2 using the below given query:

Select
	T1.ColA,
	T1.ColB,
	T2.ColC,
	T2.ColD
	FROM T1(NOLOCK)
	LEFT JOIN T2(NOLOCK)
	ON T1.ColA=T2.ColC

As stated earlier that it will return all the rows that were returned by inner join, so we will surely receive 9 rows, but along with it we will also receive those rows that were present only in Table T1. We have only one such row I.e. the 7th row where the value of ColA is "5". So now the total number of rows we have is 10. For the 10th row in output in the columns ColC and ColD we will receive the NULL value since the 7th row in T1 table could not find a match in Table T2. Let's have a look at the results.

ColA ColB ColC ColD
1 AA 1 A
1 AA 1 B
1 BB 1 A
1 BB 1 B
2 CC 2 C
3 DD 3 D
3 EE 3 D
4 FF 4 E
4 FF 4 F
5 GG NULL NULL

Now let us move ahead and discuss the Right Join or Right Outer Join. The Right Join is exactly similar to the Left join but instead of the unmatched rows of the Left table I.e. Table T1 we will return the unmatched rows of the right table I.e. Table T2. The 9 rows that were returned by Inner Join will remain as it is. Only the 10th row will change. Here this time the ColA and ColB will show NULL values and ColC and ColD will show the values of the unmatched row from Table T2. Let us have a look at the query and the result.

Select
	T1.ColA,
	T1.ColB,
	T2.ColC,
	T2.ColD
	FROM T1(NOLOCK)
	RIGHT JOIN T2(NOLOCK)
	ON T1.ColA=T2.ColC

ColA ColB ColC ColD
1 AA 1 A
1 AA 1 B
1 BB 1 A
1 BB 1 B
2 CC 2 C
3 DD 3 D
3 EE 3 D
4 FF 4 E
4 FF 4 F
NULL NULL 6 G

Now it's time to understand the Full Outer join. Full outer join is a combination of Inner Join, Left Outer Join and Right Outer Join. Talking about our example tables T1 and T2, we will get all the 9 output rows in the inner join output. Along with it, we will also get the the unmatched rows from the left and the right tables I.e. T1 and T2 respectively. Since we had one unmatched rows from each table T1 andIT2 I.e. the 7th row from both the tables, we will get a total of 9+1+1=11 rows in the output of Full Outer Join query. Let us have a look at the query and its result.

Select 
	T1.ColA, 
	T1.ColB, 
	T2.ColC, 
	T2.ColD 
	FROM T1(NOLOCK) 
	FULL OUTER JOIN T2(NOLOCK) 
	ON T1.ColA=T2.ColC

ColA ColB ColC ColD
1 AA 1 A
1 AA 1 B
1 BB 1 A
1 BB 1 B
2 CC 2 C
3 DD 3 D
3 EE 3 D
4 FF 4 E
4 FF 4 F
5 GG NULL NULL
NULL NULL 6 G

Last but not the least we have the CROSS Join or the Cartesian Product. This join is basically a combination of every row in Table T1 with every other row in Table T2. So if we have 7 rows in T1 and 7 rows in T2 then the Cross join output will give us 7×7=49 rows in total. The query that we will be using will be:

Select
	T1.ColA,
	T1.ColB,
	T2.ColC,
	T2.ColD
	FROM T1(NOLOCK), T2(NOLOCK) 

ColA ColB ColC ColD
1 AA 1 A
1 BB 1 A
2 CC 1 A
3 DD 1 A
3 EE 1 A
4 FF 1 A
5 GG 1 A
1 AA 1 B
1 BB 1 B
2 CC 1 B
3 DD 1 B
3 EE 1 B
4 FF 1 B
5 GG 1 B
1 AA 2 C
1 BB 2 C
2 CC 2 C
3 DD 2 C
3 EE 2 C
4 FF 2 C
5 GG 2 C
1 AA 3 D
1 BB 3 D
2 CC 3 D
3 DD 3 D
3 EE 3 D
4 FF 3 D
5 GG 3 D
1 AA 4 E
1 BB 4 E
2 CC 4 E
3 DD 4 E
3 EE 4 E
4 FF 4 E
5 GG 4 E
1 AA 4 F
1 BB 4 F
2 CC 4 F
3 DD 4 F
3 EE 4 F
4 FF 4 F
5 GG 4 F
1 AA 6 G
1 BB 6 G
2 CC 6 G
3 DD 6 G
3 EE 6 G
4 FF 6 G
5 GG 6 G

About the author

saurabhr2

1 comment

  1. Tarun

    This was an awesome article. I am new to SQL and was looking for help on the topic. The blog specifically addresses the pain point of join formulation which is not available on any other so-called proficient blogs. Thanks for the write-up. Keep sharing more.

Leave a Reply

Your email address will not be published. Required fields are marked *