Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here's what's going on. Consider the query piece by piece and what MySQL is processing as it goes.</p> <p>First, you're selecting from items (<code>select i.item_id, i.item_name, avg(ir.rating) from Items i</code>):</p> <pre><code>+---------+-----------+ | item_id | item_name | +---------+-----------+ | 1 | Item 1 | | 2 | Item 2 | | 3 | Item 3 | | 4 | Item 4 | | 5 | Item 5 | +---------+-----------+ </code></pre> <p>Then you are left joining the ratings (<code>left join ItemRating ir ON ir.item_id = i.item_id</code>). Note that <em>Item 1</em> appears in two rows after the join, because that's how <em>JOIN</em> is defined to work -- it returns one row for every join condition match (and the <em>LEFT</em> basically means "return every row in the first table at least once even if there are no join condition matches on that row").</p> <pre><code>+---------+-----------+-----------+------------+ | item_id | item_name | ir.rating | ir.item_id | +---------+-----------+-----------+------------+ | 1 | Item 1 | 9 | 1 | | 1 | Item 1 | 6 | 1 | | 2 | Item 2 | NULL | NULL | | 3 | Item 3 | 10 | 3 | | 4 | Item 4 | NULL | NULL | | 5 | Item 5 | NULL | NULL | +---------+-----------+-----------+------------+ </code></pre> <p>Finally, you are grouping by rating (<code>group by ir.item_id</code>). This will return one row for each unique ir.item_id. There are three unique ir.item_ids (as you can see in the last column there): <code>1</code>, and <code>NULL</code>, and <code>3</code>. For each of these, it returns one row and averages the rating.</p> <p>So, for <code>1</code> we have:</p> <pre><code>+---------+-----------+-----------+------------+ | item_id | item_name | ir.rating | ir.item_id | +---------+-----------+-----------+------------+ | 1 | Item 1 | 9 | 1 | | 1 | Item 1 | 6 | 1 | +---------+-----------+-----------+------------+ </code></pre> <p>Which collapses into:</p> <pre><code>+---------+-----------+----------------+------------+ | item_id | item_name | avg(ir.rating) | ir.item_id | +---------+-----------+----------------+------------+ | 1 | Item 1 | 7.5 | 1 | +---------+-----------+----------------+------------+ </code></pre> <p>For <code>NULL</code> we have:</p> <pre><code>+---------+-----------+-----------+------------+ | item_id | item_name | ir.rating | ir.item_id | +---------+-----------+-----------+------------+ | 2 | Item 2 | NULL | NULL | | 4 | Item 4 | NULL | NULL | | 5 | Item 5 | NULL | NULL | +---------+-----------+-----------+------------+ </code></pre> <p>Which collapses into:</p> <pre><code>+---------+-----------+----------------+------------+ | item_id | item_name | avg(ir.rating) | ir.item_id | +---------+-----------+----------------+------------+ | 2| Item 2 | NULL | NULL | +---------+-----------+----------------+------------+ </code></pre> <p>For <code>3</code> we have:</p> <pre><code>+---------+-----------+-----------+------------+ | item_id | item_name | ir.rating | ir.item_id | +---------+-----------+-----------+------------+ | 3 | Item 3 | 10 | 3 | +---------+-----------+-----------+------------+ </code></pre> <p>Which collapses into:</p> <pre><code>+---------+-----------+----------------+------------+ | item_id | item_name | avg(ir.rating) | ir.item_id | +---------+-----------+----------------+------------+ | 3 | Item 3 | 10 | 3 | +---------+-----------+----------------+------------+ </code></pre> <p>Combining the three collapsed results gives:</p> <pre><code>+---------+-----------+----------------+------------+ | item_id | item_name | avg(ir.rating) | ir.item_id | +---------+-----------+----------------+------------+ | 1 | Item 1 | 7.5 | 1 | | 3 | Item 3 | 10 | 3 | | 2 | Item 2 | NULL | NULL | +---------+-----------+----------------+------------+ </code></pre> <p>Which is what you got.</p> <p>The one tricky part is the way the NULL rows collapsed. Recall, these were the null rows:</p> <pre><code>+---------+-----------+-----------+------------+ | item_id | item_name | ir.rating | ir.item_id | +---------+-----------+-----------+------------+ | 2 | Item 2 | NULL | NULL | | 4 | Item 4 | NULL | NULL | | 5 | Item 5 | NULL | NULL | +---------+-----------+-----------+------------+ </code></pre> <p>When you do a group by, most database systems will not even let you select columns that are not part of the group. MySQL is an exception. Since you are only grouping on ir.rating, that's the only one most would let you select, because there is no clear way to collapse three rows in a non-aggregate way. What MySQL does is just choose the first one it encounters and use the values in that row as the collapsed value. So (2,4,5) => (2) and (Item 2, Item 4, Item 5) => Item 2 and (NULL, NULL, NULL) => NULL. That's why you only see row 2 (you are actually seeing three collapsed rows that look like row 2).</p> <p>To really see this in action and drive the point home, consider this query:</p> <pre><code>select group_concat(i.item_id), group_concat(i.item_name), avg(ir.rating) from Items i left join ItemRating ir ON ir.item_id = i.item_id group by ir.item_id; </code></pre> <p>This is just like your original query except all three selected columns now have group aggregate functions. I am using <code>GROUP_CONCAT</code>, which just concatenates strings to form the collapsed version (this would be valid in other SQL systems besides MySQL). That returns this:</p> <pre><code>+-------------------------+---------------------------+----------------+ | group_concat(i.item_id) | group_concat(i.item_name) | avg(ir.rating) | +-------------------------+---------------------------+----------------+ | 2,4,5 | Item 2,Item 4,Item 5 | NULL | | 1,1 | Item 1,Item 1 | 7.5000 | | 3 | Item 3 | 10.0000 | +-------------------------+---------------------------+----------------+ </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POWhat's happening in this MYSQL query with a left join and a group by (on the wrong column)?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBen Lee
    1. USBen Lee
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhat's happening in this MYSQL query with a left join and a group by (on the wrong column)?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you for the very detailed explanation! This makes total sense, I don't know why I wasn't seeing it. Sometimes you just need someone to point out the obvious. :)
      singulars
      1. PO
      1. USJohn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload