MySQL supports the following JOIN
syntaxes
for the table_references
part of
SELECT
statements and
multiple-table DELETE
and
UPDATE
statements:
table_references:
table_reference
[,table_reference
] ...table_reference
:table_factor
|join_table
table_factor
:tbl_name
[[AS]alias
] [index_hint_list
] |table_subquery
[AS]alias
| (table_references
) | { OJtable_reference
LEFT OUTER JOINtable_reference
ONconditional_expr
}join_table
:table_reference
[INNER | CROSS] JOINtable_factor
[join_condition
] |table_reference
STRAIGHT_JOINtable_factor
|table_reference
STRAIGHT_JOINtable_factor
ONconditional_expr
|table_reference
{LEFT|RIGHT} [OUTER] JOINtable_reference
join_condition
|table_reference
NATURAL [{LEFT|RIGHT} [OUTER]] JOINtable_factor
join_condition
: ONconditional_expr
| USING (column_list
)index_hint_list
:index_hint
[,index_hint
] ...index_hint
: USE {INDEX|KEY} [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list
]) | IGNORE {INDEX|KEY} [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list
) | FORCE {INDEX|KEY} [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list
)index_list
:index_name
[,index_name
] ...
A table reference is also known as a join expression.
The syntax of table_factor
is
extended in comparison with the SQL Standard. The latter accepts
only table_reference
, not a list of
them inside a pair of parentheses.
This is a conservative extension if we consider each comma in a
list of table_reference
items as
equivalent to an inner join. For example:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
is equivalent to:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
In MySQL, CROSS JOIN
is a syntactic
equivalent to INNER JOIN
(they can replace
each other). In standard SQL, they are not equivalent.
INNER JOIN
is used with an
ON
clause, CROSS JOIN
is
used otherwise.
In general, parentheses can be ignored in join expressions containing only inner join operations. MySQL also supports nested joins (see Section 7.2.12, “Nested Join Optimization”).
Index hints can be specified to affect how the MySQL optimizer makes use of indexes. For more information, see Section 12.2.9.2, “Index Hint Syntax”.
The following list describes general factors to take into account when writing joins.
A table reference can be aliased using
or
tbl_name
AS
alias_name
tbl_name alias_name
:
SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
A table_subquery
is also known as
a subquery in the FROM
clause. Such
subqueries must include an alias to
give the subquery result a table name. A trivial example
follows; see also Section 12.2.10.8, “Subqueries in the FROM
clause”.
SELECT * FROM (SELECT 1, 2, 3) AS t1;
INNER JOIN
and ,
(comma) are semantically equivalent in the absence of a join
condition: both produce a Cartesian product between the
specified tables (that is, each and every row in the first
table is joined to each and every row in the second table).
However, the precedence of the comma operator is less than
of INNER JOIN
, CROSS
JOIN
, LEFT JOIN
, and so on. If
you mix comma joins with the other join types when there is
a join condition, an error of the form Unknown
column '
may occur. Information about dealing with
this problem is given later in this section.
col_name
' in 'on
clause'
The conditional_expr
used with
ON
is any conditional expression of the
form that can be used in a WHERE
clause.
Generally, you should use the ON
clause
for conditions that specify how to join tables, and the
WHERE
clause to restrict which rows you
want in the result set.
If there is no matching row for the right table in the
ON
or USING
part in a
LEFT JOIN
, a row with all columns set to
NULL
is used for the right table. You can
use this fact to find rows in a table that have no
counterpart in another table:
SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL;
This example finds all rows in left_tbl
with an id
value that is not present in
right_tbl
(that is, all rows in
left_tbl
with no corresponding row in
right_tbl
). This assumes that
right_tbl.id
is declared NOT
NULL
. See
Section 7.2.10, “LEFT JOIN
and RIGHT JOIN
Optimization”.
The
USING(
clause names a list of columns that must exist in both
tables. If tables column_list
)a
and
b
both contain columns
c1
, c2
, and
c3
, the following join compares
corresponding columns from the two tables:
a LEFT JOIN b USING (c1,c2,c3)
The NATURAL [LEFT] JOIN
of two tables is
defined to be semantically equivalent to an INNER
JOIN
or a LEFT JOIN
with a
USING
clause that names all columns that
exist in both tables.
RIGHT JOIN
works analogously to
LEFT JOIN
. To keep code portable across
databases, it is recommended that you use LEFT
JOIN
instead of RIGHT JOIN
.
The { OJ ... LEFT OUTER JOIN ...}
syntax
shown in the join syntax description exists only for
compatibility with ODBC. The curly braces in the syntax
should be written literally; they are not metasyntax as used
elsewhere in syntax descriptions.
SELECT left_tbl.* FROM { OJ left_tbl LEFT OUTER JOIN right_tbl ON left_tbl.id = right_tbl.id } WHERE right_tbl.id IS NULL;
You can use other types of joins within { OJ ...
}
, such as INNER JOIN
or
RIGHT OUTER JOIN
. This helps with
compatibility with some third-party applications, but is not
official ODBC syntax.
STRAIGHT_JOIN
is similar to
JOIN
, except that the left table is
always read before the right table. This can be used for
those (few) cases for which the join optimizer puts the
tables in the wrong order.
Some join examples:
SELECT * FROM table1, table2; SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 USING (id); SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;
Join Processing Changes in MySQL 5.0.12
Natural joins and joins with USING
,
including outer join variants, are processed according to the
SQL:2003 standard. The goal was to align the syntax and
semantics of MySQL with respect to NATURAL
JOIN
and JOIN ... USING
according
to SQL:2003. However, these changes in join processing can
result in different output columns for some joins. Also, some
queries that appeared to work correctly in older versions
(prior to 5.0.12) must be rewritten to comply with the
standard.
These changes have five main aspects:
The way that MySQL determines the result columns of
NATURAL
or USING
join
operations (and thus the result of the entire
FROM
clause).
Expansion of SELECT *
and SELECT
into a list
of selected columns.
tbl_name
.*
Resolution of column names in NATURAL
or
USING
joins.
Transformation of NATURAL
or
USING
joins into JOIN ...
ON
.
Resolution of column names in the ON
condition of a JOIN ... ON
.
The following list provides more detail about several effects of current join processing versus join processing in older versions. The term “previously” means “prior to MySQL 5.0.12.”
The columns of a NATURAL
join or a
USING
join may be different from
previously. Specifically, redundant output columns no longer
appear, and the order of columns for SELECT
*
expansion may be different from before.
Consider this set of statements:
CREATE TABLE t1 (i INT, j INT); CREATE TABLE t2 (k INT, j INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); SELECT * FROM t1 NATURAL JOIN t2; SELECT * FROM t1 JOIN t2 USING (j);
Previously, the statements produced this output:
+------+------+------+------+ | i | j | k | j | +------+------+------+------+ | 1 | 1 | 1 | 1 | +------+------+------+------+ +------+------+------+------+ | i | j | k | j | +------+------+------+------+ | 1 | 1 | 1 | 1 | +------+------+------+------+
In the first SELECT
statement, column j
appears in both
tables and thus becomes a join column, so, according to
standard SQL, it should appear only once in the output, not
twice. Similarly, in the second SELECT statement, column
j
is named in the
USING
clause and should appear only once
in the output, not twice. But in both cases, the redundant
column is not eliminated. Also, the order of the columns is
not correct according to standard SQL.
Now the statements produce this output:
+------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+ +------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+
The redundant column is eliminated and the column order is correct according to standard SQL:
First, coalesced common columns of the two joined tables, in the order in which they occur in the first table
Second, columns unique to the first table, in order in which they occur in that table
Third, columns unique to the second table, in order in which they occur in that table
The single result column that replaces two common columns is
defined via the coalesce operation. That is, for two
t1.a
and t2.a
the
resulting single join column a
is defined
as a = COALESCE(t1.a, t2.a)
, where:
COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)
If the join operation is any other join, the result columns of the join consists of the concatenation of all columns of the joined tables. This is the same as previously.
A consequence of the definition of coalesced columns is
that, for outer joins, the coalesced column contains the
value of the non-NULL
column if one of
the two columns is always NULL
. If
neither or both columns are NULL
, both
common columns have the same value, so it doesn't matter
which one is chosen as the value of the coalesced column. A
simple way to interpret this is to consider that a coalesced
column of an outer join is represented by the common column
of the inner table of a JOIN
. Suppose
that the tables t1(a,b)
and
t2(a,c)
have the following contents:
t1 t2 ---- ---- 1 x 2 z 2 y 3 w
Then:
mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | x | NULL |
| 2 | y | z |
+------+------+------+
Here column a
contains the values of
t1.a
.
mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+
| a | c | b |
+------+------+------+
| 2 | z | y |
| 3 | w | NULL |
+------+------+------+
Here column a
contains the values of
t2.a
.
Compare these results to the otherwise equivalent queries
with JOIN ... ON
:
mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a | b | a | c |
+------+------+------+------+
| 1 | x | NULL | NULL |
| 2 | y | 2 | z |
+------+------+------+------+
mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a | b | a | c |
+------+------+------+------+
| 2 | y | 2 | z |
| NULL | NULL | 3 | w |
+------+------+------+------+
Previously, a USING
clause could be
rewritten as an ON
clause that compares
corresponding columns. For example, the following two
clauses were semantically identical:
a LEFT JOIN b USING (c1,c2,c3) a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
Now the two clauses no longer are quite the same:
With respect to determining which rows satisfy the join condition, both joins remain semantically identical.
With respect to determining which columns to display for
SELECT *
expansion, the two joins are
not semantically identical. The USING
join selects the coalesced value of corresponding
columns, whereas the ON
join selects
all columns from all tables. For the preceding
USING
join, SELECT
*
selects these values:
COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)
For the ON
join, SELECT
*
selects these values:
a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
With an inner join,
COALESCE(a.c1,b.c1)
is
the same as either a.c1
or
b.c1
because both columns will have
the same value. With an outer join (such as
LEFT JOIN
), one of the two columns
can be NULL
. That column will be
omitted from the result.
The evaluation of multi-way natural joins differs in a very
important way that affects the result of
NATURAL
or USING
joins
and that can require query rewriting. Suppose that you have
three tables t1(a,b)
,
t2(c,b)
, and t3(a,c)
that each have one row: t1(1,2)
,
t2(10,2)
, and
t3(7,10)
. Suppose also that you have this
NATURAL JOIN
on the three tables:
SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
Previously, the left operand of the second join was
considered to be t2
, whereas it should be
the nested join (t1 NATURAL JOIN t2)
. As
a result, the columns of t3
are checked
for common columns only in t2
, and, if
t3
has common columns with
t1
, these columns are not used as
equi-join columns. Thus, previously, the preceding query was
transformed to the following equi-join:
SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c;
That join is missing one more equi-join predicate
(t1.a = t3.a)
. As a result, it produces
one row, not the empty result that it should. The correct
equivalent query is this:
SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;
If you require the same query result in current versions of MySQL as in older versions, rewrite the natural join as the first equi-join.
Previously, the comma operator (,
) and
JOIN
both had the same precedence, so the
join expression t1, t2 JOIN t3
was
interpreted as ((t1, t2) JOIN t3)
. Now
JOIN
has higher precedence, so the
expression is interpreted as (t1, (t2 JOIN
t3))
. This change affects statements that use an
ON
clause, because that clause can refer
only to columns in the operands of the join, and the change
in precedence changes interpretation of what those operands
are.
Example:
CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); INSERT INTO t3 VALUES(1,1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
Previously, the SELECT
was
legal due to the implicit grouping of
t1,t2
as (t1,t2)
. Now
the JOIN
takes precedence, so the
operands for the ON
clause are
t2
and t3
. Because
t1.i1
is not a column in either of the
operands, the result is an Unknown column 't1.i1'
in 'on clause'
error. To allow the join to be
processed, group the first two tables explicitly with
parentheses so that the operands for the
ON
clause are (t1,t2)
and t3
:
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
Alternatively, avoid the use of the comma operator and use
JOIN
instead:
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
This change also applies to statements that mix the comma
operator with INNER JOIN
, CROSS
JOIN
, LEFT JOIN
, and
RIGHT JOIN
, all of which now have higher
precedence than the comma operator.
Previously, the ON
clause could refer to
columns in tables named to its right. Now an
ON
clause can refer only to its operands.
Example:
CREATE TABLE t1 (i1 INT); CREATE TABLE t2 (i2 INT); CREATE TABLE t3 (i3 INT); SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
Previously, the SELECT
statement was legal. Now the statement fails with an
Unknown column 'i3' in 'on clause'
error
because i3
is a column in
t3
, which is not an operand of the
ON
clause. The statement should be
rewritten as follows:
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
Resolution of column names in NATURAL
or
USING
joins is different than previously.
For column names that are outside the
FROM
clause, MySQL now handles a superset
of the queries compared to previously. That is, in cases
when MySQL formerly issued an error that some column is
ambiguous, the query now is handled correctly. This is due
to the fact that MySQL now treats the common columns of
NATURAL
or USING
joins
as a single column, so when a query refers to such columns,
the query compiler does not consider them as ambiguous.
Example:
SELECT * FROM t1 NATURAL JOIN t2 WHERE b > 1;
Previously, this query would produce an error ERROR
1052 (23000): Column 'b' in where clause is
ambiguous
. Now the query produces the correct
result:
+------+------+------+ | b | c | y | +------+------+------+ | 4 | 2 | 3 | +------+------+------+
One extension of MySQL compared to the SQL:2003 standard is
that MySQL allows you to qualify the common (coalesced)
columns of NATURAL
or
USING
joins (just as previously), while
the standard disallows that.
User Comments
Tip time:
(Background: This database is used to keep track of scores for students in my classes.)
So in this case, I have three tables, one has student's "codename" (as posting their real name on the web is a no-no) and an index (there is more data in this table, but this is all you really need to know.) Then there's a table with the assignments, containing the assignment name, and an index for each assignment. Finally, there is a scores table, which has for each paper I get turned in, a student_id (releated to the student index) an act_id (related to the assignments index) and a score.
It looked something like this:
students table:
assignments table:
scores table:
Now the problem was, I wanted to have the assignments listed across the top, and the scores next to the names. Something like this:
So here's how the sql statement ended up:
SELECT names.codename,
s1.score AS "Score1", s1.comment AS "Comments1",
s2.score AS "Score2", s2.comment AS "Comments2",
SUM(st.score) AS "Total"
FROM students names
LEFT JOIN scores s1 ON s1.act_id=1 AND names.id=s1.student_id
LEFT JOIN scores s2 ON s2.act_id=2 AND names.id=s2.student_id
LEFT JOIN scores st ON names.id=st.student_id
WHERE names.codename <> ''
GROUP BY names.codename
ORDER BY names.codename;
As you can see, for each activity, I need to add another left join, but it looks exactly like the last one, thus it is easy to build through a program like php. I hope this helps someone out.
I use left joins to generate sums on one table using different conditions:
t1 to make sure that ALL grouped records are shown
t(n+1) for use per condition
and as mentioned above, the JOIN condition must be used as well for the primary key AND for the condtion per sum!
Here is an example:
drop table if exists testtable;
create table testtable
(mykey int not null,
mygroup int,
cond int,
value int,
primary key (mykey));
insert into testtable
values (1, 1, 1, 5), (2, 1, 1, 6), (3, 1, 2, 3), (4, 2, 2, 4), (5, 3, 3, 5);
-- returns nothing
select t1.mygroup, sum(t2.value) as cond_1, sum(t3.value) as cond_2, sum(t4.value) as cond_3
from testtable t1
left join testtable t2 on t1.mykey=t2.mykey
left join testtable t3 on t1.mykey=t3.mykey
left join testtable t4 on t1.mykey=t4.mykey
where t2.cond=1
and t3.cond=2
and t4.cond=3
group by 1
order by 1;
-- returns correct sums
select t1.mygroup, sum(t2.value) as cond_1, sum(t3.value) as cond_2, sum(t4.value) as cond_3
from testtable t1
left join testtable t2 on t1.mykey=t2.mykey and t2.cond=1
left join testtable t3 on t1.mykey=t3.mykey and t3.cond=2
left join testtable t4 on t1.mykey=t4.mykey and t4.cond=3
group by 1
order by 1;
mygroup | cond_1 | cond_2 | cond_3
1 | 11 | 3 | 0
2 | 0 | 4 | 0
3 | 0 | 0 | 5
I also think that the missing feature of FULL OUTER JOIN is a real drawback to MySQL. However, from MySQL 4 on you can use a workaround using the UNION construct. E.g. at
http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html
Below is an example of how to left-join multiple tables independently.
SELECT ...
FROM table 1
LEFT JOIN table 2 on (table1.id = table2.id)
LEFT JOIN table 3 on (table1.id2 = table3.id2)
LEFT JOIN table 4 on (table1.id3 = table4.id3)
Martin,
your comment & others helped emensely!
Here's a left-join select that also has a regular join to another table.
I want to get all Plans (& associated SubType info),
but also see which plans user 13 is signed up
for, but only if the expire_date hasn't passed.
This select will show all Plan & SubType info,
but user-info only if the user is signed up,
and the expire-date hasn't passed.
SELECT
*
FROM mt_SubTypes, mt_Plans as t1
LEFT JOIN mt_UserPlans as t2
on (t1.id_plan = t2.id_plan
and t2.expire_date > '2003-11-12'
and t2.id_user = 13)
WHERE
t1.id_subType = mt_SubTypes.id_subType;
This is an example of using a left to get lookup values from a table twice. The reason that an outer join was used instead of an inner join, was that in this case, there may be values that are null inside of the degree table.
SELECT d.degDegId, m1.majDescription AS major_1, m2.majDescription AS major_2
FROM degree AS d
LEFT OUTER JOIN major AS m1
ON d.degMajor1 = m1.majMajId
LEFT OUTER JOIN major AS m2
ON d.degMajor2 = m2.majMajId
The oracle outter join syntax:
select a1.a,a1.b,a2.a,a2.b from a1,a2 where a1.a=a2.b(+);
Is expressed like:
select a1.a,a1.b,a2.a,a2.b from a1 left join a2 on a2.b=a1.a where 1=1;
NOTE: a2 is the optional table:
(PLEASE NOTE you must NOT specify a2 in from line(!!).
The 1=1 is optional,but I added it here so you could see where to add other conditions).
full example:
drop table a1; drop table a2;
create table a1 (a integer,b integer);
create table a2 (a integer,b integer);
insert into a1 (a,b) values (1,2);
insert into a1 (a,b) values (3,5);
insert into a2 (a,b) values (2,1);
insert into a2 (a,b) values (3,5);
select a1.a,a1.b,a2.a,a2.b from a1 left join a2 on a2.b=a1.a where 1=1;
drop table a1; drop table a2;
PS.
I prefer the Oracle syntax and would like to see it supported by MySql too.
Let's say you are doing a LEFT JOIN with a table that shares a column name in common with another table, and that you are selecting for instances where the join is missing, that is IS NULL.
Normally, the common column name is "wiped out" by the null record, but here is a workaround for it: You simply alias that common column name in the select. For instance,
CREATE TABLE t1 (INT id NOT NULL, ....);
CREATE TABLE t2 (INT id NOT NULL, ....);
...
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;
would result in the column 'id' being null on each selected row. Instead, you can do:
SELECT *, t1.id AS id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;
And now the 'id' column will be preserved since the alias is evaluated *after* the LEFT JOIN.
Note that table_reference in the above grammar may also be a "anonymous table" resulting from a join, as shown below. I don't know if this is intended or by occasion, but for me it works (MySQL 4.0.22):
SELECT products.id
FROM products
LEFT OUTER JOIN (
author2products
INNER JOIN author ON author.id = author2products.authorid
) ON products.id = author2products.productsid
This enables you to OUTER JOIN with the results of an INNER JOIN within one query.
Scenario: A group of companies have a set of publications. Any company in the group can subscribe to any number of publications.
Requirement: Create a query to generate a list of companies, showing the count of publications and subscriptions for each company.
create table company ( cid int not null, cref char(64) not null,primary key (cid));
create table publication ( pid int not null, pcid int not null, pref char(64) not null,primary key (pid));
create table subscription ( scid int not null,spid int not null,primary key (scid,spid));
insert into company values (1,'A Corp');
insert into company values (2,'B Corp');
insert into company values (3,'C Corp');
insert into company values (4,'D Corp');
insert into company values (5,'E Corp');
insert into publication values (10,1,'A News');
insert into publication values (11,1,'A Digest');
insert into publication values (12,1,'A Review');
insert into publication values (20,2,'B News');
insert into publication values (51,5,'E Digest');
insert into publication values (52,5,'E Review');
insert into subscription values (1,10);
insert into subscription values (1,11);
insert into subscription values (1,20);
insert into subscription values (1,51);
insert into subscription values (1,52);
insert into subscription values (2,10);
insert into subscription values (4,10);
insert into subscription values (4,52);
select cref,count(distinct pid) as pubs,count(distinct spid) as subs from company left join publication on cid=pcid left join subscription on cid=scid group by cid order by cref;
This checks for an event that should happen yearly, depending on other factors - it could be applied to checking for any event that should occur at regular intervals - ie monthly or weekly - with adjustments to the code for selecting max_calves.
This is a fairly complex example, with lots of joins, aliases and grouping... Its aim is to extract a list of cows which have not calved every year in a given period. It accounts for cows which have not been in the database for the entire period, are not currently on our property (locations.local==1), or which are too young to have calves for the entire period (they can't calve before 2 years old).
There are many actions that signal entry/exit of an animal - eg birth/buy/sell/death. many (or none) of them may be recorded in the history table. As a fallback, animal.yob is the year of birth of the animal.
Cows and their calves are all stored as "animals" in the animals table (the mob field distinguishes between them).
The query returns the number of calves they did have in the time frame (num_calves) and the number of calves they should have had (max_calves).
Tables: (columns not relevant to query have been removed)
create table history (id int(10), date datetime, action int(2) not null, info text, primary key(id,date));
create table actions (id int(2) auto_increment primary key, name char(20), type char(20));
create table mob_types (id int(2) primary key, name char(20));
create table locations (id int(2) auto_increment primary key, name char(20), alive tinyint(1), local tinyint(1));
create table animals (id int(10) auto_increment primary key, yob int(4), tag1 char(10), mother int(10), mob int(2), location int(2));
Foreign keys:
animals.id <- history.id
animals.location -> locations.id
animals.mob -> mobs.id
mobs.type -> mob_types.id
history.action -> actions.id
The SQL to make it happen:
The original formatting is easier to read, but the leading spaces are not preserved here (the site won't let me use html to get it either)
$xxx are the input variables:
$start_year, $end_year: date range to check (inclusive)
$all_animals: 0= alive and on property, 1= everything
$nomiss: 0= those that missed a year, 1=those that didn't miss
$allow_miss: number of misses considered acceptable.
select a1.*,aquire.date,disposal.date,count(a2.id) num_calves,
(IF(IsNull(disposal.date),$end_year,
IF(DATE_FORMAT(disposal.date,'%Y') < $end_year,DATE_FORMAT(disposal.date,'%Y'),$end_year) -
IF(DATE_FORMAT(aquire.date,'%Y')>$start_year,
IF(DATE_FORMAT(aquire.date,'%Y')>(a1.yob+2),DATE_FORMAT(aquire.date,'%Y'),a1.yob+2),
IF(a1.yob>($start_year-2),a1.yob+2,$start_year)
)
+1) as max_calves
from animals as a1
left join animals as a2
on a1.id=a2.mother
and a2.yob>=$start_year
left join history as aquire
on a1.id=aquire.id
and aquire.action in (select id from actions where type='aquire')
left join history as disposal
on a1.id=disposal.id
and disposal.action in (select id from actions where type='disposal')
where a1.mob in (select id from mobs where mobtype in (select id from mob_types where name='Cow'))
and (($all_animals>0)
or a1.location in (select id from locations where alive=1 and local=1))
group by a1.id
having IF($nomiss>0, num_calves>=(max_calves-$allow_miss), num_calves<(max_calves-$allow_miss))
order by a1.location,cast(a1.tag1 as unsigned),a1.tag1;
Speed: with about 6000 records in the animals table, and appropriate indexes, this returns in under half a second on a modern machine (versus several seconds without indexes)
I still need to add support for twins (two events in same year should be counted as one), but I'm not sure how to do that yet (any suggestions?).
If you are running older versions of Bugzilla you should be aware that you'll have to do some modification yourself, because of the change in the JOIN-syntax. For a nice to-do, see http://www.chuckcaplan.com/blog/archives/2005/10/bugzilla_and_my.html
That really helped me!
Here a example join 3 tables like that
s_g_a_t
............|
............+-> s_c
.....................|
.....................+-> s_t_i
SELECT distinct sc.TType, si.Id
FROM s_g_a_t st LEFT JOIN s_c sc ON(st.GA = sc.GA and sc.id_app = 'XXX' and sc.Select = 1) INNER JOIN s_t_i si ON (sc.TType = si.DL)
Order by si.Id
This is an example for joining tables which are related by 2 fields.
Squeme:
tblA(idA, idB1, idB2)
tblB(idB, Name)
relations: tblA.idB1->tblB.IdB
tblB.idB2->tblB.IdB
and we want to obtain all records of tblA with its related tblB.Name
The sql statment could be like this:
SELECT
tblA.IdA,
tblB.Name AS Name1,
tblB_1.Name AS Name2
FROM
tblA INNER JOIN
tblB tblB_1 ON tblA.IdB1 = tblB_1.IdB
INNER JOIN tblB
ON tblA.IdB2 = tblB.IdB
(tested in MySql v4.0.24)
You can emulate FULL OUTER JOIN using UNION (from MySQL 4.0.0 on):
with two tables t1, t2:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
with three tables t1, t2, t3:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id
The result of a full outer join betwee tables A and B includes:
- rows from the result of the inner join
- rows from A that don't have corresponding rows in B
- rows from B that don't have corresponding rows in A
Formally, the corresponding SQL statement looks like this:
SELECT *
FROM A JOIN B ON A.id = B.id
UNION ALL
SELECT *
FROM A LEFT JOIN B ON A.id = B.id
WHERE B.id IS NULL
UNION ALL
SELECT *
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL
Due to the fact that the first union represents a left outer join, the statement can be simplified:
SELECT *
FROM A LEFT JOIN B ON A.id = B.id
UNION ALL
SELECT *
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL
The example posted on September 12 2006 about how to emulate FULL OUTER JOIN using UNION has a subtle problem. Assume you have 3 tables, each with one single colum "id" and 4 rows.
t0 contains 1,3,5,7,
t1 contains 2,3,6,7,
t2 contains 4,5,6,7
(ti contains j iff 2^i xor j = 1).
The suggested solution:
SELECT * FROM t0 LEFT JOIN t1 ON t0.id = t1.id LEFT JOIN t2 ON t1.id = t2.id UNION
SELECT * FROM t0 RIGHT JOIN t1 ON t0.id = t1.id LEFT JOIN t2 ON t1.id = t2.id UNION
SELECT * FROM t0 RIGHT JOIN t1 ON t0.id = t1.id RIGHT JOIN t2 ON t1.id = t2.id
produces
id id id
1 NULL NULL
3 3 NULL
5 NULL NULL
7 7 7
NULL 2 NULL
NULL 6 6
NULL NULL 4
NULL NULL 5
where "5" appears 2 times. To get the correct result, use only LEFT JOIN, start once with each table, and name the columns:
SELECT t0.id as id0, t1.id as id1, t2.id as id2 FROM t0 LEFT JOIN t1 USING(id) LEFT JOIN t2 USING(id) UNION
SELECT t0.id as id0, t1.id as id1, t2.id as id2 FROM t1 LEFT JOIN t0 USING(id) LEFT JOIN t2 USING(id) UNION
SELECT t0.id as id0, t1.id as id1, t2.id as id2 FROM t2 LEFT JOIN t1 USING(id) LEFT JOIN t0 USING(id)
ORDER BY COALESCE(id0,id1,id2)
The ORDER BY is optional. The length of the query grows as the square of the number of tables, which is quite bad. FULL OUTER JOIN would be really welcome.
I found this union LEFT JOIN AND RIGHT JOIN, display all the columns from the two tables very usefull for me...
mysql> select * from DESCR;mysql> select * from NAME;
mysql> SELECT t1.ID,t1.NAME,t2.DESCR FROM NAME t1 LEFT JOIN DESCR t2 ON (t1.ID = t2.ID) UNION SELECT t2.ID,t1.NAME,t2.DESCR FROM NAME t1 RIGHT JOIN DESCR t
2 ON (t1.ID = t2.ID);
Use group_concat to coalesce fields that are otherwise impossible when using an inner join.
For example:
SELECT T1.a,T1.b,T2.a,GROUP_CONCAT(T2.b ORDER BY T2.b DESC SEPARATOR '|') as d FROM T1 INNER JOIN T2 ON T1.a = T2.b GROUP BY T1.a
When this query might otherwise duplicate rows, adding the group_concat coalesces them as a blob which when printed returns all the values concatenated by the defined separator string!
For those who don't fully understand the concept of joins, I wrote an article which might help.
http://www.halfgaar.net/sql-joins-are-easy
Faking a full outer join through unions doesn't work when you need to do grouping to calculate totals.
For example:
select a.x, a.y, sum(a.price), sum(b.price)
from A a full join B b on a.x = b.x and a.y = b.y
where ...
group by a.x, a.y
order by a.x, a.y
A full join is needed because there are (x,y) combinations that exist only in A or only in B.
A group by is needed because in B the (x,y) combination isn't unique.
Add your own comment.