UNIX and Linux Applications

Hi there, I have a query that works just fine, but im having a little problem understanding how it works and have been struggling to find any docs that explain the techniques that are being used. If anybody out there could explain how this is put together then that would be great

I have three tables

admin

mac | boxstatus
11:22:33 | ON
AA:BB:CC | OFF

physical_hosts

mac | hostname | firmware
11:22:33 | server1 | 1.34
AA:BB:CC | server2 | 1.45

virtual_hosts

mac | virtual_name | status
11:22:33 | VM-1 | RUNNING
11:22:33 | VM-3 | RUNNING
AA:BB:CC | VM-2 | CONFIGURED

The following query outputs a combination of the physical and virtual hosts inserting a "-" into the firmware column as this doesn't apply to the virtual hosts. NOTE: that the status of the physical hosts is stored in the admin table but for virtual hosts is a column within its own table

output

mac | hostname | firmware | status
11:22:33 | server1 | 1.34 | ON
11:22:33 | VM-1 | - | RUNNING
11:22:33 | VM-3 | - | RUNNING
AA:BB:CC | server2 | 1.45 | OFF
AA:BB:CC | VM-2 | - | CONFIGURED

this is the query that creates the above output

Code:

SELECT
`A`.`mac` AS `mac`,
`A`.`hostname` AS `hostname`,
`A`.`firmware` AS `firmware`,
`T`.`boxstatus` AS `status` FROM (`MYDB`.`physical_hosts` `A` join `MYDB`.`admin` `T` 
ON((`A`.`mac` = `T`.`mac`))) UNION ALL SELECT 
`virtual_hosts`.`mac` AS `mac`,
`virtual_hosts`.virtual_name` AS `virtual_name`,
'-' AS `-` 
`virtual_hosts`.`status` AS `status` 
FROM `MYDB`.`virtual_hosts` ORDER BY `mac`');

Im really struggling to understand the use of the A and T within this query ? what is this technique called ? i looked at temporary tables and placeholders but they are different things altogether .Also what it is doing in the line with the UNION ALL . any help understanding this would be great

Cheers

A and T are aliases - they are just shorter names for the tables `MYDB`.`physical_hosts` and `MYDB`.`admin` respectively, so you can write:

Code:

`A`.`mac` = `T`.`mac`

Insted of:

Code:

`MYDB`.`physical_hosts`.`mac` = `MYDB`.`physical_hosts`.`mac`

Which is longer and harder to read.

The union all set operator combines the result of two SQL queries including the duplicates.
See Wikipedia for more.