CODEWITHSUNDEEP
mysqlsqljoinleft-join
user11652666
user11652666

Reputation:

MySQL - Difference between two statements

I noticed this SQL query in a documentation:

UPDATE pages page
LEFT JOIN pages parentpage ON page.pid = parentpage.uid
LEFT JOIN pages grandparentpage ON parentpage.pid = grandparentpage.uid
LEFT JOIN pages grandgrandparentpage ON grandparentpage.pid = grandgrandparentpage.uid
SET page.is_hidden = 0
WHERE page.uid = 5
OR page.pid = 5
OR parentpage.pid = 5
OR grandparentpage.pid = 5
OR grandgrandparentpage.pid = 5;

Isn't this exactly the same as:

UPDATE pages page
SET page.is_hidden = 0
WHERE page.uid = 5
OR page.pid = 5;

PID and UID are filtered by the where clause to equal 5. Therefore I don't see, why this JOINs would be important.

But maybe I am missing the point?

Any help is appreciated.

Thanks in advance.

Upvotes: 0

Views: 86

Answers (2)

gaborsch
gaborsch

Reputation: 15758

They are different, because the first query updates all children, grandchildren and grandgrandchildren rows of a pid=5 or uid=5 row. The second will update only the rows themselves.

Here's an example how the queries are different:

|uid|pid| first query                 | second query     |
| 1 | 5 | Yes, page.pid=5             | Yes. page.pid=5  |
| 5 | 6 | Yes, page.uid=5             | Yes. page.uid=5  |
| 6 | 7 | Yes, parentpage.pid=5       | No               |
| 7 | 8 | Yes, grapdppage.pid=5       | No               |
| 8 | 9 | Yes, grandgrandppage.pid=5  | No               |
| 9 | 10| No                          | No               |

Upvotes: 0

Booboo
Booboo

Reputation: 44043

You are missing something. See db-fiddle. The first SQL will up date not only the row with uid 5 but also its child, grandchild and great-grandchild if they exist.

Upvotes: 2

Related Questions