Examples for Combining Tables

These examples are in directory /home/walker/public_html/courses/325.fa08/database/

• JOIN operations (2 tables): example-3-join-1.php
• GROUP and JOIN operations: example-3-join-2.php
• JOIN operations (3 tables): example-3-join-3.php

Reseting and Expanding the Conference Tables

Several examples and exercises relate to tables confAuthor, confAuthorPaper, and confPaper in the csc325 database. Three scripts can help in set up the database for this work.

• Resent the paper table to data from the previous lab with example-2-reset.php.
• Reset the author and author-paper tables with example-3-reset.php.
• Add two authors with no papers and three papers with no authors with example-3-extension.php.

Examples of Join Types

Join, restricting to matching rows only

SELECT * FROM confAuthor, confAuthorPaper
     WHERE confAuthor.authorID=confAuthorPaper.authorID ;

Join, ensuring every author appears, whether or not the author is matched with a paper.

SELECT * FROM confAuthor LEFT JOIN confAuthorPaper
     ON confAuthor.authorID=confAuthorPaper.authorID ;

Join, ensuring every author appears, whether or not the author is matched with a paper and whether or not an author-paper pair matches with an author.

SELECT * FROM confAuthor JOIN confAuthorPaper
     ON confAuthor.authorID=confAuthorPaper.authorID ;

Join, ensuring every paper appears, whether or not the paper matches any author in the database.

SELECT * from confAuthorPaper RIGHT JOIN confPaper
    ON confAuthorPaper.paperID=confPaper.paperID;

Examples of Joins over 3 Tables

Match authors and papers through the author-paper table, ignoring any extra authors or papers

SELECT * FROM confAuthor, confAuthorPaper, confPaper 
     WHERE confAuthor.authorID=confAuthorPaper.authorID 
        AND confAuthorPaper.paperID=confPaper.paperID;

Same query, expressed as binary operations

SELECT * FROM confAuthor INNER JOIN confAuthorPaper INNER JOIN confPaper 
           WHERE confAuthorPaper.paperID=confPaper.paperID
        AND confAuthor.authorID=confAuthorPaper.authorID ;

Match authors and papers through the author-paper table, but also include any extra authors

SELECT * FROM confAuthor LEFT JOIN confAuthorPaper 
           ON confAuthor.authorID=confAuthorPaper.authorID 
    LEFT JOIN confPaper 
           ON confAuthorPaper.paperID=confPaper.paperID;

Match authors and papers through the author-paper table, but also include any extra papers; here are 3 versions

SELECT * FROM confAuthor RIGHT JOIN confAuthorPaper 
           ON confAuthor.authorID=confAuthorPaper.authorID
    RIGHT JOIN confPaper 
           ON confAuthorPaper.paperID=confPaper.paperID;

SELECT * FROM (confAuthor RIGHT JOIN confAuthorPaper 
           ON confAuthor.authorID=confAuthorPaper.authorID)
    RIGHT JOIN confPaper 
           ON confAuthorPaper.paperID=confPaper.paperID;

SELECT * FROM confAuthor RIGHT JOIN (confAuthorPaper 
        RIGHT JOIN confPaper 
           ON confAuthorPaper.paperID=confPaper.paperID)
    ON confAuthor.authorID=confAuthorPaper.authorID;

Match authors and papers through the author-paper table, but also include any extra authors and any extra papers

• In SQL, this could be accomplished with a FULL OUTER JOIN, but this is not implemented in MySQL
• One can piece together two different queries as a UNION

SELECT * FROM confAuthor LEFT JOIN confAuthorPaper 
           ON confAuthor.authorID=confAuthorPaper.authorID 
    LEFT JOIN confPaper 
           ON confAuthorPaper.paperID=confPaper.paperID
UNION
SELECT * FROM confAuthor RIGHT JOIN confAuthorPaper 
           ON confAuthor.authorID=confAuthorPaper.authorID 
    RIGHT JOIN confPaper 
           ON confAuthorPaper.paperID=confPaper.paperID;