Home arrow MySQL arrow Page 2 - SQL In Simple English Part 2/2
MYSQL

SQL In Simple English Part 2/2


In part one of this article, Kiran looked at the basics of SQL including SELECT, INSERT, UPDATE and DELETE queries. In this, the final part of the article, Kiran's going to teach us about joins and sub queries. After reading both part 1 and 2 of this article, you should be able to construct intermediate SQL queries to extract and manipulate data from both single and multiple tables.

Author Info:
By: Kiran Pai
Rating: 5 stars5 stars5 stars5 stars5 stars / 7
May 11, 2002
TABLE OF CONTENTS:
  1. · SQL In Simple English Part 2/2
  2. · What is a join?
  3. · Inner Joins
  4. · Sub Queries
  5. · Conclusion

print this article
SEARCH DEVARTICLES

SQL In Simple English Part 2/2 - What is a join?
(Page 2 of 5 )

Suppose you have a database that has 2 tables in it and you want some information that 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. By using joins however, you could get data from both of these tables with just one query. There are quite a few types of joins. I shall begin by explaining the simplest one first.

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:

Table Name : authors

Table Name : authors

Table Name : books

Table Name : books

Initially I will show you the way to get the work done with the simplest form of Join using the WHERE clause. You would only be using 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 only 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).

Let's work through an explanation of what we've just seen. First and foremost, you should understand that the basic difference between the query discussed above and all previous queries you have learnt until 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 look at the values in this table carefully, you should see that many of the rows indicate wrong information, i.e. 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 up to us 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 have written.

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 ....

SQL can sometimes be very, very confusing in case you don't have an easy way to see what is happening when the query is executed. Specially when you are joining 2 tables to select values from both of them you need to remember the joined table. Generally, in case you have 2 tables with say 3 and 5 records (rows) each, the joined table would have 3x5 = 15 records (rows).

The most simple and obvious use of Joins is to get data that exists in 2 tables that 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, i.e. something that you would want to extract from a database as a response to a users question. 

An important thing to know is that while creating tables you generally create a primary key in every table. A primary key (for beginners) basically means that in that particular column there can be no duplicate values and that column would be the most unique thing representing the data in that table. So for example in the authors table the author_id is the primary key and there can be no 2 same author_id values. Every author must have a unique id. You would generally have this value (of the primary key present in some other table) whenever you want to create a link between the 2 tables. In our case author_id is present even in the books table so that a link is created between the 2 tables. Such links between 2 tables allow us to use Joins effectively and get a lot of data with just single queries.

With the type of Join I just explained above, it would not be too tough to get this done. The 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.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials