Query to compare two subsets of data from the same table?

My problem:

I'm trying to compare to subsets of data within one table, and I've got two methods that partially work and one certainty that there's got to be a more correct way to do it.

The idea here is a table which contains datasets about the same systems over time, and I would like to compare them and especially to see when there are introductions or absences. Allow me to demonstrate with a simple test table:

mysql> select * from gocore;
+-----+------------+------+----------+----------+
| uid | server     | tag  | software | revision |
+-----+------------+------+----------+----------+
|   1 | enterprise | old  | apache   | 2.2.25   |
|   2 | enterprise | new  | apache   | 2.4.6    |
|   3 | enterprise | new  | tomcat   | 7.0.42   |
|   4 | enterprise | old  | geronimo | 2.1.7    |
+-----+------------+------+----------+----------+

In this example there are two datasets - the tag "old" dataset, and the tag "new" dataset. Each reflects a data sample taken at some point in time. For server "enterprise", we have one software package that changed over time (apache), one software package that was introduced (tomcat), and one software package that became absent (geronimo).

My goal: A query that will allow me to summarize the state between "old" and "new":

+------------+----------+----------+----------+
| server     | software | revision | revision |
+------------+----------+----------+----------+
| enterprise | apache   | 2.2.25   | 2.4.6    |
| enterprise | geronimo | 2.1.7    | NULL     |
| enterprise | tomcat   | NULL     | 7.0.42   |
+------------+----------+----------+----------+

It is important for my purposes to be able to see the 'NULL' cells above - I need to know when software has been added or removed from the system. TO BE CLEAR, the table above is not the result of a query - it's me using a text editor to fix up what I was getting to describe what I'm looking for. I need your help to figure out the query that would make that table

I think you have to hack it a bit with a derived table, AKA an implicit temporary table, AKA a "subquery in the from clause."

We derive a table we'll call t containing each distinct (server,software) from gocore, then left join to gocore twice, once on tag = 'old' and once on tag = 'new'.

SELECT t.server, t.software, o.revision AS old_rev, n.revision AS new_rev
  FROM (SELECT DISTINCT server, software FROM gocore) t
  LEFT JOIN gocore o ON o.server = t.server AND o.software = t.software AND o.tag = 'old'
  LEFT JOIN gocore n ON n.server = t.server AND n.software = t.software AND n.tag = 'new';

StackExchange Database Administrators Q#50773, answer score: 9