"Chuck Anderson" <websiteaddressseemy.sig> wrote in message
news:65WdnQU7d-k1nnHeRVn-iwcomcast.com...The syntax you have used above is called a "join" and it could be thought of> [I have been using MySQL with Php for a couple of years and have been
> doing fine with simple INSERT, UPDATE, SELECT, and DELETE statements. But
> now I have a need to use multiple table SELECTs and I am having some
> I have two tables - table1 and table2. Their structures are identical and
> contain a column called status (an integer to store http response status
> codes - e.g., 200, 403, 404, ....). I would like to get a list of the
> distinct values of status across both tables.
> I have tried to use the following query:
> SELECT DISTINCT status FROM table1, table2 ORDER BY status
as combining the two tables "side by side" instead of "one after the other".
You probably want to use UNION to list the records of one table, followed by
the records of the other table.
SELECT DISTINCT table1.status
SELECT DISTINCT table2.status
UNION returns only the distinct rows from both of its component queries (by
Read more about UNION in MySQL here:
What you did was called a "Cartesian product". You have matched every row> Also, ..... I'm not sure what happened, but when I tried:
> SELECT table1.status, table2.status FROM table1, table2
> ... MySQL seemed to go into an infinite loop (grabbed 100% CPU - disk
> thrashing - and would not let go). I could not get my Windows XP machine
> to respond without powering it off. I'd like to understand why that
> happened so I can avoid doing something like that again.
in table1 to every row in table2. The result set has N x M rows, where N is
the number of rows in table1 and M is the number of rows in table2. You can
see how this can be a very large amount of data, and it is costly to
generate such a result set.
Cartesian products have some good uses, but typically when you join two
tables together like that, you want to specify some condition that defines
how the rows relate to one another, so that only truly matching rows are
I think it's a bit surprising that you have used MySQL for as long as you
have, but never learned about joins. Using a relational database without
understanding joins is like using a language like PHP without understanding
a "while" loop. It's possible to do some useful work without using theses
features, but you're sure missing out on a lot of the power you have
available to you. Joins are fundamental to relational databases.
You should read one of the SQL tutorials on the internet that discuss joins.
Just Google for "sql join tutorial" or similar search terms.
Or read this Wikipedia article on joins in SQL:
Or read reference doentation for joins in MySQL here: