Initially I shall show you the way to get the work done with the simplest form of Join using the WHERE clause. You would be using only those features that you have already learnt.

Example : Suppose you want the names of all the authors as well as the books they have written, what would you do? Instead of using 2 separate queries to get the work done you could use one query as follows

SELECT firstname,lastname,title FROM authors,books WHERE authors.author_id = books.author_id

This query selects 3 columns (firstname, lastname and title) from the joined table of 2 different tables (authors and books) depending on the criteria mentioned. In this case it selects those rows from the joined table where the value of the authors.author_id field is equal to books.author_id in the joined table. So both the author_id values should be equal. This query would return 4 rows of data. (Try out these examples so that you can see the results yourself)

What is this joined table? Where did 2 author_id values come from? I didn't understand that example at all !!!
Hold on!! You need some detailed explanation to understand all of that. First and foremost you should understand that the basic difference between the query discussed above and all previous queries you have learnt till now - and that is that now you are asking the query to SELECT from more than 1 table.

In our case SELECT .... from authors,books .... The moment you do so, the way queries work might surprise you. To understand it clearly, you can assume the following. The moment you ask a query to select from more than one table, a temporary table is created which has all possible combinations of the contents of the 2 tables. In our case this could be represented with the following table (Do not bother about the order of the columns.. just look at the number of rows in this temporary table)

This table below could be a result of a SELECT * FROM authors,books

If you have seen the values in this table carefully, you must have observed that many of the rows indicate wrong information. Not what the database really intended to hold. Yes it might be wrong but that's what is generated when you create a Join between 2 tables. Now its upto you to extract the correct rows from this table using a proper condition. The rows marked in red are the correct rows and also they happen to be the ones where the first column (authors.author_id) is equal to the seventh column (books.author_id) . These are the rows that hold the correct information as intended.

Thus when you SELECT data from more than one table, in order to extract only the correct rows of data from the joined table, you must always use a condition for checking the equality of the common column in the WHERE clause of your query. For example in our case as explained before we should use the following query

SELECT firstname,lastname,title FROM authors,books WHERE authors.author_id = books.author_id

Thus you get the name of all the authors as well as a list of all the books that they has written.