Joining Three or More Tables in SQL With MySql (Nested Joins)

| 6 Comments

Joining three or more database tables seems a bit tricky. But once you know how to join three tables you can literally join any number of database tables. For the purpose of this tutorial I have four tables named Table1 to Table4. Field names are given as F[Table Number][Alphabet], eg: F1A. Table names with double numbers are foreign keys. The names of foreign Keys are given as follows: F[Table Number][Table Number which it points to][Alphabet]. Foreign Key of Table1 points to Table2 and that of Table2 points to Table3 and finally that of Table3 points to Table4.

Table1
F1A Primary Key
F12A Foreign Key Table2
F1B
Table2
F2A Primary Key
F23A Foreign Key Table3
F2B
Table3
F3A Primary Key
F34A Foreign Key Table4
T3B
Table4
F4A Primary Key
F4B
F4C

For simplicity I select all fields (SELECT *) and select all records (WHERE 1) in the SQL

Simple Two Table Joint

To start with I will just demonstrate a simple join statement.

Select * FROM Table1 as T1 JOIN Table2 as T2 ON T1.F12A=T2.F2A WHERE 1

Three Table Join Statement

To make a 3 table Sql Join we must first do a two table join and then join this result with the remaining table.

SELECT * FROM
( Select * FROM Table1 as T1 JOIN Table2 as T2 ON T1.F12A=T2.F2A WHERE 1 ) as T12
JOIN Table3 as T3 ON T12.F23A=T3.F3A WHERE 1

Table T12 comes from the joining of Table1 and Table2.

Join Four Tables With SQL

With four tables we can either follow the same strategy that we had done with 3 tables or we can use a slightly  different technique. If we use the above linear Joining this is what it would look like.

SELECT * FROM(
SELECT * FROM  (
SELECT * FROM Table1 as T1 JOIN Table2 as T2 ON T1.F12A=T2.F2A WHERE 1 ) as T12 JOIN
Table3 as T3 ON T12.F2A=T3.F3A WHERE 1 ) as T123 JOIN
T4 ON T123.F34A=T4.F4A WHERE 1

The second method is a binary tree approach. It groups that table into sub-joins and combine the results rather than a linear approach.

SELECT * FROM
( SELECT * FROM  ( Select * FROM Table1 as T1 JOIN Table2 as T2 ON T1.F12A=T2.F2A WHERE 1 ) as T12 JOIN
( SELECT * FROM  ( Select * FROM Table3 as T3 JOIN Table4 as T4 ON T1.F34A=T4.F4A WHERE 1 ) as T34 ON T12.F23A=T34.F3A WHERE 1

Combine Any Number of Tables

The above techniques can be combinations to join any number of tables. But one thing you have to keep in mind is that join statements are expensive. That is, they consume a lot of memory and processing time. So it advice to make the joint statements as simple as possible. Special care have to be given for the database design. A bad database design will consume server resources in an exponential rate as the database gets bigger, especially with JOIN statements.
Another thing to keep in mid is the order of JOIN when multiple joins are maid. Whenever possible the first join must be the join that retrieves the minimum records. This will save unwanted joints that will waist server resources.

If you have any questions or suggestions feel free to leave then in the comments section.

6 Responses to Joining Three or More Tables in SQL With MySql (Nested Joins)

  1. Hello. Thanks for tutorial. What does “WHERE 1″ means on this statement “( Select * FROM Table1 as T1 JOIN Table2 as T2 ON T1.F12A=T2.F2A WHERE 1 )”?

    codex73
    • “WHERE 1″ simply mean “select every record”. If you omit the WHERE statement most DBMS gives an error. You can use any condition that you want instead of that.
      You can also say “WHERE 1=0″, in which case no record is returned. However certain information regarding the database table could be retrieved.

  2. How are the fields as F12A, F2A, F3A, F23A etc defined ? Can you please explained a little bit more? By the way, I have four tables and I only need two fileds from each (SELECT * FROM gives me lots of trouble!). How is the syntax for my case? Thanks.

    Yifang
  3. Yifang, F=fiels, number that follows represents the table to which it belong to, that is F1 is a filed belonging to Table1. The alphabet at the end is the filed identifier. F12A simply mean that its a foreign key in Table1 that points to Table2. Please dont get confused with the naming.
    Actually the nested SELECT are creating temporary tables. Its hard to explain with words so if you can send me your database schema and tell me what fields you want to extract I can give you the solution. I have an email at Gmail with my blog name.

  4. i followed ur pattern but i cannt accomplish it with four tables. It works when i have three tables. please help.

    [code]
    SELECT * FROM(
    SELECT * FROM(
    SELECT * FROM vtiger_seactivityrel as T1
    JOIN vtiger_activity_reminder as T2 ON T1.activityid = T2.activity_id WHERE 1) as T12
    JOIN vtiger_crmentity as T3 ON T12.activity_id = T3.crmid WHERE 1) as T123
    JOIN vtiger_leadaddress as T4 ON T123.activity_id = T4.leadaddressid WHERE 1
    [/code]

    sonny
    • Your SQL seems to be correct. What error do you get?
      Are you selecting every thing from every table (‘select *’ & ‘where 1′)? This might result in a very huge result when you combine 4 tables.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
Designed and developed by Alfie Punnoose @ Blokeish.com