Joins

Database schema before class: database_day4_end_of_class.sql
Slides from class
SQL script from class

Database notes

SQL command that evaluates an expression and return one value if true and another value if false. Example: SELECT RestaurantID, IF (Grade IS NULL,'N/A',Grade) AS Grade FROM Inspections;

COALESCE

SQL command that returns the first non-null value. Example: SELECT OrderID, COALESCE(State, Country, 'N/A') AS Location FROM Orders;

CASE

SQL command that provides if-then-else logic. Example:

SELECT i.RestaurantID, RestaurantName, count(*) AS `Total Inspections`,
	CASE 
		WHEN count(*) < 10 THEN 'Infrequent inspections '
		WHEN count(*) BETWEEN 10 AND 15 THEN 'Moderate inspections'
	    	ELSE 'Frequent inspections'
	END AS Frequency
	FROM Inspections i, Restaurants r
	WHERE i.RestaurantID = r.RestaurantID
	GROUP BY i.RestaurantID;

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.