or 02-9262-7770 
| Database Joins | |
By Chris Smith |
Published
03/8/2006
|
Programming
|
Rating:
|
|
|
Join SyntaxBack to the topic again! To get the author information for a particular news item we need to join the tables together. We can do this with a "INNER JOIN" (this joins the table on the left to the table on the right using the column we specify): SELECT * FROM newsitem INNER JOIN authors ON newsitem.authorid=authors.authorid; Depending on which database you use you will get slightly different formatting - the results will be the same though. From MySQL: mysql> SELECT * FROM newsitem INNER JOIN authors ON newsitem.authorid=authors.authorid; +--------+-----------+--------------+----------+----------+----------+-----------+----------+ | newsid | newstitle | newscontent | authorid | authorid | username | firstname | lastname | +--------+-----------+--------------+----------+----------+----------+-----------+----------+ | 1 | Test | Testing News | 1 | 1 | Chris | Chris | Smith | +--------+-----------+--------------+----------+----------+----------+-----------+----------+ 1 row in set (0.00 sec) From PostgreSQL: testing=# SELECT * FROM newsitem INNER JOIN authors ON newsitem.authorid=authors.authorid; newsid | newstitle | newscontent | authorid | authorid | username | firstname | lastname --------+-----------+--------------+----------+----------+----------+-----------+---------- 1 | Test | Testing News | 1 | 1 | Chris | Chris | Smith (1 row) So we can see the news content and the author information all in one go! If we couldn't do this, in our code (eg PHP or Perl), we'd have to get the authorid from the newsitem table and then get the author information separately - so double the work. Using the INNER JOIN syntax we have to tell MySQL/PostgreSQL how to join the tables together. In this case, using the authorid: ON newsitem.authorid=authors.authorid Otherwise the database doesn't know how to put the two tables together (this happens whether the foreign key relationship is created or not). On another side note, I prefer this syntax: SELECT * FROM newsitem, authors WHERE newsitem.authorid=authors.authorid; It produces exactly the same output, but in complicated queries where you join more than 2 tables together, the join order is very important. Joining tables together in the wrong order will make the query very slow (and this can be quite hard to track down). It's a personal preference but I don't have any problems using this syntax (unless I forget an index which we've ignored in this tutorial). |
|
