Home arrow MySQL arrow Page 3 - 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 - Inner Joins
(Page 3 of 5 )

As I told you initially, there are many other types of Joins. Inner Joins and Outer Joins are 2 of them. Inner Joins are the most commonly used type of Joins and 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 combine all of 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 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.

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 author's table is now the one shown below instead of the original one. The books table remains the same.



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 in spite of there being no corresponding values, all of 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.

As I mentioned in Left Joins, all the records in the first table are combined with data from the second table andif 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 desipte 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 two types of outer joins. 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??

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 authors table would be made. Thus while adding all of 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 author's table was not added at all to the joined table. Remember that the joined table is the kind of a temporary table that is created while using Joins. That table is refined depending on the criteria present in the query.

You can combine 2 Joins to create more complex Joins. I shall not go into the details of those kinds of Joins, but I shall outline the structure of the query that 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 then you're not a beginner... you are already on your way to becoming an expert!

So basically there are 3 join types: cross, inner and outer. There is also a Self Join, which I shall not be explaining in this article. Just remember that it exists. It is a fairly complex topic which you may not need right away.
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