Where in this sql Fifddle example does the "a." character keep coming from? Its not in the table at all

Question

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) unsigned NOT NULL,
  `rev` int(3) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`,`rev`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
  ('1', '1', 'The earth is flat'),
  ('2', '1', 'One hundred angels can dance on the head of a pin'),
  ('1', '2', 'The earth is flat and rests on a bull\'s horn'),
  ('1', '3', 'The earth is like a ball.');

/////////////////////////////////////////////////////////

Then the actual Query:

SELECT a.id, a.rev, a.content
FROM `docs` a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM `docs`
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev;


SELECT a.*
FROM `docs` a
LEFT OUTER JOIN `docs` b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

a is some kind of placeholder?
Where would one go to find an explaination of this IN ENGLISH?
I'm hoping someone would walk us through this and make it understandable. Thank you!

Another question. Join WHAT? I only see ONE database and ONE table. What is there to join? — GaryM, Mar 01 '19 at 01:46
Take a look at [this](https://www.w3schools.com/sql/sql_alias.asp) or [this](http://www.mysqltutorial.org/mysql-alias/) — Nick, Mar 01 '19 at 02:24

Paul Maxwell · Answer 1 · 2019-03-01T03:33:48.223

"a" is a table alias:

SELECT a.id, a.rev, a.content
FROM `docs` a    # HERE, on this line, the table is given an alias of a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM `docs`
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev;

That alias is declared immediately after the table name in the from clause (as indicated above).

Aliases are used to abbreviate and/or to clarify a query. There are several places where one can declare an alias e.g.

#table alias
from tablex as x

#derived table (subquery) alias
select d.* from (select * from t where col1 = 'xyz') as d

#column alias
select col1 as xyz
from tablex as x

Note the "as" seen above is optional in most databases, Oracle does not permit use of "as" when declaring table or subquery aliases.

SELF JOINS

Table aliases are VITAL when you join a table to itself

select
     t1.id as t1_id
   , t2.id as t2_id
from tablex as t1
inner join tablex as t2 on t1.fk = t2.id

Without those aliases t1 and t2 that query simply could not work.

EDIT

Regrettably all too often we see alias where the aliases are defined "in order" within the query. In your example the first is "a" and the second is "b" and so on.

That is poor practice. A much more meaningful way to use aliases is to use "first letters" of each word within a table's name or to assign some meaning to a subquery.

In the example I would suggest "d" (for docs) and "mr" (for max(rev))

SELECT d.id, d.rev, d.content, mr.rev
FROM `docs` as d 
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM `docs`
    GROUP BY id
) as mr ON d.id = mr.id AND d.rev = mr.rev;

I'm sorry, you're joining a table with ITSELF? What would be a reason for doing that? In the example there is one table. I presume Joins somehow optimize a query (does it?) and if thats the cae what is to be gained by taking the extra step? — GaryM, Mar 01 '19 at 03:17
There are at least million reasons for this, see https://stackoverflow.com/questions/3362038/what-is-self-join-and-when-would-you-use-it **But for your original question** "why" does not matter. Aliases provide the "how" — Paul Maxwell, Mar 01 '19 at 03:23

Where in this sql Fifddle example does the "a." character keep coming from? Its not in the table at all

1 Answers1