Hey guys, I'm Venkat and in this video, we'll understand how an inner join, left join, right
join, full join and even cross join returns the same row count. We have two tables here - TableA
and TableB. As you can see, both the tables have just one column each. TableA has two rows and
TableB has three rows. To join both these tables, we are using ColumnA in TableA and ColumnB in
TableB. So, basically joining ColumnA in TableA with ColumnB in TableB. Now, here is the question
asked in the interview. Actually one of our Youtube channel subscribers faced this question in
an interview and here's the question. However we join these two tables inner join, left outer join,
right outer join, full outer join or even cross join, we get the same number of rows as the
result, that is six rows. How is this possible? Can you please explain?This is the question.
Let's look at this in action. In the interest of time, I have the create script ready. So,
let's execute it. There we go, both the tables are created. Now, let's populate both the tables with
test data. Again, in the interest of time I have the insert script ready. So, let's execute it.
Let's select the data to make sure we have everything as we want it to be. There we go,
as expected we have two rows in TableA and three rows in TableB. Now, let's try, inner join. Select
ColumnA, ColumnB from TableA inner join TableB on, we want to join ColumnA in TableA with ColumnB in
TableB. Execute the query. There we go. We have six rows. Now, let's try left outer join. Make a
copy of the query. Change inner join to left outer join. Execute the query. Again, we have the same
number of rows - 6. Now, in the interest of time, I already have queries for right outer join,
full outer join, and cross join. So, let me paste those three queries here.
Now, let's execute all the queries. There we go. All the five queries executed successfully.
Flip over to the "messages" tab. Notice, all the 5 queries returned the same row count - 6 rows. Now,
let's understand what's going on? How come all the different types of joins return the same count of
rows? The answer, is actually very simple. If you take a look at the data in both the tables, every
row in TableA matches with every row in TableB. So, what we get back is a cartesian product, that
is the number of rows in TableA multiplied by the number of rows in TableB. So, in essence, what we
have here is a cross join. TableA has 2 rows and Tableb 3 rows. Every row in TableA matches with
every row in TableB. So, irrespective of the type of join, we get the cartesian product 6, that is
2 rows in TableA multiplied by 3 rows in TableB. Now, what do you think is the result going to be,
if we add 1 more row with a value of 1 to TableB. Want to take a guess ?Pause the video right now
and give it a shot. What's your answer ? Is it 8 ? If so, you are correct, but
how ? Well, the same logic - cartesian product. 2 rows in TableA multiplied by 4 rows in TableB.
So, the answer is 8. Let's actually prove it. Execute the insert query. Now, let's execute
all the 5 select queries. Click on the "messages" tab. There we go. 8 rows returned by each query.
Are you still confused ? Let me explain with another example. Drop both tables that is TableA
and TableA. Let's recreate them. Notice the two create table queries, they are the same as before,
except we now have a second column called "SomeValue" in both the tables.
Let's insert some data. Notice, even the insert queries are the same as before.
The only difference is, we are now supplying value for the second column "SomeValue".
Now, the select queries. In addition to ColumnA and ColumnB, we also want to select "SomeValue"
from TableA. Let's give it an alias [TableASomeValue] Similarly,
"SomeValue" column from TableB as well. Let's call it [TableBSomeValue]. Let's include the same
"select list" on the rest of the 4 queries that is left outer join, right outer join, full outer join
and cross join. Execute all the queries. Notice the output. It takes the first row in TableA that
is the row which has the value A1 and returns every row in TableB. So we have A1 B1, A1 B2, A1
B3. The same happens even with the second row in TableA. So, we have A2 B1, A2 B2, A2 B3. Hope this
explains, why we have the same number of rows for every join type. If you're new to joins, please
check out our videos on SQL Server Joins. I will include the video link as well as all the queries
we used in this demo in the description of this video. This is Venkat. Thank you for watching.