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.
View
A virtual table based on a SELECT command that is saved as an object in the database. The view does not store a separate copy of the data, instead if references data in other tables.
Transaction
A sequence of database requests to access the database. A transaction is a logical unit of work. It must complete entirely, or must abort. No intermediate states are allowed.
COMMIT
A SQL command that permanently saves data to the database. By default in MySQL auto commit is enabled, so each SQL command commits after it executes.
ROLLBACK
A SQL command that restored the database to the condition that existed after the last commit. By default in MySQL auto commit is enabled, so each SQL command commits after it executes. Turn off auto commit with SET AUTOCOMMIT = 0; Turn on auto commit with SET AUTOCOMMIT = 1;
AUTO INCREMENT
A characteristic of an attribute used to uniquely identify rows. Normally starts at 1 and increments by 1 each time a row in inserted.
NOT NULL
A constraint on an attribute to ensure the value cannot be null.
UNIQUE
A constraint on an attribute to ensure no two rows share the value.
CHECK
A constraint on an attribute to ensure the value is appropriate. Example: CHECK (Salary > 0)