CODEWITHSUNDEEP
phpmysqlsqlleft-join
Rashid Farooq
Rashid Farooq

Reputation: 365

Effect of LEFT JOIN SQL Statement on Performance

As We Know that LEFT JOIN SQL Statement gives All the Values from the LEFT table and the Joint values through an id (Foreign Key) from the Right Table. For Example I have the following table

comments
    comment_id
    parent_id
    comment_body

This table has the following data

-----------|-----------|-------------------|
comment_id | parent_id | comment_body      |
-----------|-----------|-------------------|
1          | NULL      | parent comment    | 
-----------|-----------|-------------------|
2          | 1         | child comment     | 
-----------|-----------|-------------------|
3          | NULL      | parent comment    | 
-----------|-----------|-------------------|
4          | 1         | child comment     | 
-----------|-----------|-------------------|
5          | 3         | child comment     | 
-----------|-----------|-------------------|
6          | 1         | parent comment    | 
-----------|-----------|-------------------|
7          | NULL      | parent comment    | 
-----------|-----------|-------------------|
8          | 1         | child comment     | 
-----------|-----------|-------------------|
9          | 7         | child comment     | 
-----------|-----------|-------------------|
10         | 3         | child comment     | 
-----------|-----------|-------------------|

for example i run the following query at the above table

SELECT c1 . * , c2 . * 
FROM comments c1
LEFT JOIN comments c2 ON c2.parent_id = c1.comment_id
WHERE c1.parent_id IS NULL

It will give the following output

-----------|-----------|---------------|------------|-----------|---------------|
comment_id | parent_id | comment_body  | comment_id | parent_id | comment_body  |
-----------|-----------|---------------|------------|-----------|---------------|
1          | NULL      |parent comment | 1          | 1         | child comment |
-----------|-----------|---------------|------------|-----------|---------------|
1          | NULL      |parent comment | 4          | 1         | child comment |
-----------|-----------|---------------|------------|-----------|---------------|
1          | NULL      |parent comment | 6          | 1         | child comment |
-----------|-----------|---------------|------------|-----------|---------------|
1          | NULL      |parent comment | 8          | 1         | child comment |
-----------|-----------|---------------|------------|-----------|---------------|
3          | NULL      |parent comment | 5          | 3         | child comment |
-----------|-----------|---------------|------------|-----------|---------------|
3          | NULL      |parent comment | 10         | 3         | child comment |
-----------|-----------|---------------|------------|-----------|---------------|
7          | NULL      |parent comment | 9          | 7         | child comment |
-----------|-----------|---------------|------------|-----------|---------------|

Here I have Highlighted the Values, That are extra and I don't need them.

enter image description here

And in my opinion these extra values could be performance effective. Can I get the output in the following format

enter image description here

If it is not possible, Please guide me that what are the pros and cons of my above mentioned query?, and should I use it or not?

Upvotes: 1

Views: 964

Answers (3)

Aivar
Aivar

Reputation: 2029

You can allso use some more subqueries example:

SELECT 
    IF(c2.comment_id = c4.first_child, c1.comment_id, NULL) AS comment_id,
    IF(c2.comment_id = c4.first_child, c1.parent_id, NULL) AS parent_id,
    IF(c2.comment_id = c4.first_child, c1.comment_body, NULL) AS comment_body,
   c2.*
FROM comments c1
LEFT JOIN comments c2 ON c2.parent_id = c1.comment_id
LEFT JOIN ( select c3.parent_id, MIN(c3.comment_id) AS first_child from comments as c3 WHERE c3.parent_id IS NOT NULL GROUP BY c3.parent_id ) as c4 ON (c4.parent_id = c2.parent_id)
WHERE c1.parent_id IS NULL;

Example output:

comment_id | parent_id | comment_body   | comment_id | parent_id | comment_body
1          | NULL      | Parent Comment | 2          | 1         | Child Comment
NULL       | NULL      | NULL           | 4          | 1         | Child Comment
NULL       | NULL      | NULL           | 6          | 1         | Child Comment
NULL       | NULL      | NULL           | 8          | 1         | Child Comment
3          | NULL      | Parent Comment | 5          | 3         | Child Comment
NULL       | NULL      | NULL           | 10         | 3         | Child Comment
7          | NULL      | Parent Comment | 9          | 7         | Child Comment

Of course if you want performance, then such things will not help you. It will not make your query quicker.

As you have allso marked Tag PHP, and want to run quick queries with low cpu/memory usage and allso with using less bandwith (if mysql server is not in localhost). Then probably most wisest thing to do is make several queries And only ask what you really need from mysql, if tables are bigger. Probably you do not want to get all parents and child comments at once. And what will you do when your child comment is allso parent for some other comment ;-)

Upvotes: 0

user1613212
user1613212

Reputation: 93

I think u possibly need to check the below link for ur reference. It may help you..

How do you suppress or hide duplicate values in SQL?

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can not do this. But here is an alternate

SELECT 
    c1 . * , 
    GROUP_CONCAT(c2.comment_id) AS comment_ids,
    GROUP_CONCAT(c2.parent_id) AS parent_ids,
    GROUP_CONCAT(c2.comment_body) AS comment_bodies
FROM comments c1
LEFT JOIN comments c2 ON c2.parent_id = c1.comment_id
WHERE c1.parent_id IS NULL

This will give you output in this form

-----------|-----------|---------------|------------|-----------|-----------------------------------------------------------|
comment_id | parent_id | comment_body  | comment_id | parent_id | comment_body                                              |
-----------|-----------|---------------|------------|-----------|-----------------------------------------------------------|
1          | NULL      |parent comment | 1,4,6,8    | 1,1,1,1   | child comment , child comment ,child comment,child comment|
-----------|-----------|---------------|------------|-----------|-----------------------------------------------------------|
3          | NULL      |parent comment | 5,10       | 3,3       | child comment ,child comment                              |
-----------|-----------|---------------|------------|-----------|-----------------------------------------------------------|
7          | NULL      |parent comment | 9          | 7         | child comment                                             |
-----------|-----------|---------------|------------|-----------|-----------------------------------------------------------|

You can explode the columnm with php explode function to get comma seperated values transformed into an array.

MySQL GROUP_CONCAT

Upvotes: 1

Related Questions