What do I do if I wanted a list of books written by an author with author_id =1 as well as his full name?
With the type of Join I just explained above, it would not be too tough to get this done. A query shown below could be used:

SELECT firstname,lastname,title FROM authors,books
	WHERE (authors.author_id = books.author_id AND authors.author_id=1)

This would first create a joined table as discussed previously having 8 rows of data. Then it would further refine this table and select only 4 rows from them as a result of the authors.author_id = books.author_id criteria in the WHERE clause. Once down to 4 rows, it would further refine this table to 1 final row because of the second criteria which is authors.author_id=1 . Thus you get the exact data that you wanted - The firstname, lastname and the titles of the books written by Jason Hunter, since his author_id is equal to 1. This kind of Join that you have been using till now is called a Cross Join or a Cartesian Join.

So what are the other types of Joins?
As I told you initially, there are many other types of Joins. Inner Joins and Outer Joins are 2 of them.

What are Inner Joins?
Inner Joins are the most commonly used type of Joins are are almost the same thing that you just learnt above. Check the following query:

SELECT * FROM (authors INNER JOIN books ON authors.author_id = books.author_id)

This would return 4 rows of data having all the information about the 4 books. This gives exactly the same result as the following query:

SELECT * FROM authors,books WHERE authors.author_id = books.author_id

Basically Inner Joins combines all the records in the first table with all the records in the second table and then selects those rows depending on the criteria that is present after the the ON keyword in the query.

The most important thing to remember in Inner Joins is that only those records from both the tables are combined where there is a corresponding value in both the tables. You will understand this point clearly when you read about Outer Joins.

What are Outer Joins?
Outer Joins are almost similar to Inner Joins, but in Outer Joins all the records in one table are combined with records in the other table even if there is no corresponding common value. To get this point assume that our authors table is now the one shown below instead of the original one. The books table remains the same.

Result of an outer join

Note that there is a new row added.

There are 2 types of Outer Joins - Left Join and Right Join. Consider an example of Left Join:

SELECT * FROM (authors LEFT JOIN books ON authors.author_id = books.author_id)

This query would now (considering the new authors table) return 5 rows of data. The last row would correspond to the record of the author named Kiran Pai but there would be NULL values for the fields such as title,pages,book_id... since there are no books written by Kiran Pai in our books table.

This is exactly what Left Joins do - they combine all the records in the first table with those in the second table irrespective of whether there are corresponding values in 2 tables. By corresponding I mean like in our case since there were no books by Kiran Pai in our books table there are no corresponding values for Kiran Pai in our books table. But inspite of there being no corresponding values all the values were joined and thus you got the NULL values in your final query result. This point is very important to understand Joins.. so please do read it again in case you have not understood it clearly.

Could you explain the concept of Inner Joins once again with respect to this Left Joins?
Yeah.. now you will understand better. As I mentioned in Left Joins all the records in the first table are combined with data from the second table. And if there is no corresponding data in the second table then a NULL value is inserted into the results. Whereas in Inner Joins, records from the 2 tables are combined only if there are corresponding values in both the tables. For example consider the following Inner Join on the books table and our new authors table:

SELECT * FROM (authors INNER JOIN books ON authors.author_id = books.author_id)

This query would return only 4 rows of data and not 5 inspite of the new Kiran Pai entry being present. This is because it could find no corresponding entry in the books table for the author named Kiran Pai (there were no books written by Kiran Pai)

There are are 2 types of Outer Joins ..right??
Yeah.. there are 2 types. One is Left Join and the other obviously is Right Join. Consider the same example as shown previously for Left Join, but this time use a Right Join instead of a Left Join:

SELECT * FROM (authors RIGHT JOIN books ON authors.author_id = books.author_id)

This time you would NOT GET 5 rows of data, instead you would get only 4 rows of data. Why so??

Since while joining the data from the books table with the authors table, the books table data is considered first.. since it is a Right Join. Thus for every row in the books table a check for corresponding value in the authoirs table would be made. Thus while adding all the rows of the books table to the joined table, since there were no books written by Kiran Pai, so that particular record from the authors table was not added at all to the joined table. Remember that the joined table is the kind of temporary table that is created while using Joins. That table is refined depending on the criteria present in the query.

Inner Joins and Outer Joins aren't really clear in my mind right now. Why don't you explain it once again in short ?
Actually, all that is required is a re-reading of the previous 4-5 questions. In case you still haven't got it, there is only way out. Create actual tables using some software and then run some sample queries on those tables. Remember not to put corresponding entries for all your data in both of your tables.

By that I mean in case you are making 2 tables - one for artists and one for songs. See to it that in case you have 10 artists names in the artists table, you do not have songs sung by all of those 10 artists in your songs table. Add songs by only 6-7 artists. When you have such a kind of database where there isn't corresponding information for all the records in one table in the other table, Inner Joins and Outer Joins show their real working. In case you have songs sung by all 10 artists in your Songs table, most of your query results would be the same irrespective of what kind of Join you use. Only the order of the rows in the resultset might be altered. So you would be confused..you wouldn't understand the difference between the working of different Joins.

Are there other ways in which I can use Joins?
Yeah you can combine 2 Joins to create more complex Joins. I shall not go into the details of those kind of Joins, but I shall outline the structure of the query which you have to make. This kind of thing is required when you are combining data from say 3 tables. Your query would look something like the following:

SELECT ... , ... FROM ( .... INNER JOIN .... ON .... = .... )
	INNER JOIN .... ON .... = .... WHERE .... = .....

This is not really for beginners, but if you can get it.. you are not a beginner.. you are already on your way to be an expert.

