Joining Data From Multiple Tables

Often, the data you need will be spread across two(or more tables). In such cases, we have to join these tables together to get the data. The JOIN statement in SQL is created for a situation like that.

In our database there are two tables - Character and Quote. Say we need to see all quotes of Homer Simpson...

SELECT id, name FROM Character WHERE name='Homer Simpson'

SELECT quote FROM Quote WHERE character_id=<Homer's ID>

To get Homer's quotes, we had to use two queries - the first to get Homer's ID and the next to find all his quotes using that ID. Now lets use the JOIN statement to combine both tables together...

SELECT Character.name, Quote.quote FROM Character INNER JOIN Quote ON Character.id=Quote.character_id WHERE Character.name='Homer Simpson'

Syntax

SELECT <Table>.<Field>, <Table>.<Field> FROM <Table 1> 
INNER JOIN <Table 2> ON <Table 1>.<Primary Key>=<Table 2>.<Foreign Key> 
WHERE <Conditions>

Lets see the main methods of joining two tables...

INNER JOIN

INNER JOIN joins two table only if the condition given as the ON condition is satisfied in both tables. Yeah, I am sure you have no idea what I am talking about. Time for an example.

SELECT Character.name, Quote.quote FROM Character INNER JOIN Quote ON Character.id=Quote.character_id WHERE Character.name='Homer Simpson'

We just created a query where we fetched all the quotes of Homer Simpson. In that example, the ID of the Homer Simpson row is '1'. There is a row with id 1 in the Character table - and there is a row with character_id 1 in the Quote table. Here the wanted row is available in both tables. INNER JOIN will return result only in such cases.

LEFT JOIN

Here, the data we are searching for must exist in the first table - but not necessarily in the second. Lets say we are searching for all quotes of 'Maggie Simpson'. There is a character called 'Maggie Simpson' in the Character table - but she does not have any quotes(owing to the fact that she's a baby who has not started talking yet). So INNER JOIN will fail. The right join to use here is the left join - and sorry about the pun.

SELECT Character.name, Quote.quote FROM Character LEFT JOIN Quote ON Character.id=Quote.character_id WHERE Character.name='Maggie Simpson'

The Other JOINs

There are some other JOINS as well - take a look at A Visual Explanation of SQL Joins for more explanations.

Joining more than two tables

There are times when you need to join more than two tables together. In such cases, you can use this method...

SELECT Character.name, Quote.quote FROM Character,Quote WHERE Character.id=Quote.character_id AND Character.name='Bart Simpson'

Here, I am only joining two tables(as our table have just two tables right now), but you can add more tables to the FROM list.