SQL In Simple English - Part II

I read SQL In Simple English Part I. I shall tell my friends that I know a lot of SQL !!
Wait.. You still have a long way to go before you know a lot of SQL. But to know even the basics of SQL you need to be familiar with one topic.. called Joins. In this article I shall explain Joins and some other interesting aspects of SQL which all beginners must know.

But why? I read Part I and I was able to implement a simple database program very easily. Why would I need to learn anything more?
You might have implemented some database program.. but I am sure it would be a very simple one. You may even be able to implement a good database program without Joins. But you wouldn't be using the features of SQL that let you do the same work much easily. Without Joins, you may manage SQL.. but with Joins you could do things very easily.

So what are Joins?
Suppose you have a database which has 2 tables in it. And you want some information which is spread over both the tables. Typically you would have to run a query for each table to get the data from each separate table. But with Joins you could get data from both the tables with just one query. There are quite a few types of Joins. I shall begin by explaining the simplest one first.

Give me an example..
To explain this topic, I shall use the tables that I have shown below. It's just some sample values. It should be sufficient for you to grasp the concept.

"autohrs" & "books "tables

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 :

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.

Is there anything that I have to take care of when making such queries?
Yeah. Remember that when you write the names of the columns in the query you should take care that you specify the exact column that you are referring to. Thus in case you have a column named 'topic' in the above 2 tables, and you want the value of the topic field in the authors table, you should refer to it as authors.topic as in the following way..

SELECT authors.topic .... from authors, .... WHERE ....

So what's the most obvious use of Joins? And what is this concept of a common column in 2 tables?
The most simple and obvious use of Joins is to get data that exists in 2 tables which have some kind of relation. Actually there need not be a relation. Joins basically means you are kind of joining 2 tables and then selecting data from them. But in case you have no relation between the 2 tables (no common column) you will see that the query you make is senseless. It would have no practical value. In our example above the common author_id column allows us to make our query sensible. By sensible I mean something useful as information. Something that you would want to extract from a database as a response to a users question.