StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>MySQL allows this non-standard SQL syntax because there is at least one specific case in which it makes the SQL nominally easier to write. That case is when you're joining two tables which have a PRIMARY / FOREIGN KEY relationship (whether enforced by the database or not) and you want an aggregate value from the FOREIGN KEY side and multiple columns from the PRIMARY KEY side.</p> <p>Consider a system with <code>Customer</code> and <code>Orders</code> tables. Imagine you want <em>all</em> the fields from the customer table along with the total of the <code>Amount</code> field from the <code>Orders</code> table. In standard SQL you would write:</p> <pre><code> SELECT C.CustomerID, C.FirstName, C.LastName, C.Address, C.City, C.State, C.Zip, SUM(O.Amount) FROM Customer C INNER JOIN Orders O ON C.CustomerID = O.CustomerID GROUP BY C.CustomerID, C.FirstName, C.LastName, C.Address, C.City, C.State, C.Zip </code></pre> <p>Notice the unwieldy GROUP BY clause, and imagine what it would look like if there were more columns you wanted from customer.</p> <p>In MySQL, you could write:</p> <pre><code> SELECT C.CustomerID, C.FirstName, C.LastName, C.Address, C.City, C.State, C.Zip, SUM(O.Amount) FROM Customer C INNER JOIN Orders O ON C.CustomerID = O.CustomerID GROUP BY C.CustomerID </code></pre> <p>or even (I think, I haven't tried it):</p> <pre><code> SELECT C.*, SUM(O.Amount) FROM Customer C INNER JOIN Orders O ON C.CustomerID = O.CustomerID GROUP BY C.CustomerID </code></pre> <p>Much easier to write. In <em>this particular case</em> it's safe as well, since you know that only one row from the <code>Customer</code> table will contribute to each group (assuming CustomerID is PRIMARY or UNIQUE KEY).</p> <p>Personally, I'm not a big fan of this exception to standard SQL syntax (since there are many cases where it's <em>not</em> safe to use this syntax and rely on getting values from any particular row in the group), but I can see where it makes certain kinds of queries easier and (in the case of my second MySQL example) possible.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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