6 Replies - 449 Views - Last Post: 26 July 2018 - 11:10 AM

#1 dgupta111   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 66
  • Joined: 28-August 17

Difference between these expressions

Posted 26 July 2018 - 09:22 AM

Hi,

I was reading this: pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/solutions/ans3ed-oddonly.pdf

In the sql exercise: when I execute these statements in mysql i get same result.Can you give a more general example where the results may differ:

select B.id,B from A,B where A.id=B.id; comment - join
+------+------+
| id   | B    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    1 |    3 |
+------+------+

select * from B where B.id in (select id from A )
    -> ;
+------+------+
| id   | B    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    1 |    3 |
+------+------+

select * from B where B.id in (select id from A where A.id=B.id) comment - correlated subquery
    -> ;
+------+------+
| id   | B    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    1 |    3 |
+------+------+

select * from A;
+------+------+
| id   | A    |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+

SELECT * FROM B;
+------+------+
| id   | B    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    1 |    3 |
+------+------+

:code:
Thanks

This post has been edited by modi123_1: 26 July 2018 - 09:24 AM
Reason for edit:: In the future, please highlight the text that is code and click the [code] button in the editor.


Is This A Good Question/Topic? 0
  • +

Replies To: Difference between these expressions

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15362
  • View blog
  • Posts: 61,611
  • Joined: 12-June 08

Re: Difference between these expressions

Posted 26 July 2018 - 09:25 AM

Why would you think executing the same queries would give you different results?
Was This Post Helpful? 0
  • +
  • -

#3 dgupta111   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 66
  • Joined: 28-August 17

Re: Difference between these expressions

Posted 26 July 2018 - 09:29 AM

View Postdgupta111, on 26 July 2018 - 09:22 AM, said:

Hi,

So what is the difference in join and correlated subqueries?Are they using the same query tree?

I was reading this: pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/solutions/ans3ed-oddonly.pdf

In the sql exercise: when I execute these statements in mysql i get same result.Can you give a more general example where the results may differ:

select B.id,B from A,B where A.id=B.id; comment - join
+------+------+
| id   | B    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    1 |    3 |
+------+------+

select * from B where B.id in (select id from A )
    -> ;
+------+------+
| id   | B    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    1 |    3 |
+------+------+

select * from B where B.id in (select id from A where A.id=B.id) comment - correlated subquery
    -> ;
+------+------+
| id   | B    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    1 |    3 |
+------+------+

select * from A;
+------+------+
| id   | A    |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+

SELECT * FROM B;
+------+------+
| id   | B    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    1 |    3 |
+------+------+

:code:/>
Thanks

Was This Post Helpful? 0
  • +
  • -

#4 dgupta111   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 66
  • Joined: 28-August 17

Re: Difference between these expressions

Posted 26 July 2018 - 10:02 AM

Can you give a more general example where the results may differ: expression 2 and 3

This post has been edited by dgupta111: 26 July 2018 - 10:05 AM

Was This Post Helpful? 0
  • +
  • -

#5 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3001
  • View blog
  • Posts: 11,551
  • Joined: 03-December 12

Re: Difference between these expressions

Posted 26 July 2018 - 10:51 AM

You will get the same results, because you are asking the same thing each time, just differently.

Can you see where each draw the same conclusions?
Was This Post Helpful? 0
  • +
  • -

#6 dgupta111   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 66
  • Joined: 28-August 17

Re: Difference between these expressions

Posted 26 July 2018 - 11:06 AM

I heard that correlated queries are unoptimized as compared to joins.So I wanted to look at their query trees for comparison of perfomance.

View Postastonecipher, on 26 July 2018 - 10:51 AM, said:

You will get the same results, because you are asking the same thing each time, just differently.

Can you see where each draw the same conclusions?

Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15362
  • View blog
  • Posts: 61,611
  • Joined: 12-June 08

Re: Difference between these expressions

Posted 26 July 2018 - 11:10 AM

Check out the execution plans.
https://dev.mysql.co...ce-explain.html
https://www.sitepoin...-mysql-queries/
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1