What do I do if I wanted a list of books written by an author with author_id =1 as well as his full name?
With the type of Join I just explained above, it would not be too tough to get this done. A query shown below could be used:

SELECT firstname,lastname,title FROM authors,books
	WHERE (authors.author_id = books.author_id AND authors.author_id=1)

This would first create a joined table as discussed previously having 8 rows of data. Then it would further refine this table and select only 4 rows from them as a result of the authors.author_id = books.author_id criteria in the WHERE clause. Once down to 4 rows, it would further refine this table to 1 final row because of the second criteria which is authors.author_id=1 . Thus you get the exact data that you wanted - The firstname, lastname and the titles of the books written by Jason Hunter, since his author_id is equal to 1. This kind of Join that you have been using till now is called a Cross Join or a Cartesian Join.

So what are the other types of Joins?
As I told you initially, there are many other types of Joins. Inner Joins and Outer Joins are 2 of them.

What are Inner Joins?
Inner Joins are the most commonly used type of Joins are are almost the same thing that you just learnt above. Check the following query:

SELECT * FROM (authors INNER JOIN books ON authors.author_id = books.author_id)

This would return 4 rows of data having all the information about the 4 books. This gives exactly the same result as the following query:

SELECT * FROM authors,books WHERE authors.author_id = books.author_id

Basically Inner Joins combines all the records in the first table with all the records in the second table and then selects those rows depending on the criteria that is present after the the ON keyword in the query.

The most important thing to remember in Inner Joins is that only those records from both the tables are combined where there is a corresponding value in both the tables. You will understand this point clearly when you read about Outer Joins.

What are Outer Joins?
Outer Joins are almost similar to Inner Joins, but in Outer Joins all the records in one table are combined with records in the other table even if there is no corresponding common value. To get this point assume that our authors table is now the one shown below instead of the original one. The books table remains the same.

Result of an outer join

Note that there is a new row added.

There are 2 types of Outer Joins - Left Join and Right Join. Consider an example of Left Join:

SELECT * FROM (authors LEFT JOIN books ON authors.author_id = books.author_id)

This query would now (considering the new authors table) return 5 rows of data. The last row would correspond to the record of the author named Kiran Pai but there would be NULL values for the fields such as title,pages,book_id... since there are no books written by Kiran Pai in our books table.

This is exactly what Left Joins do - they combine all the records in the first table with those in the second table irrespective of whether there are corresponding values in 2 tables. By corresponding I mean like in our case since there were no books by Kiran Pai in our books table there are no corresponding values for Kiran Pai in our books table. But inspite of there being no corresponding values all the values were joined and thus you got the NULL values in your final query result. This point is very important to understand Joins.. so please do read it again in case you have not understood it clearly.

Could you explain the concept of Inner Joins once again with respect to this Left Joins?
Yeah.. now you will understand better. As I mentioned in Left Joins all the records in the first table are combined with data from the second table. And if there is no corresponding data in the second table then a NULL value is inserted into the results. Whereas in Inner Joins, records from the 2 tables are combined only if there are corresponding values in both the tables. For example consider the following Inner Join on the books table and our new authors table:

SELECT * FROM (authors INNER JOIN books ON authors.author_id = books.author_id)

This query would return only 4 rows of data and not 5 inspite of the new Kiran Pai entry being present. This is because it could find no corresponding entry in the books table for the author named Kiran Pai (there were no books written by Kiran Pai)

There are are 2 types of Outer Joins ..right??
Yeah.. there are 2 types. One is Left Join and the other obviously is Right Join. Consider the same example as shown previously for Left Join, but this time use a Right Join instead of a Left Join:

SELECT * FROM (authors RIGHT JOIN books ON authors.author_id = books.author_id)

This time you would NOT GET 5 rows of data, instead you would get only 4 rows of data. Why so??

Since while joining the data from the books table with the authors table, the books table data is considered first.. since it is a Right Join. Thus for every row in the books table a check for corresponding value in the authoirs table would be made. Thus while adding all the rows of the books table to the joined table, since there were no books written by Kiran Pai, so that particular record from the authors table was not added at all to the joined table. Remember that the joined table is the kind of temporary table that is created while using Joins. That table is refined depending on the criteria present in the query.

Inner Joins and Outer Joins aren't really clear in my mind right now. Why don't you explain it once again in short ?
Actually, all that is required is a re-reading of the previous 4-5 questions. In case you still haven't got it, there is only way out. Create actual tables using some software and then run some sample queries on those tables. Remember not to put corresponding entries for all your data in both of your tables.

By that I mean in case you are making 2 tables - one for artists and one for songs. See to it that in case you have 10 artists names in the artists table, you do not have songs sung by all of those 10 artists in your songs table. Add songs by only 6-7 artists. When you have such a kind of database where there isn't corresponding information for all the records in one table in the other table, Inner Joins and Outer Joins show their real working. In case you have songs sung by all 10 artists in your Songs table, most of your query results would be the same irrespective of what kind of Join you use. Only the order of the rows in the resultset might be altered. So you would be confused..you wouldn't understand the difference between the working of different Joins.

Are there other ways in which I can use Joins?
Yeah you can combine 2 Joins to create more complex Joins. I shall not go into the details of those kind of Joins, but I shall outline the structure of the query which you have to make. This kind of thing is required when you are combining data from say 3 tables. Your query would look something like the following:

SELECT ... , ... FROM ( .... INNER JOIN .... ON .... = .... )
	INNER JOIN .... ON .... = .... WHERE .... = .....

This is not really for beginners, but if you can get it.. you are not a beginner.. you are already on your way to be an expert.