SQL queries subqueries and joining SQL query

SQL queries subqueries and joining

SQL query – beyond one table • So far, we have only applied queries to a single table • It is possible – and very useful – to make queries applied to several tables • Can answer more complex questions… • …but queries also tend to become more complex themselves SWC – 2011 2

SQL query – beyond one table • We use a movie information database as example Movie movieid title country prodyear genre oscars Actor Casting movieid actorid SWC – 2011 actorid name country birth living oscars 3

SQL query – beyond one table actorid name country birth 1 John Wayne USA 04 -06 -1911 No 2 2 Chow-Yun Fat HK 12 -11 -1959 Yes 0 3 Karl Stegger Denmark 30 -01 -1922 No 0 4 Jean Reno France 10 -10 -1949 Yes 0 5 Julia Roberts USA 28 -09 -1964 Yes 1 6 Natalie Portman Israel 02 -07 -1980 Yes 0 7 Marc Duret France 01 -08 -1966 Yes 1 SWC – 2011 living oscars 4

SQL query – beyond one table movieid actorid 1 1 4 4 6 1 5 5 2 1 7 5 4 2 4 5 6 2 6 6 5 2 7 6 6 3 1 7 2 3 2 7 4 3 3 7 5 SWC – 2011 5

SQL query – subqueries • Suppose we wish to answer a (complex) question like this: • How many movies are from the country that Marc Duret is from • This question cannot be answered by a single query (as we know it…) • Information is spread out in multiple tables SWC – 2011 6

SQL query – subqueries SELECT country FROM Actor WHERE name = ’Marc Duret’ Result is ’France’ SELECT COUNT(title) AS Movie. Count FROM Movie WHERE Movie. country = ’France’ used as input NB ! SWC – 2011 7

SQL query – subqueries • We can run these two queries ”manually”, or use output from first query as input to second query – not optimal… • Better to make first query a subquery to the second query SWC – 2011 8

SQL query – subqueries Outer query SELECT COUNT(title) AS Movie. Count FROM Movie WHERE country = (SELECT country FROM Actor WHERE name = ’Marc Duret’) Inner query SWC – 2011 9

SQL query – subqueries • Result from inner query (the subquery) is used as input to outer query • The inner query produces a result table – just as any other query – which is then used by the outer query • Very convenient – but some complications – Name clash – Multiple results SWC – 2011 10

SQL query – subqueries • When we query more than one table, field names from different tables might be identical – a name clash • We can qualify a field name by prefixing it with the table name – Movie. country (country field in Movie table) – Actor. country (country field in Actor table) SWC – 2011 11

SQL query – subqueries • What if a subquery produces more than one result? SELECT country FROM Actor WHERE (oscars > 0) SWC – 2011 Result: USA France 12

SQL query – subqueries • In such case, we must use the IN keyword SELECT COUNT(title) AS Movie. Count FROM Movie WHERE country IN (SELECT country FROM Actor WHERE (oscars > 0)) Try formulating this query in plain English… SWC – 2011 13

SQL query – joining • Another approach to multi-table queries is to use join • To ”join” tables is to make a kind of ”multiplication” of the tables • Simple case: Join without any conditions: SELECT * FROM Movie, Actor SWC – 2011 14

SQL query – joining • If the previous query is run against the Movie. Information database, we get a result with 49 rows… • 49 = 7 x 7. All combinations of Movie records (7) and Actor records (7) • All fields from both tables are included • This is rarely what we want… SWC – 2011 15

SQL query – joining • In almost all cases, we wish to pick out those rows where certain fields match • Example: For each movie, find the title of the movie, and the names of the actors appearing in the movie • We already have this information in the Casting table (20 records), but only as identifiers… SWC – 2011 16

SQL query – joining • We clearly need information from all three tables in the Movie. Information database, so first attempt at a query could be: SELECT * FROM Movie, Casting, Actor • Result: 980 records with a lot of fields… SWC – 2011 17

SQL query – joining • First of all, we only need the Title field from the Movie table, and the Name field from the Actor table SELECT Movie. Title, Actor. Name FROM Movie, Casting, Actor • Result: 980 records with correct fields SWC – 2011 18

SQL query – joining • Next, we only need those records where the relevant information match • We are trying to ”substitute” identifiers in the Casting table with real data (titles and names) • In order to get the right titles and names, the identifiers for this data must match! SWC – 2011 19

SQL query – joining • Match identifier for titles and names: SELECT Movie. Title, Actor. Name FROM Movie, Casting, Actor WHERE ((Movie. Id = Casting. Movie. Id) AND (Actor. Id = Casting. Actor. Id)) • Result: 20 records with correct fields! SWC – 2011 20

SQL query – joining • This is a quite common ”pattern” for multitable queries – Tables representing relations only contain identifiers (keys in other tables) – ”Real data” is contained in tables representing entities (identifiers are keys) – Obtaining ”real data” for relations requires use of join, with matching of key fields SWC – 2011 21