5.4 Table Joins

In the world of relational databases, data often has complex relationships and is spread across multiple tables. Sometimes it is necessary to grab information from one table based on information in another. This requires that the two tables be JOIN ed.

For an example, we create a new table in the people database called addresses that contains information about people's addresses (surprise!). First, it must be created as follows :

 mysql>  CREATE TABLE addresses (  ->  lastname CHAR(20),  ->  firstname CHAR(20),  ->  address CHAR(40),  ->  city CHAR(20),  ->  state CHAR(2),  ->  zip CHAR(10)  -> ); 

The table needs some data:

 mysql>  INSERT INTO addresses  ->  (lastname, firstname, address, city, state, zip)  ->  VALUES ("Wall", "Larry", "Number 1 Perl Way",  ->  "Cupertino", "CA", "95015-0189"  ->  );  mysql>  INSERT INTO addresses  ->  (lastname, firstname, address, city, state, zip)  ->  VALUES ("Torvalds", "Linus", "123 Main St.",  ->  "San Francisco", "CA", "94109-1234"  ->  );  mysql>  INSERT INTO addresses  ->  (lastname, firstname, address, city, state, zip)  ->  VALUES ("Raymond", "Eric", "987 Oak St.",  ->  "Chicago", "IL", "60601-4510"  ->  );  mysql>  INSERT INTO addresses  ->  (lastname, firstname, address, city, state, zip)  ->  VALUES ("Kedzierski", "John", "3492 W. 75th St.",  ->  "New York", "NY", "10010-1010"  ->  );  mysql>  INSERT INTO addresses  ->  (lastname, firstname, address, city, state, zip)  ->  VALUES ("Ballard", "Ron", "4924 Chicago Ave.",  ->  "Evanston", "IL", "60202-0440"  ->  );  

To verify the tables were populated , do this:

 mysql>  SELECT * FROM age_information;  +------------+-----------+------+   lastname    firstname   age   +------------+-----------+------+   Wall        Larry        46    Torvalds    Linus        31    Raymond     Eric         40    Kedzierski  John         23    Ballard     Ron          31   +------------+-----------+------+  5 rows in set (0.00 sec)  mysql>  SELECT * FROM addresses;  +----------+---------+-----------------+--------------+-----+----------+   lastname firstnameaddress          city          statezip         +----------+---------+-----------------+--------------+-----+----------+  Wall      Larry    # 1 Perl Way     Cupertino     CA   95015-0189  Torvalds  Linus    123 Main St.     San Francisco CA   94109-1234  Raymond   Eric     987 Oak St.      Chicago       IL   60601-4510  KedzierskiJohn     3492 W. 75th St. New York      NY   10010-1010  Ballard   Ron      4924 Chicago Ave.Evanston      IL   60202-0440  +----------+---------+-----------------+--------------+-----+----------+  5 rows in set (0.00 sec) 

Now, on to the JOIN s. Let's say we want to find out what city our under-40-year-old people live in. This requires looking up information in two tables: To find out who is under 40, we look in age_information , and to find out the city, we look in addresses . Therefore, we need to tell the SELECT command about both tables.

Because both tables are being used, we need to be specific about which table a particular field belongs to. In other words, instead of saying SELECT city , we need to say what table that field is in, so we say SELECT addresses. city . The addresses.city tells MySQL that the table is addresses and the field is city .

Moreover, we need to hook the two tables together somehow ”we do so with the following command by making sure the lastname from the addresses row matches the lastname from the age_information row. Ditto for the firstname . So, our command is:

 mysql>  SELECT addresses.city  ->  FROM addresses, age_information  ->  WHERE age_information.age < 40 AND  ->  addresses.lastname = age_information.lastname  ->  AND addresses.firstname = age_information.firstname;  +---------------+   city            +---------------+   San Francisco    NewYork          Evanston        +---------------+  3 rows in set (0.02 sec) 

In English, we are saying, "give me the city for all the people with ages less than 40, where the last names and first names match in each row."

Let's grab the last names and zip codes for all those 40 and over, and order the data based on the last name :

 mysql>  SELECT addresses.lastname, addresses.zip  ->  FROM addresses, age_information  ->  WHERE age_information.age >= 40 AND  ->  addresses.lastname = age_information.lastname AND  ->  addresses.firstname = age_information.firstname  ->  ORDER BY addresses.lastname;  +----------+------------+   lastname  zip          +----------+------------+   Raymond   60601-4510    Wall      95015-0189   +----------+------------+  2 rows in set (0.02 sec) 

As you can see, there are lots of different ways to query more than one table to get the exact information desired.