MySQL Join Tutorial

17 February 2011

In mySQL is a posibility to make joins. A join query is the joining of two (or more!) tables in one query. For example there are two tables:

Tabel cars
- ID
- name

Tabel colors
- ID
- color
- car_ID

In the first table are all the cars. In the second table there are all posible colors for the cars. Since a car can have more colors we'll make a join to see wich colors are available for a car.

First we are going to fetch all cars:

SELECT
    *
FROM
    cars


As you can see, i don't write the query in one line as you might be used, but over several lines. This is a bit useless in simple queries as this one, but if you have got some complex queries, this can improve the readability of your query. Get used to doing this when you are writing complex queries to keep things simple for yourself (and other coders).

SELECT
    *
FROM
    cars
JOIN
    colors
ON
    colors.car_ID=cars.id


This query looks a lot like the first one, but we added a few lines. At first, after the JOIN, we state wich table we want to join with. In this case the "colors" table. Then is the difficult peace... After the ON, we state on wich columns the tables should join. Every color has a reference to a car by the "car_ID" column. Every car has an ID, and these two columns are the link between the two tables. These are the columns we want to join.

In complex queries, always point to the table you're talking about. For example; if you use "WHERE id=3", MySQL doesn't know wich ID you're talking about. Both tables have a column "ID", so you have to say to MySQL wich table you mean. To do this, put the name of the table in front of the column name.

In case of table names that are very long, you can abbreviate there names to keep things simple and clean. This is done like so:

SELECT
    *
FROM
    cars AS cr
JOIN
    colors AS cl
ON
    cl.car_ID=cr.id


As you can see in above query, i state that MySQL should name the table cars "cr" from now one, and the table colors should be names "cl". This can save you many typing and keeps things nice and clear.
You must have JavaScript enabled to use this form!

Leave a comment!

  1. Your mail is safe with me. It's only only used to display your Gravatar image!

9 comments

  1. Gravatar

    Johan van Tongeren

    16 December 2011

    Hi Caleb,

    The columns on which you want to join don't need to be the ID columns. It can be all columns, as long as there is a connection between the two columns. In this example, the car_id is the connection to the car, but this might as well have been the brand of the car (cl.car_brand = cr.brand for example).

    A where clause can also be added just as you would do in a normal query:

    SELECT * FROM cars AS cr
    JOIN colors AS cl ON cl.car_ID=cr.id
    WHERE cr.brand='Subaru'

  2. Gravatar

    Caleb

    16 December 2011

    excellent post..but i do have an issue with the column..must the column of the table i want to join must be the ID and how will i include the WHERE clause. Am still new in PHP
    Merry Christmass

  3. Gravatar

    Jan Brinkmann

    10 December 2010

    Hey, I found this tutorial and it looks generally fine. Just in case anybody is looking for other join types too, take a look at the MySQL Join Tutorials:

    - http://mysql-join.com

    All joins MySQL understands are explained with a description, examples and links to further information. I would be interested in feedback. What has to be optimized to make it more useful ? Thanks in advance and have a merry christmas.

  4. Gravatar

    jayaprakash

    21 October 2010

    want to join tutorial

  5. Gravatar

    strony internetowe tworzenie

    27 December 2008

    This looks good! Really good tutorial include so many helpful informations!
    Excellent SITE. I will refer people to your ITEMS. Effective use of Wordpress had some exceptional.
    Cheers

  6. Gravatar

    rental epc

    22 December 2008


    This looks good! Really good tutorial include so many helpful informations!
    Excellent SITE. I will refer people to your ITEMS. Effective use of Wordpress had some exceptional.
    Cheers

  7. Gravatar

    Peter Joseph

    13 March 2008

    Actually these all about to get record from table . But wat if we wanna have to insert record in two table . having one to one relation ship.

  8. Gravatar

    DreamDealer

    22 January 2008

    @Deepesh:
    No matter how many tables you want to join, the one thing they all need to have in common is one column that connects one table to another. For example, if you would have a thirth table in the example above, something like engine types, you would want to join it with the cars table and use the car_id as an identifier for the query. Every color is connected to a car by the column car_id, and then every engine will be connected to a car by a column car_id.

  9. Gravatar

    Deepesh

    14 January 2008

    I want to know what is the fundamental things to join more than two tables.Please tell me soon . Thanks!!!!!!!!!!!!!!!