Number of rows in the result
Question
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
It will take less than 1 minute to register for lifetime. Bonus Tip - We don't send OTP to your email id Make Sure to use your own email id for free books and giveaways
Answers ( 4 )
Joining the 2 tables based on their values (A_values & B_values) and counting number of rows:
Tables: A & B. Columns: A_values & B_values
Inner: (Selects all the values common to both the tables)
SELECT COUNT(*) FROM A INNER JOIN B
ON A.A_values= B.B_values
Left: (Selects all values from A: Intersection values plus only A)
SELECT COUNT(*) FROM A
LEFT JOIN B
ON A.A_values= B.B_values
RIGHT: (Selects all values from B: Intersection values plus only B)
SELECT COUNT(*) FROM A RIGHT JOIN B
ON A.A_values= B.B_values
CROSS: In cross join, each value of the first table gets matched with each value of the second table, like a cartesian product. All types of possible combinations are derived from cross join.
SELECT COUNT(*) FROM A CROSS JOIN B
A – 1,2,3,4,5
B – 1,2,3,Null,Null
Left JOIN-1,2,3,4,5
RIGHT Join-1,2,3,Null,Null
CROSS Join
1,1
1,2
1,3
1,Null
1,Null
2,1
2,2
2,3
2,Null
2,Null
3,1
3,2
3,3
3,Null
3,Null
4,1
4,2
4,3
4,Null
4,Null
5,1
5,2
5,3
5,Null
5,Null
INNER JOIN
1,2,3
There are two tables A,B with 5 rows
A – 1,2,3,4,5
B – 1,2,3,Null,Null
The output when you do-
-Left – 5 rows – all of Table A
-Right – 5 rows – all of Table 5
-Cross – 25 rows – all rows of Table A mapped with all rows of Table B. Null doesn’t affect results.
-Inner Join – 3 rows (1,2,3) of Table A and Table B
There are two tables A,B with 5 rows
A – 1,2,3,4,5
B – 1,2,3,Null,Null
What will be the output when you do
-Left Join : So according to the definition of the left join is that it will return all record form the left table &
matched record from the Right table. All 5 rows of Table A where as 3 matching rows of Table B.
-Right Join : So according to the definition of the right join is that it will return all record from the right table
& matched record from the Left Table. All 5 rows of Table B along with the nulls where as 3 matching rows of
Table A.
-Cross Join : For Cross Join it creates all paired combinations of the rows of the table that will be joined i.e., M*N (
Considering that M & N are rows of the given tables ). All total of 25 records will be shown along with the nulls.
-Inner Join : For inner join, so according to the definition it will return all the records that have matched in both the sides.
All the matching values in both the table will be shown i.e., first 3 matching rows from Table A and Table B.