56
248
Chapter 10 Working with Your MySQL Database
These items are in separate tables because they relate to separate real-world objects.
This is one of the principles of good database design that we described in Chapter 8,
“Designing Your Web Database.”
To put this information together in SQL,you must perform an operation called a
join.This simply means joining two or more tables together to follow the relationships
between the data.For example,if you want to see the orders that customer Julie Smith
has placed,you will need to look at the
customers
table to find Julie’s customerid and
then at the
orders
table for orders with that customerid.
Although joins are conceptually simple,they are one of the more subtle and complex
parts of SQL.Several different types of joins are implemented in MySQL,and each is
used for a different purpose.
Simple Two-Table Joins
Let’s begin by looking at some SQL for the query about Julie Smith we just discussed:
select orders.orderid, orders.amount, orders.date
from customers, orders
where customers.name = ‘Julie Smith’
and customers.customerid = orders.customerid;
The output of this query is
+---------+--------+------------+
| orderid | amount | date |
+---------+--------+------------+
| 2 | 49.99 | 2000-04-15 |
+---------+--------+------------+
There are a few things to notice here.First,because information from two tables is
needed to answer this query,you must list both tables.
By listing two tables, you also specify a type of join,possibly without knowing it.The
comma between the names of the tables is equivalent to typing
INNER JOIN
or
CROSS
JOIN
.This is a type of join sometimes also referred to as a full join,or the Cartesian prod-
uct of the tables.It means,“Take the tables listed,and make one big table.The big table
should have a row for each possible combination of rows from each of the tables listed,
whether that makes sense or not.”In other words,you get a table,which has every row
from the
customers
table matched up with every row from the
orders
table,regardless
of whether a particular customer placed a particular order.
That brute-force approach doesn’t make a lot of sense in most cases. Often what you
want is to see the rows that really do match—that is, the orders placed by a particular
customer matched up with that customer.
You achieve this result by placing a join condition in the
WHERE
clause.This special type
of conditional statement explains which attributes show the relationship between the
two tables.In this case,the join condition is
customers.customerid = orders.customerid
80
249
Retrieving Data from the Database
which tells MySQL to put rows in the result table only if the customerid from the
customers
table matches the customerid from the
orders
table.
By adding this join condition to the query,you actually convert the join to a different
type,called an equi-join.
Also notice the dot notation used to make it clear which table a particular column
comes from;that is,
customers.customerid
refers to the
customerid
column from the
customers
table,and
orders.customerid
refers to the
customerid
column from the
orders
table.
This dot notation is required if the name of a column is ambiguous—that is,if it
occurs in more than one table.As an extension,it can also be used to disambiguate col-
umn names from different databases.This example uses a
table.column
notation, but
you can specify the database with a
database.table.column
notation,for example,to
test a condition such as
books.orders.customerid = other_db.orders.customerid
You can,however,use the dot notation for all column references in a query. Using this
notation can be a good idea,particularly when your queries begin to become complex.
MySQL doesn’t require it,but it does make your queries much more humanly readable
and maintainable.Notice that we followed this convention in the rest of the previous
query,for example,with the use of the condition
customers.name = ‘Julie Smith’
The column
name
occurs only in the table
customers
,so we do not really need to spec-
ify what table it is from.MySQL will not be confused.For humans, though,the
name
on
its own is vague,so it does make the meaning of the query clearer when you specify it as
customer.name
.
Joining More Than Two Tables
Joining more than two tables is no more difficult than a two-table join.As a general rule,
you need to join tables in pairs with join conditions.Think of it as following the rela-
tionships between the data from table to table to table.
For example,if you want to know which customers have ordered books on Java (per-
haps so you can send them information about a new Java book),you need to trace these
relationships through quite a few tables.
You need to find customers who have placed at least one order that included an
order_item
that is a book about Java.To get from the
customers
table to the
orders
table,you can use the
customerid
as shown previously.To get from the
orders
table to
the
order_items
table,you can use the
orderid
.To get from the
order_items
table to
the specific book in the
Books
table,you can use the ISBN.After making all those links,
you can test for books with Java in the title and return the names of customers who
bought any of those books.
How to C#: Basic SDK Concept of XDoc.PDF for .NET You may add PDF document protection functionality into your for .NET supports editing PDF document metadata Creator, Producer, Keywords, Created Date, and Last
pdf form creator; create a pdf form that can be filled out VB.NET PDF: Basic SDK Concept of XDoc.PDF You may add PDF document protection functionality into your for .NET supports editing PDF document metadata Creator, Producer, Keywords, Created Date, and Last
adding text to pdf form; chrome pdf save form data
43
250
Chapter 10 Working with Your MySQL Database
Let’s look at a query that does all those things:
select customers.name
from customers, orders, order_items, books
where customers.customerid = orders.customerid
and orders.orderid = order_items.orderid
and order_items.isbn = books.isbn
and books.title like ‘%Java%’;
This query returns the following output:
+-----------------+
| name |
+-----------------+
| Michelle Arthur |
+-----------------+
Notice that this example traces the data through four different tables,and to do this with
an equi-join,you need three different join conditions. It is generally true that you need
one join condition for each pair of tables that you want to join,and therefore a total of
join conditions one less than the total number of tables you want to join.This rule of
thumb can be useful for debugging queries that don’t quite work. Check off your join
conditions and make sure you’ve followed the path all the way from what you know to
what you want to know.
Finding Rows That Don’t Match
The other main type of join that you will use in MySQL is the left join.
In the previous examples,notice that only the rows where a match was found
between the tables were included.Sometimes you may specifically want the rows where
there’s no match—for example,customers who have never placed an order or books that
have never been ordered.
One way to answer this type of question in MySQL is to use a left join.This type of
join matches up rows on a specified join condition between two tables. If no matching
row exists in the right table,a row will be added to the result that contains
NULL
values
in the right columns.
Let’s look at an example:
select customers.customerid, customers.name, orders.orderid
from customers left join orders
on customers.customerid = orders.customerid;
This SQL query uses a left join to join customers with orders.Notice that the left join
uses a slightly different syntax for the join condition;in this case,the join condition goes
in a special
ON
clause of the SQL statement.
C#: XDoc.HTML5 Viewer for .NET Online Help Manual PDF and Word (.docx). Annotation Tab. Click it and click on the document to create a current date signature. Click to add a rectangle redaction on the file.
adding text to a pdf form; add form fields to pdf
61
251
Retrieving Data from the Database
The result of this query is
+------------+-----------------+---------+
| customerid | name | orderid |
+------------+-----------------+---------+
| 1 | Melissa Jones | NULL |
| 2 | Michael Archer | NULL |
| 3 | Julie Smith | 2 |
| 4 | Alan Wong | 3 |
| 5 | Michelle Arthur | 1 |
| 5 | Michelle Arthur | 4 |
+------------+-----------------+---------+
This output shows that customers Melissa Jones and Michael Archer do not have match-
ing
orderid
s because the
orderid
s for those customers are
NULL
s.
If you want to see only the customers who haven’t ordered anything,you can check
for those
NULL
s in the primary key field of the right table (in this case,
orderid
) because
that should not be
NULL
in any real rows:
select customers.customerid, customers.name
from customers left join orders
using (customerid)
where orders.orderid is null;
The result is
+------------+----------------+
| customerid | name |
+------------+----------------+
| 1 | Melissa Jones |
| 2 | Michael Archer |
+------------+----------------+
Also notice that this example uses a different syntax for the join condition.Left joins
support either the
ON
syntax used in the first example or the
USING
syntax in the second
example.Notice that the
USING
syntax doesn’t specify the table from which the join
attribute comes;for this reason,the columns in the two tables must have the same name
if you want to use
USING
.
You can also answer this type of question by using subqueries.We look at subqueries
later in this chapter.
Using Other Names for Tables:Aliases
Being able to refer to tables by other names is often handy and occasionally essential.
Other names for tables are called aliases.You can create them at the start of a query and
then use them throughout.They are often handy as shorthand.Consider the huge query
you saw earlier,rewritten with aliases:
73
252
Chapter 10 Working with Your MySQL Database
select c.name
from customers as c, orders as o, order_items as oi, books as b
where c.customerid = o.customerid
and o.orderid = oi.orderid
and oi.isbn = b.isbn
and b.title like ‘%Java%’;
As you declare the tables you are going to use,you add an
AS
clause to declare the alias
for that table.You can also use aliases for columns;we return to this approach when we
look at aggregate functions shortly.
You need to use table aliases when you want to join a table to itself.This task sounds
more difficult and esoteric than it is. It is useful,if,for example,you want to find rows in
the same table that have values in common.If you want to find customers who live in
the same city—perhaps to set up a reading group—you can give the same table (
cus-
tomers
) two different aliases:
select c1.name, c2.name, c1.city
from customers as c1, customers as c2
where c1.city = c2.city
and c1.name != c2.name;
What you are basically doing here is pretending that the table
customers
is two different
tables,
c1
and
c2
,and performing a join on the
City
column.Notice that you also need
the second condition,
c1.name != c2.name
;this is to avoid each customer coming up as
a match to herself.
Summary of Joins
The different types of joins we have described are summarized in Table 10.2.There are a
few others,but these are the main ones you will use.
Table 10.2 Join Types in MySQL
Name
Description
Cartesian product
All combinations of all the rows in all the tables in the join.Used by
specifying a comma between table names, and not specifying a
WHERE
clause.
Full join
Same as preceding.
Cross join
Same as above. Can also be used by specifying the
CROSS JOIN
key-
words between the names of the tables being joined.
Inner join
Semantically equivalent to the comma.Can also be specified using the
INNER JOIN
keywords.Without a
WHERE
condition, equivalent to a
full join.Usually,you specify a
WHERE
condition as well to make this a
true inner join.
Equi-join
Uses a conditional expression with
=
to match rows from the different
tables in the join. In SQL, this is a join with a
WHERE
clause.
RasterEdge Product Refund Policy such as product name(s), license type(s), amount, order date and the We are dedicated to provide powerful & profession imaging controls, PDF document, image to
pdf form creation; add photo to pdf form C# Imaging - Read Data Matrix in C#.NET C#: DLLs for Data Matrix Barcode Scanning from Document(TIFF, PDF, Office). This is the C# demo code for reading Date Matrix from tiff. Add references(Extra):
add text field to pdf; cannot save pdf form in reader
56
253
Retrieving Data from the Database
Left join
Tries to match rows across tables and fills in nonmatching rows with
NULL
s.Use in SQL with the
LEFT JOIN
keywords.Used for finding
missing values.You can equivalently use
RIGHT JOIN
.
Retrieving Data in a Particular Order
If you want to display rows retrieved by a query in a particular order, you can use the
ORDER BY
clause of the
SELECT
statement.This feature is handy for presenting output in
a good human-readable format.
The
ORDER BY
clause sorts the rows on one or more of the columns listed in the
SELECT
clause. For example,
select name, address
from customers
order by name;
This query returns customer names and addresses in alphabetical order by name,like
this:
+-----------------+--------------------+
| name | address |
+-----------------+--------------------+
| Alan Wong | 1/47 Haines Avenue |
| Julie Smith | 25 Oak Street |
| Melissa Jones | |
| Michael Archer | 12 Adderley Avenue |
| Michelle Arthur | 357 North Road |
+-----------------+--------------------+
Notice that in this case, because the names are in firstname, lastname format,they are
alphabetically sorted on the first name.If you wanted to sort on last names,you would
need to have them as two different fields.
The default ordering is ascending (a to z or numerically upward).You can specify this
if you like by using the
ASC
keyword:
select name, address
from customers
order by name asc;
You can also do it in the opposite order by using the
DESC
(descending) keyword:
select name, address
from customers
order by name desc;
Table 10.2 Continued
Name
Description
57
254
Chapter 10 Working with Your MySQL Database
In addition,you can sort on more than one column.You can also use column aliases or
even their position numbers (for example,3 is the third column in the table) instead of
names.
Grouping and Aggregating Data
You may often want to know how many rows fall into a particular set or the average
value of some column—say,the average dollar value per order.MySQL has a set of
aggregate functions that are useful for answering this type of query.
These aggregate functions can be applied to a table as a whole or to groups of data
within a table.The most commonly used ones are listed in Table 10.3.
Table 10.3 Aggregate Functions in MySQL
Name
Description
AVG(column)
Average of values in the specified column.
COUNT(items)
If you specify a column,this will give you the number of non-
NULL
values in that column. If you add the word
DISTINCT
in front of the
column name,you will get a count of the distinct values in that col-
umn only.If you specify
COUNT(*)
,you will get a row count regard-
less of
NULL
values.
MIN(column
)
Minimum of values in the specified column.
MAX(column)
Maximum of values in the specified column.
STD(column)
Standard deviation of values in the specified column.
STDDEV(column)
Same as
STD(column)
.
SUM(column)
Sum of values in the specified column.
Let’s look at some examples,beginning with the one mentioned earlier.You can calcu-
late the average total of an order like this:
select avg(amount)
from orders;
The output is something like this:
+-------------+
| avg(amount) |
+-------------+
| 54.985002 |
+-------------+
To get more detailed information,you can use the
GROUP BY
clause. It enables you to
view the average order total by group—for example,by customer number.This informa-
tion tells you which of your customers place the biggest orders:
63
255
Retrieving Data from the Database
select customerid, avg(amount)
from orders
group by customerid;
When you use a
GROUP BY
clause with an aggregate function,it actually changes the
behavior of the function.Instead of giving an average of the order amounts across the
table,this query gives the average order amount for each customer (or,more specifically,
for each
customerid
):
+------------+-------------+
| customerid | avg(amount) |
+------------+-------------+
| 1 | 49.990002 |
| 2 | 74.980003 |
| 3 | 47.485002 |
+------------+-------------+
Here’s one point to note when using grouping and aggregate functions:In ANSI SQL,if
you use an aggregate function or
GROUP BY
clause,the only things that can appear in
your
SELECT
clause are the aggregate function(s) and the columns named in the
GROUP
BY
clause.Also, if you want to use a column in a
GROUP BY
clause,it must be listed in the
SELECT
clause.
MySQL actually gives you a bit more leeway here.It supports an extended syntax,
which enables you to leave items out of the
SELECT
clause if you don’t actually want
them.
In addition to grouping and aggregating data,you can actually test the result of an
aggregate by using a
HAVING
clause.It comes straight after the
GROUP BY
clause and is
like a
WHERE
that applies only to groups and aggregates.
To extend the previous example,if you want to know which customers have an aver-
age order total of more than $50,you can use the following query:
select customerid, avg(amount)
from orders
group by customerid
having avg(amount) > 50;
Note that the
HAVING
clause applies to the groups.This query returns the following
output:
+------------+-------------+
| customerid | avg(amount) |
+------------+-------------+
| 2 | 74.980003 |
+------------+-------------+
45
256
Chapter 10 Working with Your MySQL Database
Choosing Which Rows to Return
One clause of the
SELECT
statement that can be particularly useful in Web applications is
LIMIT
.It is used to specify which rows from the output should be returned.This clause
takes two parameters: the row number from which to start and the number of rows to
return.
This query illustrates the use of
LIMIT
:
select name
from customers
limit 2, 3;
This query can be read as,“Select name from customers,and then return 3 rows,starting
from row 2 in the output.”Note that row numbers are zero indexed;that is,the first row
in the output is row number zero.
This feature is very useful for Web applications,such as when the customer is brows-
ing through products in a catalog,and you want to show 10 items on each page. Note,
however,that
LIMIT
is not part of ANSI SQL.It is a MySQL extension, so using it
makes your SQL incompatible with most other RDBMSs.
Using Subqueries
A subquery is a query that is nested inside another query.This feature is new in
MySQL 4.1.While most subquery functionality can be obtained with careful use of
joins and temporary tables,subqueries are often easier to read and write.
Basic Subqueries
The most common use of subqueries is to use the result of one query in a comparison
in another query.For example,if you wanted to find the order in which the amount
ordered was the largest of any of the orders,you could use the following query:
select customerid, amount
from orders
where amount = (select max(amount) from orders);
This query gives the following results:
+------------+--------+
| customerid | amount |
+------------+--------+
| 4 | 74.98 |
+------------+--------+
In this case,a single value is returned from the subquery (the maximum amount) and
then used for comparison in the outer query.This is a good example of subquery use
because this particular query cannot be elegantly reproduced using joins in ANSI SQL.
Documents you may be interested
Documents you may be interested
