MySQL Join Tutorial

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:

MYSQL

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).

MYSQL

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 their names to keep things simple and clean. This is done like so:

MYSQL

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.

This article has been taken from webdesign.org.