How to Identify What MySQL Accounts Currently Exist
How to Use a PHP Class in a Script
How to Use Inheritance in PHP with Object-Oriented Programming

Combine Information from Multiple MySQL Tables with JOIN

You can use a JOIN SELECT query to combine information from more than one MySQL table. With JOIN, the tables are combined side by side, and the information is retrieved from both tables.

Tables are combined by matching data in a column — the column that they have in common. The combined results table produced by a join contains all the columns from both tables. For instance, if table1 has two columns (memberID and height), and table2 has two columns (memberID and weight), a join results in a table with four columns: memberID (from table1), height, memberID (from table2), and weight.

The two common types of joins are an inner join and an outer join. The difference between an inner and outer join is in the number of rows included in the results table.

  • Inner join: The results table produced by an inner join contains only rows that existed in both tables.

  • Outer join: The combined table produced by an outer join contains all rows that existed in one table with blanks in the columns for the rows that did not exist in the second table.

For instance, if table1 contains a row for Joe and a row for Sally, and table2 contains only a row for Sally, an inner join would contain only one row: the row for Sally. However, an outer join would contain two rows — a row for Joe and a row for Sally — even though the row for Joe would have a blank field for weight.

The results table for the outer join contains all the rows for one table. If any of the rows for that table don’t exist in the second table, the columns for the second table are empty. Clearly, the contents of the results table are determined by which table contributes all its rows, requiring the second table to match it.

Two kinds of outer joins control which table sets the rows and which must match: a LEFT JOIN and a RIGHT JOIN.

You use different SELECT queries for an inner join and the two types of outer joins. The following query is an inner join:

SELECT columnnamelist FROM table1,table2
                      WHERE table1.col2 = table2.col2

And these queries are outer joins:

SELECT columnnamelist FROM table1 LEFT JOIN table2
       ON table1.col1=table2.col2
SELECT columnnamelist FROM table1 RIGHT JOIN table2
       ON table1.col1=table2.col2

In all three queries, table1 and table2 are the tables to be joined. You can join more than two tables. In both queries, col1 and col2 are the names of the columns being matched to join the tables. The tables are matched based on the data in these columns. These two columns can have the same name or different names, but they must contain the same type of data.

As an example of inner and outer joins, consider a Clothes catalog with two tables. One table is Product, with the two columns Name and Type holding the following data:

Name          Type
T-shirt       Shirt
Dress shirt   Shirt
Jeans         Pants

The second table is Color, with two columns Name and Color holding the following data:

Name      Color
T-shirt   white
T-shirt   red
Loafer    black

You need to ask a question that requires information from both tables. If you do an inner join with the following query:

SELECT * FROM Product,Color WHERE Product.Name = Color.Name

you get the following results table with four columns: Name (from Product), Type, Name (from Color), and Color.

Name      Type    Name      Color
T-shirt   Shirt   T-shirt   white
T-shirt   Shirt   T-shirt   red

Notice that only T-shirt appears in the results table — because only T-shirt was in both of the original tables, before the join. On the other hand, suppose you do a left outer join with the following query:

         ON Product. Name=Color. Name

You get the following results table, with the same four columns — Name (from Product), Type, Name (from Color), and Color — but with different rows:

Name          Type    Name      Color
T-shirt       Shirt   T-shirt   white
T-shirt       Shirt   T-shirt   red
Dress shirt   Shirt   <NULL>    <NULL>
Jeans         Pants   <NULL>    <NULL>

This table has four rows. It has the same first two rows as the inner join, but it has two additional rows — rows that are in the Product table on the left but not in the Color table. Notice that the columns from the table Color are blank for the last two rows.

And, on the third hand, suppose that you do a right outer join with the following query:

         ON Product.petName=Color. Name

You get the following results table, with the same four columns, but with still different rows:

petName   petType   petName   petColor
T-shirt   Shirt     T-shirt   white
T-shirt   Shirt     T-shirt   red
<NULL>    <NULL>    Loafers   Black

Notice that these results contain all the rows for the Color table on the right but not for the Product table. Notice the blanks in the columns for the Product table, which doesn’t have a row for Loafers.

Sometimes it’s useful to find out which rows in a table have no matching entries in another table. For example, suppose that you want to know who has never logged in to your Members Only section. Suppose you have one table with the member’s login name (Member) and another table with the login dates (Login).

You can ask this question by selecting from the two tables. You can find out which login names don’t have an entry in the Login table with the following query:

SELECT loginName FROM Member LEFT JOIN Login
       ON Member.loginName=Login.loginName
       WHERE Login.loginName IS NULL

This query gives you a list of all the login names in the Member table that aren’t in the Login table.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Considering the Various Uses for the PHP Scripting Language
How MySQL Works
Setting Up Your Computer for PHP and MySQL Web Site Development
How to Perform PHP Validation
Retrieve Information from a MySQL Database