Everything PHP: Multiple Table Joins

Published on Dec 30, 2009   //  Development
1

Everything PHP

Continuing our series on MySQL table joins, today we’ll be taking a look at how to join multiple tables together. You may need to join multiple tables when you have data spread across multiple tables with reference points relating them.

The syntax for joining multiple tables versus two tables is similar. You may even recognize some of the operators from previous articles. First, let’s have a look at a two table join:

[sql]SELECT * FROM table1 JOIN table2 ON table1.id = table2.id[/sql]

With a multi-table join, we will need a field in the “joining tables” that will relate to a field in the initial table. These matching fields do not need to be the same for each “joining table”. Now, let’s take a look at our feature presentation:

[sql]SELECT * FROM table1 JOIN (table2, table3, table4) ON table1.id = table2.id AND table1.name = table3.name AND table1.email = table4.email[/sql]

There is no hard limit on the number of tables you can join; however, there is a practical limit. If you’re joining more than ten tables together, you may want to look at alternate methods, such as multiple queries. If you’re joining more than ten tables constantly in one application, your data may be spread across too many tables, and you may want to look at combining some of them.

Next week we’ll be going over the different types of joins (left, right, outer, inner, natural).

Tip: If you have phpMyAdmin installed on your server, you can easily run SQL queries by selecting a database and going to the “SQL” tab at the top of the page.

1 Comment to “Everything PHP: Multiple Table Joins”

  • Justin Swanhart
    December 30, 2009 5:39 pm

    While the JOIN syntax provided is valid, it is highly unusual. I’ve been a DBA for a long time, and I’ve never actually encountered a SQL statement which uses the /table_references/ variant of the ANSI join clause.

    One will usually see:
    1.
    SELECT *
    FROM table1
    JOIN table2
    ON table1.id = table2.id
    JOIN table3
    ON table1.name = table3.name
    JOIN table4
    ON table1.email = table4.email

    or

    SELECT *
    FROM table1,table2,table3,table4
    WHERE table1.id = table2.id
    AND table1.name = table3.name
    AND table1.email = table4.email

Categories

Archives