As shown by the syntactical structure above, you can also view columns from different tables by performing a join. A join requires that both tables have an object identifier value for it to pull the two or more tables together and dynamically become one large table.
For instance, let's say we had another table with information about the publisher for each of the books. This table has the publisher name, the isbn of the books, and the suggested retail price for the books. If you want to create this table, use the CREATE TABLE and INSERT command. During creation of the isbn column, remember to specify it as not null unique primary key because we will use it to reference the book table. Also, when entering the data for publ_name and price, remember to include the data in quotes. The dollar sign will automatically be added for each instance of the suggested price.
Table 4-6. Publisher's Table Specifications
If we do a select all on this table, it displays:
Example 4-20. Publisher Table
isbn | publ_name | sugg_price ---------+------------------+------------ 2567841 | ODG Books | $12.99 8741205 | Children's Books | $19.99 602071 | ABC Books | $4.55 676883 | Reading Rainbow | $13.29 355184 | ABC Books | $16.85 214872 | Young Readers | $6.00 (6 rows)
To join two separate tables and view the columns, use the SELECT clause:
SELECT t.colname1, t2.colname1, t2.colname2 FROM tableName t, tableName2 t2 WHERE t1.colname1 = t2.colname1;
We are accessing columns from two tables, so there must be a way to tell the parser which column is from which table. That is why the first line specifies that from table t, extract colname1; from table t2, extract colname1; and from table t2, extract colname2.
But the next line of command tells us that the actual table names are tableName and tableName2. Where did 't' and 't2' come from? Notice that it says 'FROM', then 'tableName', a space ' ', and finally a 't'. This specifies that the letter 't' represents tableName where ever it is used in the command statement. It is there so that you don't need to continuously re-type the table name. This is not part of the required syntax because this is an optional user specified field. If you decide to not use it, then you must type out the full table name, a period '.', then the column name where-ever you want to specify a column. The comma ',' separates the two table names.
The last line tells the parser how to connect the two tables together. Both tables have a unique colname1 that can be connected together. Again, to specify which column is from which table, you must list the table name, a period '.', then the column name.
Using our publish and book table, we can connect the two dynamically by:
Example 4-21. Joining Tables
SELECT b.title, b.cpright_date, p.sugg_price FROM book b, publish p WHERE b.isbn = p.isbn;
Or you can also use this syntax:
SELECT book.title, book.cpright_date, publish.sugg_price FROM book, publish WHERE book.isbn = publish.isbn;
As shown by the syntax above, it is not necessary to spell out the table names if you specify in the from line
title | cpright_date | sugg_price -----------------------------+--------------+------------ Franklin in the Dark | 1986-09-22 | $8.00 Bartholomew and the Oobleck | 1997-02-01 | $16.85 Goodnight Moon | 1991-04-01 | $4.55 The Velveteen Rabbit | 1958-02-06 | $13.29 Little Women | 1980-02-15 | $12.99 The Cat in the Hat | 1972-04-29 | $19.99 (6 rows)
If you want to connect three or more tables, then list all of the table names in the FROM line and use equal signs to pull the separate tables together in the WHERE line. Like this:
SELECT t.colname1, t2.colname1, t3.colname1 FROM tableName t, tableName2 t2, tableName3 t3 WHERE t1.colname1 = t2.colname1 AND t2.colname1=t3.colname1;
To perform a join, we must find the keys, or indexes within each table and connect them together through those keys. We can display the product of the columns when joined together, or display only a few columns. For example:
SELECT * FROM author a, book b, publish p WHERE a.a_id = b.author_id AND b.isbn = p.isbn;
The output of the above statement is the product of all tables specified in the FROM line.