Green Acorn

Blog

SQL Join Tutorial

At most times when working with databases, you are going to need to access data that is spread out in different tables. In these circumstances you will need to use SQL to join the required tables and return the data in one SQL query. For example, lets say with have two tables “Names” and “Emails”. Both have a table field called ID that relates them.

Now what we need to do is request a list of data containing the persons name and email address. To do this type the following SQL:

SELECT n.Name As 'Full Name', e.Email AS 'Email Address'
    FROM Names AS n
    JOIN Emails AS e
       ON e.ID = n.ID
    ORDER BY 'Full Name'

You should now see a list similar to the table below:

Full Name Email Address
Fred Flinstone fred@freddy.com
Billy Gun bill@wwe.com

Lets run over the code. The first part is the SELECT statement and a list of fields such as n.Name. You might be thinking what the “n.” is about? Have a look at the next SQL line – “FROM Names AS n“. This is where we select the Names table and give is a name / alias using the AS clause. This means n.Name refers to the Name field in the Names table. The next part is where we join the Names table to the Emails table and give the Emails table a name of e.

The ON clause is the condition of where by the two tables are joined. So in this example e.ID = n.ID – meaning the tables will be joined where the Emails ID filed is the same as the Names ID field. Finally, ORDER BY ‘Full Name’ means that the data will be listed in alphabetical order of the Name field in the Names table.

Some web developers write their SQL joins slightly different. However, this is considered the old way and can be a bit more confusing. The below example will return the same list of data.

SELECT n.Name As 'Full Name', e.Email AS 'Email Address'
    FROM Names AS n, Emails AS e
    WHERE e.ID = n.ID
    ORDER BY 'Full Name'

I hope some people will find this web development / web design tutorial useful. Please feel free to leave comments and share any further tips.

Author:
Posted in: SQL

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>