Question

1. Using the below two SQL queries and comments below, provide explanation on why the output count varies and which SQL yield correct result.

(20 points)

• Both SQLs are joining two tables
• Both SQLs are syntactically correct
• Query #1 returns about 19614 rows versus Query #2 returns 3550134.

Query #1:

select * from person.Address addr inner join person.StateProvince prov on addr.StateProvinceID = prov.StateProvinceID

Query #2:

select * from person.Address addr,person.StateProvince prov

Answer 1

SOLUTION:

QUERY 1 will yield the correct Result

EXPLANATION OF OUTPUT OF QUERY 1 :

let us take two tables 1. person. Address with two attribute one is StateProvinceID on which the inner join condition is applied and one dummy attribute 2.person.StateProvince

1. person.Address

StateProvinceID	address
10001	near Bangalore
10002	Chennai
10003	Hyderabad
10004	Pune

2.person.StateProvince : Take stateProvinceID and one Dummy attribute

StateProvinceID	state
10001	Andhra
10002	Telangana
10003	Gujarat

NOW APPLY THE QUERY1 GIVEN

select * from person.Address addr inner join person.StateProvince prov on addr.StateProvinceID = prov.StateProvinceID

This will join the rows of table1 to rows of the table only if the value of the stateProvinceID is matched like

OUTPUT OF THE ABOVE QUERY TO THE GIVEN DATA

1->10001 of Table 1 matched to 10001 of Table2

2->10002 of Table 1 is matched to 10002 of Table 2

3->10003 of Table 1 is matched to 10003 of Table 2

for 10004 there is no matching in Table 2 so it will not be retrieved.

No other row will be returned since there are not matchings for that rows in Table2

SO we have only 3 Rows as output

person.Address person.StateProvince Table Data

stateProvinceID	address	stateProvienceID	state
10001	near Bangalore	10001	Andhra
10002	Chennai	10002	Telangana
10003	Hyderabad	10003	Gujarat

QUERY 2:

select * from person.Address addr,person.StateProvince prov

EXPLANATION OF OUTPUT OF QUERY 2:

This is just a cross Join where we haven't applied on with any condition

We are just doing multiplication here where rows of Table are multiplied with Table 2.

OUTPUT OF THIS STATEMENT WILL BE LIKE THIS

1-> 10001 -> It will be matched with 10001
-> It will be matched with 10002
       -> It will be matched with 10003
2-> 10002 -> It will be matched with 10001
-> It will be matched with 10002
       -> It will be matched with 10003
3-> 10003 -> It will be matched with 10001
-> It will be matched with 10002
       -> It will be matched with 10003
4-> 10004 -> It will be matched with 10001
-> It will be matched with 10002
       -> It will be matched with 10003

->Thus here we will be having N*M rows where N is Table1 Rows and M is Table 2 Rows
->This is doesn't even give us an certain output, there is no need for us to join two tables without any reason, This is
not giving any information

-> If we join two tables means that we are expecting the matching data of those two tables where we can get more details
not just like multiplying all.

OUTPUT IS LIKE THIS :

address and state of the corresponding fields will be filled with that data i am concerned about the rows i just showed the result of the stateProvinceID field of two table.

stateProvinceID	address	stateProvienceID	state
10001		10001
10001		10002
10001		10003
10002		10001
10002		10002
10002		10003
10003		10001
10003		10002
10003		10003
10004		10001
10004		10002
10004		10003