About Schedule Software

Joins

Database schema before class: database_before_day5.sql

Slides from class
SQL script from class (posted after class)

Database notes

NATURAL JOIN

A join operation on two tables in which only rows that match on common attributes. Example: SELECT RestaurantID, RestaurantName, count(*) FROM Restaurants r NATURAL JOIN Inspections i -- matches on attributes common to both tables (RestaurantID here).

INNER JOIN

A join operation on two tables in which only rows that match on a given criteria are selected. Example: SELECT * FROM TableA a JOIN TableB b ON a.ID=b.ID -- attributes to match stated explicitly, returns rows where ID from Table A matches ID from Table B. If an ID is not in both Table A and B, that row is not returned.

LEFT [INNER] JOIN

A join operation in which all rows from the left table are returned matched with attributes from the right table, or NULL if no match in the right table. Example: SELECT * FROM TableA a LEFT JOIN TableB b ON a.ID=b.ID -- all IDs from Table A returned, matched with values from Table B if ID is in Table B, NULL for Table B attributes otherwise.

RIGHT [INNER] JOIN

A join operation in which all rows from the right table are returned matched with attributes from the left table, or NULL if no match in the left table. Example: SELECT * FROM TableA a RIGHT JOIN TableB b ON a.ID=b.ID -- all IDs from Table B returned, matched with values from Table A if ID is in Table A, NULL for Table A attributes otherwise.

OUTER JOIN

A join operation in which all unmatched pairs are returned. Example: SELECT * FROM TableA a FULL JOIN TableB b ON a.ID=b.ID -- returns all rows from Table A and Table B, returns NULL if IDs do not match.

Note: many definitions from Coronel and Morris.