SQL SUBQUERIES Chinook Dataset chinookdatabase codeplex com It

SQL – SUBQUERIES

Chinook Dataset chinookdatabase. codeplex. com It is a large (~1 MB) of many tables representing a media library (a la i. Tunes and a linked employee database.

UNION • • UNION combines the result sets of two queries. Column data types in the two queries must match. UNION combines by column position rather than column name. If we wanted all the names of Artists and Albums: SELECT Artist. Name FROM Artist UNION SELECT Album. Title FROM Album; http: //msutoday. msu. edu/news/2015/msu-union-hours-tochange-for-summer-semester/

UNION ALL • • UNION performs a DISTINCT on the result set, eliminating any duplicate rows. • If I wanted the Artists and Album names, but duplicates were okay: UNION ALL does not remove duplicates, and it therefore faster to perform than UNION. SELECT Artist. Name FROM Artist UNION ALL SELECT Album. Title FROM Album;

FULL OUTER JOIN (TAKE 2) • If I wanted all the Artist and Album pairs (with NULL if unmatched): SELECT Artist. Name, Album. Title FROM Artist LEFT OUTER JOIN Album ON Artist. Id = Album. Artist. Id UNION ALL -- combining two left joins SELECT Artist. Name, Album. Title FROM Album LEFT OUTER JOIN Artist ON Artist. Id = Album. Artist. Id WHERE Artist. ID IS NULL; -- exclude repeated inner join

CAN YOU WRITE A FULL OUTER JOIN NOW? 1. 2. 3. 4. No. Perhaps with a few minutes of time. Sure, it makes sense to me. Josh is a bastard.

WHEN IS A LEFT OUTER JOIN THE SAME AS AN INNER JOIN? 1. 2. 3. 4. Never. When the left rows always has a match. When the left rows don't have NULLs. Is this the set up of a joke?

http: //www. codeproject. com/K B/database/Visual_SQL_Join s/Visual_SQL_JOINS_orig. jpg

SUBQUERIES • • • A subquery is a SQL query within a query. Subqueries are nested queries that provide data to the enclosing query. Subqueries can return individual values or a list of records Subqueries must be enclosed with parenthesis If I wanted all the Artists who released an Album with just their own name: SELECT Artist. Name FROM Artist WHERE Artist. Name IN (SELECT Album. Title FROM Album);

EXISTS • • • WHERE EXISTS tests for the existence of any records in a subquery. • If I want all the Tracks that appear in a Playlist: EXISTS returns true if the subquery returns one or more records. EXISTS is commonly used with correlated subqueries (nested subqueries that use values in the outer query). SELECT Track. Name FROM Track WHERE EXISTS (SELECT Playlist. Track. Id FROM Playlist. Track WHERE Track. Id = Playlist. Track. Id);

DOES THE COLUMNS RETURNED BY AN EXISTS SUBQUERY MATTER? 1. The first value returned matters, the rest don't 2. Yes you can only have one column returned 3. 4. Nope Can you go back one slide?

ALL THE SAME SELECT * FROM Track WHERE EXISTS (SELECT Playlist. Track. Id FROM Playlist. Track WHERE Track. Id = Playlist. Track. Id); SELECT * FROM Track WHERE EXISTS (SELECT * FROM Playlist. Track WHERE Track. Id = Playlist. Track. Id); SELECT * FROM Track WHERE EXISTS (SELECT 1 FROM Playlist. Track WHERE Track. Id = Playlist. Track. Id);