Database Reference
In-Depth Information
To make the meaning of a statement clearer to human readers, it's often useful to qualify
column names even when that's not strictly necessary as far as MySQL is concerned. I
tend to use qualified names in join examples for that reason.
To avoid writing complete table names when qualifying column references, give each
table a short alias and refer to its columns using the alias. The following two statements
are equivalent:
SELECT
artist
.
name
,
painting
.
title
,
states
.
name
,
painting
.
price
FROM
artist
INNER
JOIN
painting
INNER
JOIN
states
ON
artist
.
a_id
=
painting
.
a_id
AND
painting
.
state
=
states
.
abbrev
;
SELECT
a
.
name
,
p
.
title
,
s
.
name
,
p
.
price
FROM
artist
AS
a
INNER
JOIN
painting
AS
p
INNER
JOIN
states
AS
s
ON
a
.
a_id
=
p
.
a_id
AND
p
.
state
=
s
.
abbrev
;
In
AS
alias_name
clauses, the
AS
is optional.
For complicated statements that select many columns, aliases can save a lot of typing.
In addition, for some types of statements, aliases are not only convenient but necessary,
as will become evident when we get to the topic of self-joins (see
Recipe 14.4
).
Joining Tables from Different Databases
To perform a join between tables located in different databases, qualify table and column
names sufficiently that MySQL knows what you're referring to. Thus far, we have used
the
artist
and
painting
tables with the implicit understanding that both are in the
cookbook
database, so we can simply refer to the tables without specifying any database
name when
cookbook
is the default database. For example, the following statement uses
the two tables to associate artists with their paintings:
SELECT
artist
.
name
,
painting
.
title
FROM
artist
INNER
JOIN
painting
ON
artist
.
a_id
=
painting
.
a_id
;
But suppose instead that
artist
is in the
db1
database and
painting
is in the
db2
database. To indicate this, qualify each table name with a prefix that specifies which
database it's in. The fully qualified form of the join looks like this:
SELECT
db1
.
artist
.
name
,
db2
.
painting
.
title
FROM
db1
.
artist
INNER
JOIN
db2
.
painting
ON
db1
.
artist
.
a_id
=
db2
.
painting
.
a_id
;
Table aliases can simplify that considerably:
SELECT
a
.
name
,
p
.
title
FROM
db1
.
artist
AS
a
INNER
JOIN
db2
.
painting
AS
p
ON
a
.
a_id
=
p
.
a_id
;
If there is no default database, or it is neither
db1
nor
db2
, it's necessary to fully qualify
both table names. If the default database is either
db1
or
db2
, you can dispense with the
